Import Fixed Width with Header rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I've read several posts regarding this topic and they have been very helpful
but I still haven't resolved my problem. I have a fixed width .dat file.
The manufacturer says that each line has 24 characters and the last 2
characters are <CRLF>.

When I use the import wizard and create a spec for the import it imported
fine EXCEPT it imported an empty row between each record. After reading John
Nuriks postings I investigated the file using HexEdit and saw that there
seemed to be an extra OD character. Each line ends with ODOD OA. Another
issue may be that there are 3 header rows of which have different lengths
(some more than 24 characters) - but they all begin with H and that there is
more than one "set of data" - meaning 3 header rows appear multiple times in
the file.

I was planning on writing a VB procedure to loop through and skip the header
rows but I don't know how to tell it to recognize the ODOD OA characters.

I hope I have explain this clearly enough for someone to help me.
Thanks
LeAnn
 
Hi LeAnn,

This is air code but shows how to work through a file. I think all
you'll need to get rid of the superfluous carriage returns is the
If Right(strLine, 1)...
structure.

Dim lngFIn as Long
Dim lngFOut as Long
Dim strLine as String

lngFIn = FreeFile()
Open "C:\folder\source.txt" For Input As #lngFIn
lngFOut = FreeFile()
Open "C:\folder\destination.txt" For Output As #lngFOut

Do Until Eof(lngFIn)
'Read line
Line Input #lngFIn, strLine

If Left(strLine, 1) <> "H" Then 'not a header

If Right(strLine, 1) = vbCR '&H0D
strLine = Left(strLine, Len(strLine) - 1)
End If

Print #lngFOut, strLine
End If
Loop

Close #lngFIn
Close #lngFOut

If you find the output file has blank lines, it means that
Line Input
is treating the extra 0Ds as line breaks. In that case, wrap the Print
statement in
If Len(strLine) > 0 Then
Print...
End If

Is it really safe to just dump the header lines, or do they contain
grouping information that you need to incorporate into your table?
 
Absolutely marvelous!! Works like a charm and yes the Line Input did add the
extra line. For our purposes it is safe to dump the header rows (thank
goodness!)

Thanks for your help!
 
Hi LeAnn,

I've experimented briefly and it seems that you don't need to do
anything special. The old Line Input statement appears to treat either
CR (0d) or CRLF (0d0a) as line terminators; thus a file whose records
are separated by 0d0d0a is read as if it had a blank line after every
line of data. Here's a snippet that reads the file and dumps the bogus
blank lines.

Dim FH As Long
Dim S As String

FH = FreeFile()
Open "C:\temp\crcrlf.txt" For Input As #FH

Do Until EOF(FH)
'Read a line. This seems read from the current
'position in the file to the first CR or CRLF
Line Input #FH, S
'S now contains either a line from the file
'excluding its terminating 0d0d0a, (i.e. VBA has
'read from the start of the line up to and including
'the first CR, which it has dumped), or else an
'empty string (i.e. VBA has read from after the
'first CR up to and including the CRLF immediately
'after, which it has dumped).
If Len(S) > 0 Then
Debug.Print S 'or do whatever you like
End If
Loop
Close #FH

So that's one problem sorted.

As for the headers: can you just dump them, or do you need to collect
values from the headers and include them in the individual data rows?
 
Back
Top