Importing fixed-length text file

[JCJM]

Hi. Haven't dabbled in access for over 8 years...would
appreciate any help (or direction to a knowledge base).

Attempting to import a file with fixed-length records,
but no line feed or other character at the end of each
record. It is just one continuous line. Can this be done
within a macro or other method?
Thanks for any assistance
..

 

[John Nurick]

Hi,

I'd do this by using a Perl one-liner to insert linebreaks at the end of
each record, then importing as a normal fixed-width file. If the record
length is 80, it would be something like:

perl -0 -p -e "s/(.{80})/$1\n/g" D:\oldfile.txt > D:\newfile.txt

If you don't have or don't want to install Perl, you can read the file a
record at a time using VBA along these lines (untested air code):

Dim lngFN As Long
Dim strRecord As String

lngFN = FreeFile()
Open "D:\File.txt" For Input Access Read As #lngFN
Do While Not EOF(lngFN)
strRecord = Input(80, #lngFN)
'parse record and append to table
...
Loop
Close #1

 

[Guest]

Thank you John.

-----Original Message-----
Hi,

I'd do this by using a Perl one-liner to insert linebreaks at the end of
each record, then importing as a normal fixed-width file. If the record
length is 80, it would be something like:

perl -0 -p -e "s/(.{80})/$1\n/g" D:\oldfile.txt > D:\newfile.txt

If you don't have or don't want to install Perl, you can read the file a
record at a time using VBA along these lines (untested air code):

Dim lngFN As Long
Dim strRecord As String

lngFN = FreeFile()
Open "D:\File.txt" For Input Access Read As #lngFN
Do While Not EOF(lngFN)
strRecord = Input(80, #lngFN)
'parse record and append to table
...
Loop
Close #1

Hi. Haven't dabbled in access for over 8 years...would
appreciate any help (or direction to a knowledge base).

Attempting to import a file with fixed-length records,
but no line feed or other character at the end of each
record. It is just one continuous line. Can this be done
within a macro or other method?
Thanks for any assistance
.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.