Access: importing very large record
I have a 4000+ byte text file. It has 700+ data fields. What is the best way to import this into Access give the limits in Access? It is the Federal Financial Aid ISIR raw data file if that rings anybodys bell.
Thanks a lot.
Thanks a lot.
Peter's got you covered.....
Just one add-on comment; that 255 field limit applies to query output as well. Depending on what you need to do with it, you might want to consider Excel.
No points here please.
Jim.
Just one add-on comment; that 255 field limit applies to query output as well. Depending on what you need to do with it, you might want to consider Excel.
No points here please.
Jim.
ASKER
not just one record - - tens of thousands. but the rest is correct. I figured the same but what I am looking for is a way to automatically parse the record into chunks that Access can handle - is that possible or am I dreaming?
<<the record into chunks that Access can handle - is that possible or am I dreaming?>>
Easily doable with VBA. You read the file and write out to the tables as needed as you read.
You main limitations are as Peter said; 255 fields in a table, 4K recordsize limit (which is a lot less then it sounds, because text is stored as unicode characters - two bytes per character), and 32 indexes per table.
Jim.
Easily doable with VBA. You read the file and write out to the tables as needed as you read.
You main limitations are as Peter said; 255 fields in a table, 4K recordsize limit (which is a lot less then it sounds, because text is stored as unicode characters - two bytes per character), and 32 indexes per table.
Jim.
tens of thousands, each with 700 fields? And where does the 4000+ bytes come in?
mx
mx
One thing though, you can work around the page limit size by storing strings in memo field, which are stored seperate from the rest of the record and can be up to 64K characters per record. Don't think you'd need to resort to that though.
Jim.
Jim.
ASKER
thought of the memo - considered importing it and storing each record as a memo but then didn't think I could get at the memo field to parse it out.
unfortunately don't know VB well enough to do that.
the 4000+ bytes is the length of each record, 700 fields per record - lots of small sized fields
Guess I am going to have to us a TextPad macro to turn the file into CSV then import to Excel and place filters on the fields that I want to "query".
unfortunately don't know VB well enough to do that.
the 4000+ bytes is the length of each record, 700 fields per record - lots of small sized fields
Guess I am going to have to us a TextPad macro to turn the file into CSV then import to Excel and place filters on the fields that I want to "query".
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If so you will have to read the file programmatically field by field and put the data somewhere.
But you can't store in in one Access record because of the 255 field limit and also the 4k record size limit.
SO maybe you store the data in 700 records each having a 'sourcefield number/name' field and a second field to contain the data. It will all have to be stored as text though I expect- unless by some chance every field is a number.