Import Pipe Delimited Text File into Access table

[vvasude2]

Hello All,

I am a new to VBA and Access. I would like to build code to be able to import a text file to a access database table. The text file is piped delimited. I have attached a sample of it (MyText.txt). I have the following code in place, but it wont import properly. Its gives me type conversion errors as well as retains "|" in some fields after import. I have a specefication in place to do the same.

I should be able to browse for the Text file and be able to import it

Dim OpenFile As OPENFILENAME
         Dim lReturn As Long
         Dim sFilter As String
         Dim chosenFile As String
         Dim chosenFileName As String
         Dim filelocString As String
         Dim structurename As String
         Dim tablename As String
         filelocString = "T:\Test Scores"
         structurename = "GRE"
         tablename = "GRE"
 
         OpenFile.lStructSize = Len(OpenFile)
         sFilter = "Text Files (*.txt)" & Chr(0) & "*.txt" & Chr(0)
         OpenFile.lpstrFilter = sFilter
         OpenFile.nFilterIndex = 1
         OpenFile.lpstrFile = String(257, 0)
         OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
         OpenFile.lpstrFileTitle = OpenFile.lpstrFile
         OpenFile.nMaxFileTitle = OpenFile.nMaxFile
         OpenFile.lpstrInitialDir = filelocString
         OpenFile.lpstrTitle = "Use the Comdlg API not the OCX"
         OpenFile.flags = 0
         lReturn = GetOpenFileName(OpenFile)
         If lReturn = 0 Then
            MsgBox "Action Cancelled"
         Else
 
 
            chosenFileName = OpenFile.lpstrFileTitle
            chosenFile = OpenFile.lpstrFile
                    DoCmd.TransferText acImportFixed, structurename, tablename, chosenFile
                   End If
       End Sub

Thanks for the help

BR,
vvasudev

[ADezii]

I had no problems whatsoever:

1. The Transfer Type should be acImportDelim instead of acImportFixed. This is not a Fixed Width File.
2. In the Specification, set all Field Data Types to Text, then convert if necessary, in Access, after the Data has been Imported into the specified Table.