Solved

Access: importing very large record

Posted on 2012-04-03
8
290 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

705 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