Import the Pipe Delimit Text File into Access Table

If you want to import the text file which is text is separated with pipe sign and and single quote is used as text qualifier.

Here is the code-


Sub import_the_pipe_delimit_text_file_data()
DoCmd.SetWarnings (False)
Dim rs As Recordset
Dim importdata
'we will create a table
DoCmd.RunSQL "CREATE TABLE sample_import (Rep_ID LONG, REP_Name text ,State text,location text,sales long)"
'import data to table from text file
Set rs = CurrentDb.OpenRecordset("sample_import", dbOpenDynaset)
Open "C:\Users\ashishkoul\Desktop\learn\sample_file.txt" For Input As #1
Do While Not EOF(1)
' Read a line of data.
Line Input #1, LineData
'change the pipe sign with comma or etc
importdata = Split(LineData, "|")
'MsgBox importdata(0)
With rs
.AddNew
' its numeric field
.Fields![Rep_ID].Value = importdata(0)
' its text field and in text file single quote is used as text qualifier so we will exclude first and last character in the word
.Fields![Rep_name].Value = Mid(importdata(1), 2, Len(importdata(1)) - 2)
' its text field and in text file single quote is used as text qualifier so we will exclude first and last character in the word
.Fields![STATE].Value = Mid(importdata(2), 2, Len(importdata(2)) - 2)
' its text field and in text file single quote is used as text qualifier so we will exclude first and last character in the word
.Fields![Location].Value = Mid(importdata(3), 2, Len(importdata(3)) - 2)
.Fields![sales].Value = importdata(4)
.Update
End With
Loop
' Close text file.
Close #1
rs.Close
RefreshDatabaseWindow
DoCmd.SetWarnings (True)
End Sub


Download Access Database

Download Sample Text File