A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

What to do with 1.5 million records...



 
 
Thread Tools Display Modes
  #1  
Old April 14th, 2010, 03:11 PM posted to microsoft.public.access.tablesdbdesign
ChicagoPete
external usenet poster
 
Posts: 27
Default What to do with 1.5 million records...

So my company just bought another company and they send me a flat text file
with 1.5 million records of customer name/orders/transactions etc - all in
one huge txt file. Just for fun... I imported this into Access 2007 and you
can view the single table, can't do anything with it, a simple parameter
search on one customer name locks up the server, Access not responding, white
screen blah, blah blah...

I tired to split the table during import with the wizard to create the 6 or
so tables I would like to have - but it kept bombing out. When I tried a
small sample I see I get prompts for nulls fields and am asked to "leave as
is?" a lot, so I guess when I tried to import the complete file that is where
it is hanging up. Don't feel like cleaning 1.5M records today.

Do I set this up on a SQL backend? If so, do I create the SQL db first then
import the text file into SQL OR do I import the text file into Access and
use the db splitter to create the SQL side? Is there a way in SQL to split
into seperate tables?

here are the fields - (tab delimited in file, i used comma here for ease):
Acct Num, Cust Name, Address1, City, State, Zip, Service Call Date, Item,
Qty, Amt, Pmt Date, Service Tech, Ins Name , Ins Paid, Balance

Basically I see about 20 rows per customer name, each with a different Pmt
Date (they pay on the accounts), looking for a ledger type report to run
based on Customer name that will show all payment dates and current balance
due.

example
48714 Smith, Mary 123 Main Boston MA 02134 02012010 937821a 1 2,500
02012010 Bob-11 2,000

48714 Smith, Mary 123 Main Boston MA 02134 02012010 937821a 1 500
03012010 Bob-11 1,500


The intent is to use this data as read only, no new transactions will be
posted, will just need to view previous balances for customers.

Open to any and all ideas on this one...



  #2  
Old April 14th, 2010, 03:25 PM posted to microsoft.public.access.tablesdbdesign
Stefan Hoffmann
external usenet poster
 
Posts: 991
Default What to do with 1.5 million records...

hi Pete,

On 14.04.2010 16:11, ChicagoPete wrote:
So my company just bought another company and they send me a flat text file
with 1.5 million records of customer name/orders/transactions etc - all in
one huge txt file. Just for fun... I imported this into Access 2007 and you
can view the single table, can't do anything with it, a simple parameter
search on one customer name locks up the server, Access not responding, white
screen blah, blah blah...

Create indices for the columns in your search condition.

I tired to split the table during import with the wizard to create the 6 or
so tables I would like to have - but it kept bombing out.

How big in GB is the database if you do a simple import into one table?
Maybe your hitting the 2GB size limit.

Do I set this up on a SQL backend? If so, do I create the SQL db first then
import the text file into SQL OR do I import the text file into Access and
use the db splitter to create the SQL side? Is there a way in SQL to split
into seperate tables?

While Access can handle huge amounts of data like this, I would use a
SQL Server back-end and use the BULK INSERT command to load the data at
once.

Then you may split the table into a correct relational schema or also
simply add the necessary indices...


mfG
-- stefan --
  #3  
Old April 14th, 2010, 03:29 PM posted to microsoft.public.access.tablesdbdesign
Stefan Hoffmann
external usenet poster
 
Posts: 991
Default What to do with 1.5 million records...

hi,

On 14.04.2010 16:25, Stefan Hoffmann wrote:
While Access can handle huge amounts of data like this, I would use a
SQL Server back-end and use the BULK INSERT command to load the data at
once.


http://msdn.microsoft.com/en-us/library/ms188365.aspx


mfG
-- stefan --
  #4  
Old April 14th, 2010, 04:08 PM posted to microsoft.public.access.tablesdbdesign
ChicagoPete
external usenet poster
 
Posts: 27
Default What to do with 1.5 million records...

Hi Stefan;

The raw file size is 500,000kb.

I will try to re-import and add indicies.

How do I split one table into several (either in Access or SQL) after import?

I would like to stay totally in Access 2007, more comfortable...


thanks for the quick reply

-Pete




"Stefan Hoffmann" wrote:

hi Pete,

On 14.04.2010 16:11, ChicagoPete wrote:
So my company just bought another company and they send me a flat text file
with 1.5 million records of customer name/orders/transactions etc - all in
one huge txt file. Just for fun... I imported this into Access 2007 and you
can view the single table, can't do anything with it, a simple parameter
search on one customer name locks up the server, Access not responding, white
screen blah, blah blah...

Create indices for the columns in your search condition.

I tired to split the table during import with the wizard to create the 6 or
so tables I would like to have - but it kept bombing out.

How big in GB is the database if you do a simple import into one table?
Maybe your hitting the 2GB size limit.

Do I set this up on a SQL backend? If so, do I create the SQL db first then
import the text file into SQL OR do I import the text file into Access and
use the db splitter to create the SQL side? Is there a way in SQL to split
into seperate tables?

