Solved

Access: importing very large record

Posted on 2012-04-03
8
284 Views
Last Modified: 2012-04-10
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.
0
Comment
Question by:COBOLforever
8 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 

Author Comment

by:COBOLforever
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
tens of thousands, each with 700 fields?  And where does the 4000+ bytes come in?

mx
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 

Author Comment

by:COBOLforever
Comment Utility
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
<<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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now