Need to Import Flat File with Two Different Column Formats

[Dean Frisoli]

Hello,

I have a flat text file, 120 columns, no header row, not delimited;
approximately

Every record (row) is formatted the same up until column 92, so I have no
problem creating the import spec. up until that point (16 fields)

If the value in column 92 is "P", columns 93-120 would need to be split into
11 additional fields.
If the value in column 92 is "O", columns 93-120 would need to be split into
9 additional fields

The only thought I have on this at the moment is to take columns 93-120 and
make that my 17th field so that I can import the file all at once, then use
an update query or some other method to parse out the data into the
appropriate fields based on the value in field #16 (column 92).

Any thoughts?

 

[Dean Frisoli]

Sorry, meant to say "approximately 200,000 records"

 

[John Nurick]

Hi Dean,

What you propose is definitely practical.

Another possibility would be to use a suitable tool (e.g. awk or Perl)
to filter the text file into separate files for "P" and "O", each of
which could then be imported with its own import specification. For
myself, I'd be tempted to use Perl to convert the fixed-width text into
a CSV file, with the full complement of fields in each row. That would
avoid import specifications altogether.

 

[Ronald Roberts]

Hello,

I have a flat text file, 120 columns, no header row, not delimited;
approximately

Every record (row) is formatted the same up until column 92, so I have no
problem creating the import spec. up until that point (16 fields)

If the value in column 92 is "P", columns 93-120 would need to be split into
11 additional fields.
If the value in column 92 is "O", columns 93-120 would need to be split into
9 additional fields

The only thought I have on this at the moment is to take columns 93-120 and
make that my 17th field so that I can import the file all at once, then use
an update query or some other method to parse out the data into the
appropriate fields based on the value in field #16 (column 92).

Any thoughts?

What you didn't say is if column 93-120 is the same for all P records
and column 93-120, diferent format, is the same for all O records.

If this is true then you can import the data into a temp table, then use
an update or append queries selecting on the P or O. One query for the O
rcords types and another for the P record types.

Ron

 

[Dean Frisoli via AccessMonster.com]

John,

Thanks so much for your quick response. I'm not familiar with awk or Perl (I
work for the Defense Department so I don't have a real good programming
background), but I did a few quick searches on the net and found quite a bit
of info. Looks promising.

Thanks again, and sorry for putting this thread in the wrong discussion group.
... saw after I posted it that it would probably have been more appropriate in
the Importing/Linking group.

Dean

Hi Dean,

What you propose is definitely practical.

Another possibility would be to use a suitable tool (e.g. awk or Perl)
to filter the text file into separate files for "P" and "O", each of
which could then be imported with its own import specification. For
myself, I'd be tempted to use Perl to convert the fixed-width text into
a CSV file, with the full complement of fields in each row. That would
avoid import specifications altogether.

Hello,

[quoted text clipped - 15 lines]

Any thoughts?

 

[Albert D.Kallal]

Hum, why not roll your own import.....

Sub ReadTextFile

Dim strFile As String
Dim intF As Integer
Dim strLineBuf As String
dim rstTable as dao.recordset

strFile = "c:\my data\MyData.txt"

intF = FreeFile()
Open strFile For Input As #intF

Do While EOF(intF) = False
Line Input #intF, strLineBuf
--> here you parse out the data....

rstTable.AddNew

rstTable!FirstName = mid(strLineBuf,1,20)
rstTable!LastName = mid(strLineBuf,21,20)
etc.

if mid(strLineBuf,92,1) = "P" then
.... bla bal bla
else
... bla bla bla
end if
rstTable.Update

Loop
Close intF
rstTable.Close
End If

You could even throw in the code to pop up the windows file browse...and
make a real slick import option...

 

[Dean Frisoli via AccessMonster.com]

Albert,

This is great stuff.

Thanks to you and the rest of the folks who provided input for all your
assistance.

I'm actually having a bit of difficulty in another area now, but I'll try and
work it a bit more on my own before I post it!

Dean