IMporting fixed width text file into a database (1 Viewer)

Hi,

I am trying to implement a system that will import data from a text file whose data is in a fixed width format i.e Field 1 is from 1-2, field 2 is from 13-20 etc

I have inserted message boxes to confirm that the code is reading the data but my problem lies in picking the fields and in the right length then saving it in the table named letters.

Will greatly appreciate your help.

See the code I have below and advice. Pls note I have commented some code as I was testing import of the 1st field

Public Function ImportData(FileName As String, ImportMonth As Date) As Integer

Dim LineOfData As String, OneChar As String, Counter As Integer

' Import a text file extract for a given month. The file should be in the current
' directory with the name YYYYMM.TXT where YYYY is the year and MM is the month.

On Error GoTo subError

LineOfData = vbNullString
Counter = 0

Open FileName For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
OneChar = Input(1, #1) ' Get one character.

If OneChar <> Chr(13) And OneChar <> Chr(10) Then
LineOfData = LineOfData & OneChar

End If

If OneChar = Chr(13) Then
If Counter > 0 Then
ParseLineOfData LineOfData, ImportMonth
End If
Counter = Counter + 1
LineOfData = vbNullString
End If
Loop
Close #1 ' Close file.
ImportData = Counter - 1

subExit:
Exit Function
subError:
ErrMsg Err.Description
Resume subExit

End Function
Public Function ParseLineOfData(LineOfData As String, ImportMonth As Date)

Dim strBrAcNumber As String, strShortName As String, strAmount As String
Dim strDateClass As String, strMarketSeg As String, strAccType As String
Dim strDOpened As String, strLastCR As String, strStreamCode As String
Dim tempStr As String, Separator As String, SQLStr As String
Dim pos As Integer

On Error GoTo subError

tempStr = LineOfData
' Separator = "\"

' 'Extract branch and account number
' pos = InStr(1, tempStr)
' MsgBox pos
' If pos > 0 Then
strBrAcNumber = Mid(tempStr, 1, 12)
' tempStr = Mid(tempStr, pos + 1)
' End If
MsgBox strBrAcNumber

' 'Extract Short Name
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strShortName = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract Amount
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strAmount = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract DateClass
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strDateClass = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract MarketSeg
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strMarketSeg = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract AccType
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strAccType = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract DOpened
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strDOpened = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract LastCR
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strLastCR = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract streamCode
' strStreamCode = tempStr
' tempStr = vbNullString

' ' Append data into the table MAIN for later etraction into Accounts and MothlyData tables.

SQLStr = "INSERT INTO Letters (Letter_Code) VALUES (" & _
strBrAcNumber & ")"
'
' DoCmd.SetWarnings True
DoCmd.RunSQL SQLStr
'
' DoCmd.SetWarnings True

subExit:
Exit Function
subError:
ErrMsg Err.Description
Resume subExit

End Function

If the files always have the same fixed width, you can create an import specification and use that spec with the docmd.transfertext command.

To build a spec, manually import the file once. Get the settings to your liking, then click on the Advance --> Save As. Then use the transfertext command with the spec name.

Hi Scooterbug,

Thanks man but something I forgot to say is that its years since I coded and need you help in creating the import specification.

Attached is a sample extract and the file specs

Creating the import spec is a piece of cake.

1. Open up the database. Under File, select Get External Data... and then select Import.
2. Naviage to the .txt file that has the data in it. Make sure you change the Files of Types to .txt or else you wont see it. Click on the file and import.
3. From here, make sure Fixed Width is selected. Then you can simply click on the Advanced Tab. On the bottom, you will see that Access has tried to set the widths for you. You can go through, and based on your file specs, change the width of the columns. Once you have that completed, save the spec and remember the name.

VBA Help will show you how to correctly use the docmd.transferText command.

but if the data includes multiple row types, you cant used an import spec

lets say you have a file of invoices

INV ----------
LINE ---------
LINE ---------
LINE ---------
LINE ---------

INV ----------
LINE ---------
LINE ---------
LINE ---------

INV ----------
LINE ---------
LINE ---------

INV ----------
LINE ---------
LINE ---------

you dont need to read a char at a time - you read A LINE at a time

so you get


hope this helps

Thank you Dave.

Changed the approach slightly and below is the code that inserts the records BUT each field is inserted in a new record i.e field one in row 1 field 2 in row two.

Can any one help ? I want the import to place each record in its own row within the table i.e field 1 to the last in row one and record/line 2 in the next row

Set Rs = CurrentDb.OpenRecordset("Select * From tblSchemas Where fldTblName = '" & TableName & "'")
Set Rs2 = CurrentDb.OpenRecordset(TableName)

If Not Rs.EOF And Not Rs.BOF Then
'read the contents of the text file one line at a time
Open TextPath & "\" & TextFileName For Input As #1
Do Until EOF(1)
Line Input #1, strText
MsgBox strText
'Get the table defnintions from the table
Do Until Rs.EOF
strFieldName = Rs(1)
sPos = Rs(2)
sLen = Rs(3)

'add the portion of the string to the record in the table
Rs2.AddNew
Rs2(strFieldName) = Mid(strText, sPos, sLen)
Rs2.Update
Rs.MoveNext
Loop

Loop
Rs.Close
Close #1
End If

'close the instances of the objects
Set Rs = Nothing
Set Rs2 = Nothing

Click to expand...

Unless you really are dealing with the case the Gemma-the-husky describes then I really suggest you consider doing as Scooterbug suggests. Looking at your sample file I would say this is perfect for creating a file import specification (part from the first line which can easily be dealt with). So what you are doing here is trying to re-invent the wheel.

Nevertheless, here's the corrections for your code:
Note that when you do an AddNew, you then need to assign the values to all the fields before updating i.e. you don't update a field at a time - you update the whole record. Also, I've added a MoveFirst, otherwise you won't re-initialise your schema file.

A couple of other points:

• Your field naming is a bit iffy. You should avoid using spaces and also any other none alpha numeric characters. I see there's a hyphen in there
• At present the file will be imported as text only. But I would think you would want to import it as the respective data types (?)
• You haven't dealt with the problem that the first line of the file is not part of the data

If you use the file import specification method then this handles:
- errors
- field naming
- data types

hth
Chris

Hi,

Thank you.

The code worked perfectly. Thank you so much

Yes you are right I havent dealt with the data types which I now want to work on.

Any ideas on how I can
1. handle line one by saving it in a different table ?

2. Handle the data types ?

Regards,
Chris

Macjnr said:

Any ideas on how I can
1. handle line one by saving it in a different table ?

Click to expand...

Just put another Line Input before you enter any of the do-loops. In other words you want to read one line and deal with it. Then all the remaining lines will be dealt with by the do-loops.

What does the first line mean anyway? Maybe you should store it in the same table i.e. in another field so that all the records imported from that file will have that reference?

Macjnr said:

2. Handle the data types ?

Click to expand...

Well first you'd have to include a data type field in your tblSchema. Your destination table will also have to be designed to match the schema.
Then in the do loop you will need to handle each data type. Something like


hth
Chris

I honestly think you are taking something simple and making it more complex. In the span of 5 min, I was able to create an import spec based on the files you uploaded. Once uploaded, a bit of code to remove the first line (You can alternatly use an append query first to move the data from the first line to a separate table), count the number of records imported and display that amount in a message box. Here is the db for that.