Import a fixed width txt file to access

mmcdonal · May 3rd, 2006, 07:42 AM

I am going on the presumption that the first value in the text file is the account number, the second is the first name, the third is the last name, and the fourth is the phone number. I am also assuming that you don't need the "Start" and "Width" values in the table. If you do, please let me know.

'-----Code Starts-----
Dim sAccount As String
Dim sFName As String
Dim sLName As String
Dim sPhone As String
...

Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
    objConn.Open "DSN=Your DSN;" 'or Use a Provider for Jet to Access
    objRS.Open "SELECT * FROM tblYourTable", objConn, 3, 3

Set fso = CreateObject("Scripting.FileSystemObject")
     If fso.FileExists("C:\data.txt") Then
       Set objStream = fso.OpenTextFile("C:\data.txt", 1, False, 0)
     End If

Do While Not objStream.AtEndOfStream
      strLine = objStream.ReadLine
      YourArray = Split(strLine, " ") 'use the tab key to create space between quotes.

      sAccount = Trim(YourArray(0))
      sFName = Trim(YourArray(1))
      sLName = Trim(YourArray(2))
      sPhone = Trim(YourArray(3))

      objRS.AddNew
      objRS("Account") = sAccount
      objRS("FirstName") = sFName
      objRS("LastName") = sLName
      objRS("Phone") = sPhone
      objRS.Update
Loop

objConn.Close
objRS.Close

'-----Code Ends-----

As I say, you may want to use DAO instead of ADO.

HTH

mmcdonal

subhanak · May 3rd, 2006, 08:44 AM

Hi,
You know what!!

I am a slave to a person that teach me one word.

Thank you so much

I will let you know abou the results.

The process is a bit more complicated than this, but you helpd with the most important part of it.

Thank you
Sayed

subhanak · May 5th, 2006, 05:35 AM

Hi,
I just wanted to let you know that the function above works perfectly.

I just had to modify the way to extract the fields, because for some reasons the split function did not pick up the 2nd and 3rd values, although I used the tab to create the space.
The array is a bright idea, but I can get it to work

Debug.Print strLine

1234567891234567 SAYED BEN AYCHA 2048349999

     'sAccount = Arrayline(0)
     'Debug.Print sFName
     sFName = Mid(strLine, 21, 30)
     'sFName = Arrayline(1)
     'Debug.Print sLName
     'sLName = Arrayline(2)
     sLName = Mid(strLine, 51, 35)
     'sPhone = Arrayline(3)
     sPhone = Mid(strLine, 86, 30)

Thank you again Sir.
Sayed