Text into Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
¤ 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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
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
 
¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top