Fixed Width Text File Import

[Johnny Meredith]

I'm going to throw this one out there just in case someone has dealt with it
before.

I have a massive fixed width text file with data similar to the following (this
represents one record in the "database"):


14523AB 12/31/2002 D1 04 1000.00 300.00 200.00
14523A 50.17032.01

The file has headers and subtotals that are not needed. Plus, there are no
text qualifiers and the file takes liberties with the term "fixed width."

Each record is on two lines just like above. The file has more rows than
Excel as it stands, but, stripping out the crap, it's really only 26,000
records roughly.

I've written code that cleans this data once imported into Excel. Since the
file is too long, I have to repeat the data validation procedure again for
the rest of the text file. I have to do seven of these things several times a
year, and it's a royal pain.

It would be great if I could come up with an "intelligent import" routine that
reads each piece of data and decides what to do with it programmitically. Even
if it's slow, it beats cutting the text file in half and importing each half
separately.

I don't even know where to start on this one. Any suggestions would be great!

I'm going to post a similar message for the Access people, but I'm more
comfortable in Access DAO than Excel, so any pointers there would be good as
well.

Thanks,
Johnny Meredith
"Knows enough about VBA to be dangerous"

 

[Dave Peterson]

You could read the file, fix it, and then import into excel.

Take a look at the Open Statement (not workbook.open) in VBA's help.

Here's a link that you may want to read:
Controlling File Access with Visual Basic for Applications
http://support.microsoft.com/default.aspx?scid=/support/excel/content/fileio/fileio.asp
(one line in your browser)

If you only wanted to keep the odd numbered rows, you could "clean" it up and
then open the clean version:

Option Explicit
Sub testme()

Dim myInFileName As String
Dim myInFileNum As Long

Dim myOutFileName As String
Dim myOutFileNum As Long

Dim myLine As String
Dim lCtr As Long

myInFileName = "C:\test.txt"
myOutFileName = myInFileName & ".out"

If Dir(myInFileName) = "" Then
MsgBox "Doesn't exist"
Exit Sub
End If

myInFileNum = FreeFile()
Close #myInFileNum
Open myInFileName For Input As #myInFileNum

myOutFileNum = FreeFile()
Close #myOutFileNum
Open myOutFileName For Output As #myOutFileNum

lCtr = 0
Do While Not EOF(myInFileNum)
lCtr = lCtr + 1
Line Input #myInFileNum, myLine
If lCtr / 2 = Int(lCtr / 2) Then
'it's an even row, skip it
Else
Print #myOutFileNum, myLine
End If
Loop

Close #myInFileNum
Close #myOutFileNum

End Sub