none
Import Pipe Delimited Text File Into Access 2007 RRS feed

  • Question

  • Hi All,

    I want to automate import of text file which is pipe delimited into Access database.

    As of now i am importing text file manually using external data and saving the steps as saved imports. Than i use below code to run the saved imports.

    DoCmd.RunSavedImportExport "IMPTXTDATA"

    Every day i get new files so using above method is not efficient as it involves lot of manual steps. Also i run into type conversion error every time as some of the data in text file has 10 digit number (phone and fax number fields which has 10 digit data) which i am not able to store in access unless i specify them as text during manual import steps.

    How can i automate the process of importing text file (with pipe delimited) into access table without getting type conversion error.  To avoid type conversion error, In excel i can insert extra row and delete that row when data gets imported into access but it will be hard to that in text file. Is there any other way?

    Is it possible to use transfertext method and provide delimiter as PIPE (|)?

    Thanks,

    Zaveri


    • Edited by zaveri cc Thursday, September 3, 2015 2:46 AM
    Thursday, September 3, 2015 2:45 AM

Answers

  • Hi Zaveri,

    >> Every day i get new files so using above method is not efficient as it involves lot of manual steps.

    Are the new files with the same format in the content and same file name? If they are, after you save the steps as saved imports, you could use “DoCmd.RunSavedImportExport "IMPTXTDATA"” without manually importing the file again. The Import Specification will store the steps you manually operate.

    >> How can i automate the process of importing text file (with pipe delimited) into access table without getting type conversion error.

    You could use RunSavedImportExport or DoCmd.TransferText. To avoid type conversion error, you could set the file type with shot text.

    >> Is it possible to use transfertext method and provide delimiter as PIPE (|)?
    Yes, you could. You could save the delimiter as PIPE (|), setting the file type in an Import Specification, and then call the specification in a DoCmd.TransferText command.
    Here is a simple code:

    Sub ImportExcel()
    'DoCmd.RunSavedImportExport ("Import-Pipe")
    ‘ Pipe is the name of specification
        DoCmd.TransferText acImportDelim, "Pipe", "Book1", "C:\Book2.txt"
    End Sub

    For details setting of specification, you could refer the picture below:

    You could refer the link below for more information about transfertext.
    # DoCmd.TransferText Method
    https://msdn.microsoft.com/en-us/library/office/ff835958.aspx?f=255&MSPPError=-2147217396

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, September 4, 2015 6:25 AM

All replies

  • Hi Zaveri,

    >> Every day i get new files so using above method is not efficient as it involves lot of manual steps.

    Are the new files with the same format in the content and same file name? If they are, after you save the steps as saved imports, you could use “DoCmd.RunSavedImportExport "IMPTXTDATA"” without manually importing the file again. The Import Specification will store the steps you manually operate.

    >> How can i automate the process of importing text file (with pipe delimited) into access table without getting type conversion error.

    You could use RunSavedImportExport or DoCmd.TransferText. To avoid type conversion error, you could set the file type with shot text.

    >> Is it possible to use transfertext method and provide delimiter as PIPE (|)?
    Yes, you could. You could save the delimiter as PIPE (|), setting the file type in an Import Specification, and then call the specification in a DoCmd.TransferText command.
    Here is a simple code:

    Sub ImportExcel()
    'DoCmd.RunSavedImportExport ("Import-Pipe")
    ‘ Pipe is the name of specification
        DoCmd.TransferText acImportDelim, "Pipe", "Book1", "C:\Book2.txt"
    End Sub

    For details setting of specification, you could refer the picture below:

    You could refer the link below for more information about transfertext.
    # DoCmd.TransferText Method
    https://msdn.microsoft.com/en-us/library/office/ff835958.aspx?f=255&MSPPError=-2147217396

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, September 4, 2015 6:25 AM
  • In addition to what Edward said, pleas consider this.

    https://www.youtube.com/watch?v=wJ7EELjy0JQ


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    • Edited by ryguy72 Saturday, September 5, 2015 1:53 PM
    Saturday, September 5, 2015 1:53 PM