While Access can handle huge amounts of data like this, I would use a
SQL Server back-end and use the BULK INSERT command to load the data at
once.

Then you may split the table into a correct relational schema or also
simply add the necessary indices...


mfG
-- stefan --
.

  #5  
Old April 14th, 2010, 04:30 PM posted to microsoft.public.access.tablesdbdesign
Stefan Hoffmann
external usenet poster
 
Posts: 991
Default What to do with 1.5 million records...

hi Pete,

On 14.04.2010 17:08, ChicagoPete wrote:
The raw file size is 500,000kb.

This may result in size problems.

How do I split one table into several (either in Access or SQL) after import?

You have to do it manually.

Create the necessary tables as you can entities in your file.
Then create appropriate append queries.

I would like to stay totally in Access 2007, more comfortable...

You can link the SQL Server tables into Access and do the data
processing (splitting the data) in Access.

Sorry for being so vague, but as I don't know the data the explanation
has to be so abstract.


mfG
-- stefan --
  #6  
Old April 14th, 2010, 09:10 PM posted to microsoft.public.access.tablesdbdesign
Dorian
external usenet poster
 
Posts: 542
Default What to do with 1.5 million records...

You have a lot of duplicated information which can be eliminated.
Decide on how many tables you need and then import the data into the
separate tables elminating the duplicates.
Then you can write a small routine to link the records with the correct
primary keys and then set up your table relationships.
I'm assuming this is a one-off operation and you will maintain the data in
your db.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"ChicagoPete" wrote:

So my company just bought another company and they send me a flat text file
with 1.5 million records of customer name/orders/transactions etc - all in
one huge txt file. Just for fun... I imported this into Access 2007 and you
can view the single table, can't do anything with it, a simple parameter
search on one customer name locks up the server, Access not responding, white
screen blah, blah blah...

I tired to split the table during import with the wizard to create the 6 or
so tables I would like to have - but it kept bombing out. When I tried a
small sample I see I get prompts for nulls fields and am asked to "leave as
is?" a lot, so I guess when I tried to import the complete file that is where
it is hanging up. Don't feel like cleaning 1.5M records today.

Do I set this up on a SQL backend? If so, do I create the SQL db first then
import the text file into SQL OR do I import the text file into Access and
use the db splitter to create the SQL side? Is there a way in SQL to split
into seperate tables?

here are the fields - (tab delimited in file, i used comma here for ease):
Acct Num, Cust Name, Address1, City, State, Zip, Service Call Date, Item,
Qty, Amt, Pmt Date, Service Tech, Ins Name , Ins Paid, Balance

Basically I see about 20 rows per customer name, each with a different Pmt
Date (they pay on the accounts), looking for a ledger type report to run
based on Customer name that will show all payment dates and current balance
due.

example
48714 Smith, Mary 123 Main Boston MA 02134 02012010 937821a 1 2,500
02012010 Bob-11 2,000

48714 Smith, Mary 123 Main Boston MA 02134 02012010 937821a 1 500
03012010 Bob-11 1,500


The intent is to use this data as read only, no new transactions will be
posted, will just need to view previous balances for customers.

Open to any and all ideas on this one...



  #7  
Old April 17th, 2010, 07:49 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default What to do with 1.5 million records...

ChicagoPete,

I ran across this article. It discusses how to manually read in text files
in Access using VBA. Here is the link to it:

http://www.applecore99.com/gen/gen029.asp#open

You could write you own conversion program. Read it in from a text file and
then build your Access rows into the separate tables as you go.

You can also automatically fix the data as you go (set Null to "").

Plus you can display record progress information so you can see if your
program hangs up.



Good luck.

Dennis
  #8  
Old April 17th, 2010, 06:48 PM posted to microsoft.public.access.tablesdbdesign
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default What to do with 1.5 million records...

ChicagoPete wrote:
So my company just bought another company and they send me a flat text file
with 1.5 million records of customer name/orders/transactions etc - all in
one huge txt file. Just for fun... I imported this into Access 2007 and you
can view the single table, can't do anything with it, a simple parameter
search on one customer name locks up the server, Access not responding, white
screen blah, blah blah...


Pete,
One option when you have huge data files is to use SQL Server Express for the
back end, because it can handle up to (I think) 4GB in the free version.
Then you can create queries etc in the Access DB. Looks like you're directly
in Access, but the data is just in a different back end. If you index
properly on both sides, it's seamless. You get the simple GUI of Access and
the storage of SQL Server. You can also buy SQL Server Developer for
ridiculously cheap. Read about it here...

http://www.microsoft.com/sqlserver/2...developer.aspx

http://www.amazon.com/gp/search/ref=...qid=1271526398


For learning SQL Server, it's a STEAL.

Mary Chipman & Andy Baron wrote a book on using Access as a FE to SQL Server..
.. it's old but I think still relevant. You can get it for maybe 15 bucks at
Amazon.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201004/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:35 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.