0

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.

HK1
  • 11,941
  • 14
  • 64
  • 99
  • It is easier to be clear if you call a quote a quote and an apostrophe an apostrophe. "Double quote" means two quotes in a row, e.g. "". – Bob77 Mar 08 '11 at 23:56
  • This will be very tricky to reparse and reformat. If you try to ignore quotes as text delimiters then embedded commas within values will trip you up. – Bob77 Mar 09 '11 at 00:02
  • Glad you can see the dilemma Bob. It seems both answers below have ignored what I'm saying about this file have multiple delimiters AND fixed width. – HK1 Mar 10 '11 at 04:06

2 Answers2

0

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

  • The way I understand it, the commas that are in this file as delimiters will cause a problem in the numeric/price fields. Fixed width doesn't allow you to specify a starting position and an ending position for each field, thus omitting the delimiters. Or at least I'm not aware of any such feature. – HK1 Mar 08 '11 at 13:17
0

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(,)
wqw
  • 11,771
  • 1
  • 33
  • 41