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

[Paul Clement]

On Tue, 13 Jul 2004 05:17:03 -0700, Art <Ar*@discussion s.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\TextF iles;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******@amerit ech.net
Microsoft MVP (Visual Basic)

[Art]

Paul,

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

Art

[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

[Paul Clement]

On Thu, 15 Jul 2004 05:14:14 -0700, Art <Ar*@discussion s.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=F alse
CharacterSet=AN SI
Format=FixedLen gth
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=F alse
CharacterSet=AN SI
Format=CSVDelim ited
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=F alse
CharacterSet=AN SI
Format=CSVDelim ited
DateTimeFormat= "yyyy-mm-dd hh:nn:ss"

Col1=TRACKING_N UMBER 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_R EQUEST Date
Col12=FILLER1 Char
Col13=QUESTION Memo
Col14=IMPORT_FI LENAME Char
Col15=IMPORTDAT E Date
Col16=STATUSCOD E Char
Col17=STATUS_DE SCRIPTION Char
Col18=OPENDATE Date
Col19=ASPEN_DAT E Date
Col20=ANSWER Memo
Col21=USERID Char
Col22=DATE_ASSI GNED Date
Col23=UPDATES Memo
Col24=SAIC_EXPO RTDATE Date
Col25=PRIORITY_ LEVEL Char
Col26=TOPIC_ID Char
Col27=DISPOSITI ON_CODE Char
Col28=SAIC_EXPO RTNAME Char
Col29=ASPEN_EXP ORTNAME Char
Col30=ASPEN_EXP ORTDATE Date
Col31=REVISED_Q UESTION Memo
Col32=COMMENTS Memo
Col33=ANSWER_RE SOURCE Char
Col34=ANSWER_RE SOURCE_DETAIL Char
Col35=CALL_COUN T Char
Col36=REF_ACTIV ITY_NAME Char
Col37=CATEGORY Char
Col38=CALLBACK Char
Col39=LOCATION Char
Col40=PRIORITY_ REASON Char
Col41=NOT_REF_C TR Char
Col42=EXCEPTION _TO_48_HR Char

[Sample.txt]
ColNameHeader=F alse
CharacterSet=AN SI
Format=Delimite d(|)
Paul ~~~ pc******@amerit ech.net
Microsoft MVP (Visual Basic)