Importing Text File

[garygdj]

We have received a zip file from a new client containing several thousand loans. The problem is that when I open the file as text all of the data is in one "field" meaning instead of going across the data goes down.
loan type,
loan number,
origination date,
loan term,
lo type,
etc....

Then it starts over with the next loan. There are over 17,000 lines.

How can I import the text file and have the loans go across. Even when I have imported the file using comma delimited it still imports them going down. Even if the comma delimited would work, I would still have issues because Access would not know when the new loan began to move it to the next row.

Any thoughts or suggestions? Sorry for my rambling.

 

[KenHigg]

How small can you make the file if you zip it?

 

[The_Doc_Man]

A way to do this (if I understand what you are saying) is to write some code in VBA to open the file, read a line, and hold fields until you have the right number.

It would be a LOT easier if

a) you have some sort of marker you can read from the file (like one of the lines has something recognizable). If LOAN TYPE happens to always be a fixed text keyword, you might be lucky...

b) you know for a fact beyond dispute that each entry has exactly the right number of lines.

In either case, you can open a recordset, open the file for input, and write a little loop that every time it finds the start of a new record, it does a .ADDNEW on the recordset, fills in the fields (with appropriate CLng, CDbl, CInt, etc), and does the .UPDATE. Of course, on the first record, you have no fields stored, and on the last record you will take an error trap for <EOF> so must do one last cycle of addnew/update.

 

[garygdj]

It's already been zipped.

 

[ghudson]

Can you post a sample of the first few lines of the data from the text file? Change anything that is too sensitive for public viewing but preserve the format and structure of the data.

 

[garygdj]

The information is very sensitive. Account numbers and such. Please understand that what I am giving you is made up information.
'con',
1234567,
null,
{ts 'ccyy-mm-dd 00:00:00.000'},
123,
456,
12.00000,
123,456.78

There are 54 fileds for each account and over 300 accounts. They are all listed just as you see them straight down. I do have a table that gives me the field names accross but I can't figure out how I can get each loan into the proper field. Then when all the fields in one loan have been moved into the proper field go to the next line and loan those loans into the table.

 

[KeithG]

You can read the file by opening up a textstream object in VBA. Then use the read line method to retrieve the value and store it in your field.