[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access: importing very large record

Posted on 2012-04-03
8
Medium Priority
?
293 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
ID: 37803992
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 58
ID: 37804023
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
ID: 37804033
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 58
ID: 37804077
<<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
 
LVL 75
ID: 37804078
tens of thousands, each with 700 fields?  And where does the 4000+ bytes come in?

mx
0
 
LVL 58
ID: 37804079
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
ID: 37804114
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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 37804258
<<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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

830 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