What's wrong with this schema.ini?

[Guest]

(reposting here)

I have a text file that I need to import. Making matters somewhat difficult,
it uses semicolons as the field delimiter, and therefore needs a schema.ini
(is this true?!). That's a bit annoying because the file changes name every
day, but I can handle that by renaming the file in my code.

The data is currently accessed using a linked "table" in a mdb. This works
fine, although the data needs considerable cleanup as there are a number of
"header" and "information" rows that need to be pulled out. My goal is to
move to an adp, and eventually from there into a DTS package inside SQL
Server to automate it.

So if I read the dox correctly, I have to use a schema.ini because there's
no way to specify a non-standard delimiter using a connection object/string.
So in order to get the syntax correct, I used the ODBC control panel to make
an ini, and it made this...

[test.txt]
ColNameHeader=False
Format=Delimited(
CharacterSet=ANSI
Col1=ClientCode
Col2=AccountID
Col3=AECode
Col4=AccountType
Col5=RecType
Col6=BuySell Integer
Col7=Canceled
Col8=SecurityID
Col9=Symbol
Col10=CUSIP Integer
Col11=Descripto
Col12=Price Float
Col13=CurrencyCD
Col14=Quantity Integer
Col15=Net Float
Col16=Adjust Float
Col17=Gross Float
Col18=EntDesc
Col19=ProcDate Integer
Col20=TradeDate Integer
Col21=SettleDate Integer
Col22=AccountFunds
Col23=BuyCode
Col24=Settled
Col25=Comissions Float
Col26=OrderNo Integer
Col27=TagNo Integer

This appears to be identical to the one that is used to read it into the
..mdb as well.

However when I attempt to open the file with...

oConn.Open _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=L:\Data;" & _
"Extensions=csv,txt"

I get the error "The Col1 option is invalid". This appears to be a bogus
message, as the option "Col1" is clearly valid. I thought it might be one of
the options earlier in the list, so I changed the Format=Delimited and then
it started complaining about the Format being invalid. So then I changed the
names for the columns thinking that was the problem, and got the original
error again. I also tried using the JET driver instead of the text driver,
but that didn't help and I got the same error.

Any ideas?

 

[Douglas J. Steele]

To be honest, I haven't used schema.ini files in a long time, but if I'm
reading the documentation at
http://msdn.microsoft.com/library/en-us/odbc/htm/odbcjetschema_ini_file.asp
properly, you must specify a data type for each column.

See whether changing it from

Col1=ClientCode

to

Col1=ClientCode Text

works any better. Most of your fields are missing the type.

Doug Steele, Microsoft Access MVP

(No private e-mails, please)