Import Fixed Width Text File

[troyu]

I am trying to import a text file (which is fixed width) into Access 97, using the "Get External Data" command off the file menu.

I am unable to import this fixed width file. Access continually created an errors table, which gives me the error:

Error Field Row
Unparsable Record 1

Any ideas?

 

[PaulF]

if this textfile does not contains Line Feeds only and not Carriage Return Line Feeds, Access treats it as one long record. You'll need to convert the Line Feeds to Carriage Return Line Feeds or to write code to bring data in from the text file one line at a time.

PaulF

 

[PaulF]

sorry that should have been

if this textfile contains Line Feeds only and not Carriage Return Line Feeds, Access treats it as one long record.

PaulF

 

[troyu]

Do you have an example of code that I could use to convert the line feeds to carriage return line feeds?

Or an example of code to bring the data in one line at a time?

 

[RickSpr]

This will convert LFs to CrLfs:

    Dim ch As String * 1
    Dim prevCh As String * 1
    Open "TestIn.txt" For Binary As #1
    Open "TestOut.txt" For Binary As #2
    prevCh = Chr$(0)
    Do While Not EOF(1)
        Get #1, , ch
        If (ch = vbLf) And (prevCh <> vbCr) Then
            Put #2, , vbCr
        End If
        Put #2, , ch
        prevCh = ch
    Loop
    Close #1
    Close #2

Rick Sprague

 

[troyu]

You'll have to forgive me. I am new at this stuff. Where would I place this code?

 

[PaulF]

One method would be to place this inside a Public Function in a new Module (select Module then New from the Database Window)

Public Function ConvLfToCrLf(strFileIn As String, strFileOut As String)
Dim ch As String * 1
Dim prevCh As String * 1
Open strFileIn For Binary As #1
Open strFileOut For Binary As #2
prevCh = Chr$(0)
Do While Not EOF(1)
Get #1, , ch
If (ch = vbLf) And (prevCh <> vbCr) Then
Put #2, , vbCr
End If
Put #2, , ch
prevCh = ch
Loop
Close #1
Close #2
End Function

The you can call this from the Click Event of a Command Button on a Form, or from a Macro using the RunCode Action. This Function requires that you pass the path and name of the file with only Line Feeds and then the path and name of the new file you want to create with Carriage Return Line Feeds

ex:
In the Macro
ConvLfToCrLf(&quot;c:\ThisIsIn.txt&quot;,&quot;c:\ThisIsOut.txt&quot


In the Click Event
Call ConvLfToCrLf(&quot;c:\ThisIsIn.txt&quot;,&quot;c:\ThisIsOut.txt&quot


PaulF