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
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:
The file is ( | ) delimited (as shown above) and it works fine except for
the lines where certain fields (like the description fields) have a ( " )
Any suggestions are greatly appreciated! Thanks in advance.
Re: Importing text file using schema.ini
On 2 Oct 2001 12:06:11 -0700, "Kevin" <firstname.lastname@example.org> 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
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 ~~~ email@example.com
Microsoft MVP (Visual Basic)
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
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.
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL