Import Fixed-width Text file (field with commas)LE (1 Viewer)

[onisdad]

I am trying to import a fixed-width text file via DAO. First of all, I know that this can be easily done via DoCmd.TransferText and an Import/Export Specification (or possibly via an Schema.ini file as well).

Input text file, size : about 784 MB, record length: 438 positions, fields 16
It includes a field (last one) PSNAME which in certain instances includes a comma as part of the data e.g. Continental Metals, Inc. ... there is no "text qualifier".

Code is written to extract two fields from each row, excluding field PSNAME

Dim rsDetail As DAO.Recordset
Dim strLine As String

Open C:\TestData.txt For Input As #1 ' 50 record sample file
' Ignore the first two lines in the text file
Line Input #1, strLine ' first line, PSCODE, PSNO, ....
Line Input #1, strLine ' second line, underline _ _ _ _

' In the description of the input (rsDetail) you could skip fields within the input
Do Until EOF(1)
Input #1, strLine
If Len(strLine) > 1 Then ' Test the length of strLine (String)
rsDetail.AddNew
rsDetail!PSCODE = Val(Mid(strLine, 1, 6))
rsDetail!PSNO = Val(Mid(strLine, 83, 8))
rsDetail.Update ' To finally confirm the new entry
End If
Loop

' Close all opened objects
Close #1
rsDetail.Close
Set rsDetail = Nothing

Code runs without giving me any errors; however, the output consist of 63 records (in lieu of the expected 50 records).
- The 13 additional records exhibit PSCODE = 0, PSNO = 0
- A review of the input indicates that 13 (of the 50 input records) contain a
comma in field PSNAME.

The ouput is ok if I convert all commas to blanks prior to running the code (received an "out of memory" error when trying run the VBScript on the original file).
Do I have to do this as a rule or can the code be tweaked to a) completely skip reading field PSNAME or b) account for the presence of "commas as part of the data" in field PSNAME?

Best Regards.

 

[jdraw]

Since you have fixed length can you check the field for a comma and adjust accordingly?

Can you zip some sample data?

if you use Line Input you can get all punctuation (I think).
then parse the input.

I often read the text into temporary Access table, then parse the fields I need and write to Final Table.

 

[onisdad]

Thank you for the prompt response. Can you advise as how I can do that within the code?

Thanks again.

 

[onisdad]

Re: Import Fixed-width Text file (field with commas)

Solved!

Dim rsDetail As DAO.Recordset
Dim strLine As String

Open C:\TestData.txt For Input As #1 ' 50 record sample file
' Ignore the first two lines in the text file
Line Input #1, strLine ' first line, PSCODE, PSNO, ....
Line Input #1, strLine ' second line, underline _ _ _ _

' Loop until we get to the line of text that contains commas
Do Until EOF(1)
' Read line of text into variable
Line Input #1, strLine
If InStr(164, strLine, ",") > 0 Then
strLine = Replace(strLine, ",", "")

' In the description of the input (rsDetail) skip unwanted fields
rsDetail.AddNew
rsDetail!PSCODE = Val(Mid(strLine, 1, 6))
rsDetail!PSNO = Val(Mid(strLine, 83, 8))
rsDetail.Update ' To finally confirm the new entry
Else
rsDetail.AddNew
rsDetail!PSCODE = Val(Mid(strLine, 1, 6))
rsDetail!PSNO = Val(Mid(strLine, 83, 8))
rsDetail.Update ' To finally confirm the new entry
End If
Loop

' Close all opened objects
Close #1
rsDetail.Close
Set rsDetail = Nothing

Thanks jdraw fror your invaluable suggestion!

 

[jdraw]

I have created a small text file (testfile.txt) and a database (database1) and have attached these in zip format.

The testfile should be placed in your C:\ directory.
The code is in a module called module4. You can follow the vba it is similar to yours.

If you don't want to place the testfile.txt in your C:\, then place it where you want and change the reference in the vba code.

Good luck.


OOOPs: Just saw you have it solved. Anyway here is the stuff I was working on.
I am not getting email notifications.
Glad you have it solved.

 

[onisdad]

jdraw, very interesting approach. Am I correct in inferring
that the only limitation would be if the input text file has more than 255 positions across?

Thanks again.