Importing text file using schema.ini
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.
Re: Importing text file using schema.ini
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)