473,322 Members | 1,523 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Text into Access

Art
I've already posted a number of questions on this topic and everyone has been extremely helpful. I've yet another (related) question.

I want to bring a text file into an Access table using vb.net. With your help I've been able to do this. The problem I'm now running into is that the first row of the text file becomes my field names. Is there any way to specify the field names in the SQL -- when putting the data into a new table? Or... if inserting the data into an existing table do I need to have field names in the text file?

Art
Nov 20 '05 #1
4 1638
On Tue, 13 Jul 2004 05:17:03 -0700, Art <Ar*@discussions.microsoft.com> wrote:

¤ I've already posted a number of questions on this topic and everyone has been extremely helpful. I've yet another (related) question.
¤
¤ I want to bring a text file into an Access table using vb.net. With your help I've been able to do this. The problem I'm now running into is that the first row of the text file becomes my field names.

You have to include HDR=No in your connection string to the text file:

[Text;DATABASE=D:\My Documents\TextFiles;HDR=No].[Table1.txt]

¤ Is there any way to specify the field names in the SQL -- when putting the data into a new table? Or... if inserting the data into an existing table do I need to have field names in the text file?

You can define your own in a schema.ini file:

http://msdn.microsoft.com/library/de...a_ini_file.asp

If you don't use a schema.ini file the column names default to F1, F2, F3, etc.

If you are inserting into an existing table I believe that the column names from the source and
destination table must match in order to omit the column names from the SQL statement.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 20 '05 #2
Art
Paul,

Thanks very much for your help -- it's just what I needed.

Art
Nov 20 '05 #3
Art
Paul,

I think I could use a hint about the schemas you've pointed me to. Could tell how (or where to look) to direct my vb.net progam to use the schema once I've set it up? Also, I've looked at the reference on schemas, and I probably will be able to create one that I need -- but if you know of an example of one it will definitely save me a lot of trial and error with regard to writing a syntactically correct definition.

Thanks again for you help - even if you don't have time to help with this second request.

Art
Nov 20 '05 #4
On Thu, 15 Jul 2004 05:14:14 -0700, Art <Ar*@discussions.microsoft.com> wrote:

¤ Paul,
¤
¤ I think I could use a hint about the schemas you've pointed me to. Could tell how (or where to look) to direct my vb.net progam to use the schema once I've set it up?
¤ Also, I've looked at the reference on schemas, and I probably will be able to create one that I need -- but if you know of an example of one it will definitely save me a
¤ lot of trial and error with regard to writing a syntactically correct definition.
¤
¤ Thanks again for you help - even if you don't have time to help with this second request.

The schema.ini file should be placed in the same folder as the text files. That is where the driver
will look.

Here are a few examples of entries in a schema.ini file:

[FixLength.txt]
ColNameHeader=False
CharacterSet=ANSI
Format=FixedLength
Col1=ColName1 Text Width 3
Col2=ColName2 Text Width 15
Col3=ColName3 Text Width 15
Col4=ColName4 Text Width 11
Col5=ColName5 Text Width 1
Col6=ColName6 Text Width 1
Col7=ColName7 Text Width 89

[Test.csv]
ColNameHeader=False
CharacterSet=ANSI
Format=CSVDelimited
Col1=F1 Integer
Col2=F2 Integer
Col3=F3 Integer
Col4=F4 Integer
Col5=F5 Char Width 255
Col6=F6 Char Width 255
Col7=F7 Integer
Col8=F8 Integer
Col9=F9 Integer
Col10=F10 Char Width 255
Col11=F11 Integer
Col12=F12 Char Width 255
Col13=F13 Char Width 255
Col14=F14 Integer
Col15=F15 Integer

[Output.txt]
ColNameHeader=False
CharacterSet=ANSI
Format=CSVDelimited
DateTimeFormat="yyyy-mm-dd hh:nn:ss"

Col1=TRACKING_NUMBER Char
Col2=TELEPHONE_NUMBER Char
Col3=FIRSTNAME Char
Col4=LASTNAME Char
Col5=ADDRESS1 Char
Col6=ADDRESS2 Char
Col7=CITY Char
Col8=STATE Char
Col9=ZIP Char
Col10=LANGUAGE Char
Col11=DATE_OF_REQUEST Date
Col12=FILLER1 Char
Col13=QUESTION Memo
Col14=IMPORT_FILENAME Char
Col15=IMPORTDATE Date
Col16=STATUSCODE Char
Col17=STATUS_DESCRIPTION Char
Col18=OPENDATE Date
Col19=ASPEN_DATE Date
Col20=ANSWER Memo
Col21=USERID Char
Col22=DATE_ASSIGNED Date
Col23=UPDATES Memo
Col24=SAIC_EXPORTDATE Date
Col25=PRIORITY_LEVEL Char
Col26=TOPIC_ID Char
Col27=DISPOSITION_CODE Char
Col28=SAIC_EXPORTNAME Char
Col29=ASPEN_EXPORTNAME Char
Col30=ASPEN_EXPORTDATE Date
Col31=REVISED_QUESTION Memo
Col32=COMMENTS Memo
Col33=ANSWER_RESOURCE Char
Col34=ANSWER_RESOURCE_DETAIL Char
Col35=CALL_COUNT Char
Col36=REF_ACTIVITY_NAME Char
Col37=CATEGORY Char
Col38=CALLBACK Char
Col39=LOCATION Char
Col40=PRIORITY_REASON Char
Col41=NOT_REF_CTR Char
Col42=EXCEPTION_TO_48_HR Char

[Sample.txt]
ColNameHeader=False
CharacterSet=ANSI
Format=Delimited(|)
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: NewBob | last post by:
Since Access automatically highlights all of the text in a text control (I use it to hold data from a memo field) when the control is activated, I've added the following code to put the cursor at...
4
by: Neil | last post by:
I just noticed that control tips aren't working in any of my databases. I recently installed Access 2003, and created a database in it, and noticed that control tips weren't working in my controls....
3
by: vtashore | last post by:
I downloaded Steve Leban's RTF2 control and it works as advertised. Good news! After reading reference material on the RTF standard codes, I have been able to write update queries to universally...
9
by: Neil | last post by:
I need to implement a rich text box in an MDB file for a user base that consists of Access 2000 and Access 2002. Unfortunately, I'm using Access 2003 on my development machine. My understanding is...
7
prn
by: prn | last post by:
Hi folks, Sometime in the last few days, I must have accidentally done something that changed some kind of switch in Access, but I can't seem to find what it was. This is Access 2003 under XP,...
4
by: Neil | last post by:
Just found out that the Microsoft Rich Textbox does not support full text justification, since it's based on Version 1.0 of the RichEdit Window Class, and full text justification is only available...
16
by: Neil | last post by:
I posted a few days ago that it seems to me that the Access 2007 rich text feature does not support: a) full text justification; b) programmatic manipulation. I was hoping that someone might...
0
Dököll
by: Dököll | last post by:
Continued from: http://www.thescripts.com/forum/thread762010.html -VB 6.0 Professional -Microsoft DAO 3.6 Reference Search Database table... An attempt to fetch data housed in Access:
16
by: Wayne | last post by:
I've read that one method of repairing a misbehaving database is to save all database objects as text and then rebuild them from the text files. I've used the following code posted by Lyle...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.