Importing text file using schema.ini


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Importing text file using schema.ini

Hybrid View

  1. #1
    Kevin Guest

    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.

  2. #2
    Paul Clement Guest

    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)

  3. #3
    Join Date
    Dec 2005
    Posts
    4
    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

  4. #4
    Join Date
    Apr 2004
    Location
    New York City
    Posts
    538
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center