Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Unanswered: Import Text File with Tilde Delimiter

    I have never imported a file with VBA. Anyone know how to get this to work in a module? I use the tilde character (~) instead of the comma as a delimiter in the text file because some fields have commas in the text. The error I get is "Type mismatch". The delimiter is causing the error message. When "acExportDelim" replaces "acTildeDelim", I get no error message and no data is imported either.

    Dim acTildeDelim As String
    acTildeDelim = "~"

    DoCmd.TransferText acTildeDelim, "My Import Specification", _
    "Temp1", "c:\Temp\Import.txt"

    Jerry

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Use a specificationName , determining the delimiter then store it in the DB.
    Use its name in the DoComand

    DoCmd.TransferText , yourSpecificationName, yourTable, YourFile

    Just look up the TransfareText

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Also, you may also find a lot of places will use the | (above the \) as a delimiter as this is the character rarely (if ever) used in any data fields. The ~ can sometimes be used in comment fields.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    hammbakka,
    You responded: DoCmd.TransferText , yourSpecificationName, yourTable, YourFile

    Your solution works and I can now import a text file using VBA in a module.

    My code that included acExportDelim must have exported my empty Temp1 table into the text file because the text file became an empty file.

    pkstormy, the text fields of my import file are company and region names and for years I have had 100% success in using the tilde (~) character as a delimiter.

    Thank you both for your advice.
    Jerry

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I had only one or two instances with the ~ character. I guess it depends on the type of databases you work with.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Good luck..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •