CODEWITHSUNDEEP
vb6adotext-filestext-driver
HK1
HK1

Reputation: 12210

ADO and Microsoft Text Driver - Field Delimiter Problem

I'm using VB6 and ADO together with the Microsoft Text Driver to import data from an ASCII file. The file is comma delimited but it also contains double quotation marks around text data fields. The fields are also fixed width.

I'm having a problem that the driver reads the columns incorrectly any time one of the rows contains a quotation mark double quotation inside the content. This happens inside the "part description" column which is the second column from the left. When this occurs, columns to the right are all Null value, which is not the case in the text file.

I think it would be better to use only the commas as delimiters. However, I believe that commas also occur in the "part description" column so this means I should really load the file as fixed width. I'm not aware that there is any way of doing this unless I can specify this in the schema.ini file.

Any ideas on how to resolve this?

Edit:
You are allowed to specify fixed width in your Schema.ini file. However, it appears to me that the commas and quotation marks that also exist as delimiters/qualifiers will prevent this from working properly. It looks like I may have to "manually" read the file in and write it back out in my own format before I load it using the MS Text driver. Still looking for other opinions.

Upvotes: 0

Views: 1556

Answers (2)

wqw
wqw

Reputation: 11991

I'm extra precautious with regional settings -- some users change default list separator. Usualy fix this with schema.ini like this:

[MyFile.csv]
Format=Delimited(,)

Upvotes: 0

user69820
user69820

Reputation:

I would try changing the Format value in the registry for the Jet text engine (if that's what you're using) at the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text. I think the default is CSVDelimited but you would change this to FixedLength. See http://msdn.microsoft.com/en-us/library/ms974559.aspx

It's probably worth adding that although you have a Schema.ini file for settings, on some options the registry overrules them anyway

Actually, looking at the link I supplied, it seems you have to use a schema.ini file for fixed-length files. Have you tried something like the following, which specifies the width?

[Test.txt]

Format=FixedLength

Col1=FirstName Text Width 7

Col2=LastName Text Width 10

Col3=ID Text Integer 3

Upvotes: 0

Related Questions