• Home
 · 
• Miscellaneous
 · 
• Simple Code to Import Text (Flat file) To Ms-Access (Any Delimited file)

Simple Code to Import Text (Flat file) To Ms-Access (Any Delimited file)

Simple Code to Import Text (Flat file) To Ms-Access (Any Delimited file)

Rate Simple Code to Import Text (Flat file) To Ms-Access (Any Delimited file)

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

'To Import Text file with any delimited File
Public Sub Import_TextFile_2_Table()

On Error GoTo LocalErrorHandler
    
    Dim dbCompany     As Database
    Dim rsGeneral     As Recordset
    Dim FullName        As String
    Dim FileHandle    As Byte
    Dim ImportRecord  As String
    Dim flnName       As String
    Dim RowPosition   As Double
    Dim EmpNumber     As String
    Dim EmpName       As String
    Dim EmpAddress    As String
    Dim EmpCity       As String
    Dim Delimiter     As String


    flnName = "C:\Exported.txt"
    'Delimiter you can give any or what is the input file
    Delimiter = ","

    FileHandle = FreeFile
    
    Open flnName For Input As FileHandle

    'Remove first line If it have Header
    Line Input #FileHandle, ImportRecord

     'Give Path with File name
    FullName = "C:\General"
    
    Set dbCompany = OpenDatabase(FullName)
    
    'Table Name example Company
    Set rsGeneral = dbCompany.OpenRecordset("Company", dbOpenDynaset)
    
    Do Until EOF(FileHandle)

      'Opening Line by Line
      Line Input #FileHandle, ImportRecord
      RowPosition = RowPosition + 1
      EmpNumber = Trim(Mid(ImportRecord, 1, InStr(1, ImportRecord, Delimiter, 1) - 1))
      EmpName = Trim(Mid(ImportRecord, 7, 10))
      EmpAddress = Trim(Mid(ImportRecord, 18, 30))
      EmpCity = Trim(Mid(ImportRecord, 49))
      
      rsGeneral.AddNew
      rsGeneral("EmpNo") = EmpNumber
      rsGeneral("EmpName") = EmpName
      rsGeneral("EmpAddress") = EmpAddress
      rsGeneral("EmpCity") = EmpCity
      rsGeneral.Update

    Loop
    
    Close FileHandle
    rsGeneral.Close
    Set rsGeneral = Nothing
    dbCompany.Close
    Set dbCompany = Nothing

    Exit Sub
    
LocalErrorHandler:
    MsgBox "Error Occured :" & Err.Description, , "Error"

End Sub

'-------------------------
'Please do not paste following line to code
'Paste to text file and same as "C:\Exported.txt"


'The Exported File is like this
No.  ,Name      ,Address                       ,City                          
1    ,Raghuraja ,Gandhi Road                   ,Bangalore                           
2    ,Dayalan   ,Nehru Road                    ,Madras                     
3    ,Saravanan ,Indira Gandhi Road            ,Sivaganga                          
4    ,Saroja    ,Rajiv Gandhi Road             ,New Delhi                           
5    ,Chandran  ,Vajpae Road                   ,Culcutta                           
6    ,Neelam    ,M.G.R Road                    ,Pune                           
7    ,Sugan     ,Mahatma Road                  ,Madurai                           
8    ,Balu      ,Patel Junction                ,Bombay                           
9    ,Keerth    ,Saroja Road                   ,Hyderabad                           
10   ,Shalan    ,Dayalan Road                  ,Vizag