By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,335 Members | 2,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,335 IT Pros & Developers. It's quick & easy.

Import Pipe Delimited Text File into Access table

P: 6
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

Expand|Select|Wrap|Line Numbers
  1. Dim OpenFile As OPENFILENAME
  2.          Dim lReturn As Long
  3.          Dim sFilter As String
  4.          Dim chosenFile As String
  5.          Dim chosenFileName As String
  6.          Dim filelocString As String
  7.          Dim structurename As String
  8.          Dim tablename As String
  9.          filelocString = "T:\Test Scores"
  10.          structurename = "GRE"
  11.          tablename = "GRE"
  13.          OpenFile.lStructSize = Len(OpenFile)
  14.          sFilter = "Text Files (*.txt)" & Chr(0) & "*.txt" & Chr(0)
  15.          OpenFile.lpstrFilter = sFilter
  16.          OpenFile.nFilterIndex = 1
  17.          OpenFile.lpstrFile = String(257, 0)
  18.          OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
  19.          OpenFile.lpstrFileTitle = OpenFile.lpstrFile
  20.          OpenFile.nMaxFileTitle = OpenFile.nMaxFile
  21.          OpenFile.lpstrInitialDir = filelocString
  22.          OpenFile.lpstrTitle = "Use the Comdlg API not the OCX"
  23.          OpenFile.flags = 0
  24.          lReturn = GetOpenFileName(OpenFile)
  25.          If lReturn = 0 Then
  26.             MsgBox "Action Cancelled"
  27.          Else
  30.             chosenFileName = OpenFile.lpstrFileTitle
  31.             chosenFile = OpenFile.lpstrFile
  32.                     DoCmd.TransferText acImportFixed, structurename, tablename, chosenFile
  33.                    End If
  34.        End Sub

Thanks for the help

Attached Files
File Type: txt MyText.txt (74 Bytes, 433 views)
Feb 22 '12 #1
Share this Question
Share on Google+
1 Reply

Expert 5K+
P: 8,638
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.
Feb 22 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.