Importing text file using schema.ini

[Kevin]

I'm importing a text file (which is a data extract) into a MDB database using
VB and a schema.ini file. My problem arises when I import a varchar field
(text) which contains a quote character ( " ). This throws VB off for some
reason and stops the importing of the line. It later resumes.

Is there any way to have VB change the ( " ) character to a single quote
( ' ) upon importing? We'll be doing this import process once a week and
we don't want to be going through and finding ( " )'s in the file all the
time.

So I need somehow for the application to ignore any imported ( " )'s and
change them to ( ' )'s. Here's the SQL-ish statement we're using to import:

"SELECT * INTO [" & stTable & "] FROM [Text;DATABASE=" & stTxtPath & _
"].[" & LCase$(stTable) & ".txt]"

Uses the schema.ini file to import those fields, here's the outtake of the
relevant part of the schema.ini file:

[data.txt]
Format=Delimited(|)
CharacterSet=ANSI
ColNameHeader=False
Col1=Plant_Code text
Col2=Part_Number text
Col3=Part_Description text
Col4=System_Lead_Time long
Col5=Lead_Time long
Col6=Operation_Number text
Col7=Operation_Description text
Col8=Operation_Type text
Col9=Operation_Dept_Code text
Col10=Planned_Time double
Col11=System_Planned_Time double
Col12=Prime_Alt_Number text
Col13=Product_Center text
Col14=Resource_Dept_Code text
Col15=Resource_Code text
Col16=Weight_Factor double
Col17=Resource_Type_Code text
Col18=Resource_Qty long
Col19=Resource_Utilization long
Col20=Oper_Metric_Type_Code text
Col21=Oper_Metric_Range_Code text
Col22=Oper_Cycle_Average double
Col23=Oper_Cycle_Span double
Col24=Oper_Cycle_Std_Dev double
Col25=Oper_Cycle_Sample_Size long
Col26=Avg_Rewk_Repr double
Col27=Oper_Cycle_Q3 long
Col28=Avg_Setup_Hrs double

The file is ( | ) delimited (as shown above) and it works fine except for
the lines where certain fields (like the description fields) have a ( " )
in them.

Any suggestions are greatly appreciated! Thanks in advance.

[Paul Clement]

On 2 Oct 2001 12:06:11 -0700, "Kevin" <kbaugh@suite224.net> wrote:

¤
¤ I'm importing a text file (which is a data extract) into a MDB database using
¤ VB and a schema.ini file. My problem arises when I import a varchar field
¤ (text) which contains a quote character ( " ). This throws VB off for some
¤ reason and stops the importing of the line. It later resumes.
¤
¤ Is there any way to have VB change the ( " ) character to a single quote
¤ ( ' ) upon importing? We'll be doing this import process once a week and
¤ we don't want to be going through and finding ( " )'s in the file all the
¤ time.

The double quote is considered a line delimiter so I'm not aware of any way around this other than
to parse them out and replace them using standard VB file operations or a text editor if you don't
need to code this part.


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

[Y2D]

going back to the days of Qbasic.. i remember using Input to get info from the user, if there was a comma inthe text entered, it would terminate the string... using Line Input would rear to an end-of-line and the commas would work properly this may not help you at all, but maybe it will light a lightbulb in the mind somewhere as a workaround

[Andrew Cushen]

I don't know of any way to do this in the SELECT SQL statement, which seems to be what you want. Anyone else know a way? Some dialects of SQL have a REPLACE clause in them, but it seems to be limited to use in INSERT and UPDATE statements.

It's a bit of a kludge, but you could use regular VB file commands to process the file first, removing the quotes, before running the update:
You would open the file; read a line at a time into another, temporary file while using the VB Replace() function to replace the quotes; close the file; then, read in the temporary file using the schema.ini file, instead of reading the original file. Finally, you would delete the temporary file; or, if you wanted, delete the original file and rename the temporary file with the original file's name; you end up with a single file that has all the quotes in it replaced, and is suitable for importing.

As far as replacing the quote characters using the Replace() function: you could replace the quotes with single quote characters, aka apostrophes, or, less prone to problems down the line, you could replace them with a different character, or even remove them entirely. Having quotes, whether single or double, in Access strings or table fields is a potential nightmare. There is no universal method for escaping quotes in Access that will work in all cases, and, if you ever need to manipulate the data later, you will run into the same types of problems you are facing now!

It may be ugly, but this approach will work until someone out there comes up with a better approach.


-Andrew