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.
COBOLforeverAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
So you are saying that logically this file contains just ONE record which has 700 fields?

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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
COBOLforeverAuthor Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
tens of thousands, each with 700 fields?  And where does the 4000+ bytes come in?

mx
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
COBOLforeverAuthor Commented:
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".
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<the 4000+ bytes is the length of each record, 700 fields per record - lots of small sized fields>>

 I think you can do that in Access fine.  May take five or six tables, but you can certainly import it.

  It's what you want to do with it after you've imported that will be the kicker.  Again, the main limitation is 255 fields in a query or table.  If your never going to work with move then 255 fields out of the 700 at one time, then your set.

  The next limit would be the 32 indexes.  Storing those in a one to one is five or six indexes right off if you do relationships.  Again, don't think that will be a problem, but it's something to be aware of.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.