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 Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Trying to import several million rows- help!



 
 
Thread Tools Display Modes
  #1  
Old August 5th, 2004, 04:49 AM
-=Drew
external usenet poster
 
Posts: n/a
Default Trying to import several million rows- help!

All-

I have a gigantic file that I need to do some data mining into. It's about
70,000 pages of flat data records (my best guess- haven't made it to the
bottom of the file yet...it's 332 meg- :-) ) Word can barely handle it (takes
over 30 mins), wordpad & notepad can't hang at all. I can't cut & paste when
I open the file in word, so severing the file into easily manageable sections
isnt' an option.

I'm trying to import it in a linebyline record format.
I tried the posted macro for importing more than 65K records- it works well
but taps out at 1.04 million records. Unfortunately, i need to import all of
them, and probably will end up needing about 3x that many to be imported (yes
friends, over 3 million records.)

I can't tell whether the error is a system resource error or if it's some
limitation in excel.


Any suggestions?

Your country owes the one who helps me a debt of gratitude.
-=Drew
  #2  
Old August 5th, 2004, 06:20 AM
Frank Kabel
external usenet poster
 
Posts: n/a
Default Trying to import several million rows- help!

Hi
suggestion: Don't use Excel for this :-)
Try using a databae which is able to handle this amount of data.
Especially if you're going into serious data mining Excel is NOT the
right tool.

--
Regards
Frank Kabel
Frankfurt, Germany


-=Drew wrote:
All-

I have a gigantic file that I need to do some data mining into. It's
about 70,000 pages of flat data records (my best guess- haven't made
it to the bottom of the file yet...it's 332 meg- :-) ) Word can
barely handle it (takes over 30 mins), wordpad & notepad can't hang
at all. I can't cut & paste when I open the file in word, so severing
the file into easily manageable sections isnt' an option.

I'm trying to import it in a linebyline record format.
I tried the posted macro for importing more than 65K records- it
works well but taps out at 1.04 million records. Unfortunately, i
need to import all of them, and probably will end up needing about 3x
that many to be imported (yes friends, over 3 million records.)

I can't tell whether the error is a system resource error or if it's
some limitation in excel.


Any suggestions?

Your country owes the one who helps me a debt of gratitude.
-=Drew


  #3  
Old August 5th, 2004, 06:28 AM
JE McGimpsey
external usenet poster
 
Posts: n/a
Default Trying to import several million rows- help!

In article ,
-=Drew wrote:

I can't tell whether the error is a system resource error or if it's some
limitation in excel.


XL worksheets are limited to 65536 rows. You could use a macro to import
3M rows into 46 worksheets.

Any suggestions?


Use a database application. You've got a large database. Using a
spreadsheet to manage it doesn't make much sense.
  #4  
Old August 5th, 2004, 06:37 AM
Alan
external usenet poster
 
Posts: n/a
Default Trying to import several million rows- help!

"-=Drew" wrote in message
...

All-

I have a gigantic file that I need to do some data mining into. It's
about 70,000 pages of flat data records (my best guess- haven't made
it to the bottom of the file yet...it's 332 meg- :-) ) Word can
barely handle it (takes over 30 mins), wordpad & notepad can't hang
at all. I can't cut & paste when I open the file in word, so

severing
the file into easily manageable sections isnt' an option.

I'm trying to import it in a linebyline record format.
I tried the posted macro for importing more than 65K records- it
works well but taps out at 1.04 million records. Unfortunately, i
need to import all of them, and probably will end up needing about

3x
that many to be imported (yes friends, over 3 million records.)

I can't tell whether the error is a system resource error or if it's
some limitation in excel.


Any suggestions?


Hi,

I won't re-iterate the sound advice that Excel is totally
inappropriate for what you are trying to achieve ... or perhaps I just
did.

However, in relation to splitting up a big Word document - that should
be possible.

Steps you could try (assuming you have a back up copy so no risk here
except wasting time):


1) Copy the file - Call the two parts 1.doc and 2.doc

2) Open 1.doc and delete approximately half the pages from the middle
to the end. Close and save.

3) Open 2.doc and delete approximately half the pages from the
beginning to the middle. Close and save.

4) Repeat (1) to (3) for each new file (1.doc and 2.doc) substituting
sensible names each time until your individual files are down to
manageable sizes.


HTH,

Alan.


  #5  
Old August 5th, 2004, 01:58 PM
Lady Layla
external usenet poster
 
Posts: n/a
Default Trying to import several million rows- help!

Why not do the data mining in a database environment? Word is definately not
made to do this type of function and the file is too large for Excel. Have you
tried Access?



"-=Drew" wrote in message
...
: All-
:
: I have a gigantic file that I need to do some data mining into. It's about
: 70,000 pages of flat data records (my best guess- haven't made it to the
: bottom of the file yet...it's 332 meg- :-) ) Word can barely handle it (takes
: over 30 mins), wordpad & notepad can't hang at all. I can't cut & paste when
: I open the file in word, so severing the file into easily manageable sections
: isnt' an option.
:
: I'm trying to import it in a linebyline record format.
: I tried the posted macro for importing more than 65K records- it works well
: but taps out at 1.04 million records. Unfortunately, i need to import all of
: them, and probably will end up needing about 3x that many to be imported (yes
: friends, over 3 million records.)
:
: I can't tell whether the error is a system resource error or if it's some
: limitation in excel.
:
:
: Any suggestions?
:
: Your country owes the one who helps me a debt of gratitude.
: -=Drew


  #6  
Old August 5th, 2004, 04:03 PM
-=Drew
external usenet poster
 
Posts: n/a
Default Trying to import several million rows- help!

All-

Thank you for the quick responses.
I know that Excel is not the optimal tool- my problem is that I need to go
step by step to get the data into a DB- ultimately a SQL w/ Access Front End
interface.

As I said before, I would love to chop it into sequential pieces as it would
make the data much easier to handle, but MS Word takes about 3.5 hours to
open this sucker, and the cut/paste options are grey'ed out when i try to
hack it into 10000 page sections. (FYI- it's so big, I haven't had the
patience to see how long it is, but it's probably between 70,000-100,000
pages of flat text records)

1. Can anyone recommend a more powerful text editor than Word to try out? If
i'm able to parse it into smaller files- i.e. those of less than 1.048
million lines, then i'm in business.
2. Can anyone recommend/write a better script to enter the data into Excel?

MVP's- THIS IS YOUR CALL!

Thanks again,
-=Drew

"Lady Layla" wrote:

Why not do the data mining in a database environment? Word is definately not
made to do this type of function and the file is too large for Excel. Have you
tried Access?



"-=Drew" wrote in message
...
: All-
:
: I have a gigantic file that I need to do some data mining into. It's about
: 70,000 pages of flat data records (my best guess- haven't made it to the
: bottom of the file yet...it's 332 meg- :-) ) Word can barely handle it (takes
: over 30 mins), wordpad & notepad can't hang at all. I can't cut & paste when
: I open the file in word, so severing the file into easily manageable sections
: isnt' an option.
:
: I'm trying to import it in a linebyline record format.
: I tried the posted macro for importing more than 65K records- it works well
: but taps out at 1.04 million records. Unfortunately, i need to import all of
: them, and probably will end up needing about 3x that many to be imported (yes
: friends, over 3 million records.)
:
: I can't tell whether the error is a system resource error or if it's some
: limitation in excel.
:
:
: Any suggestions?
:
: Your country owes the one who helps me a debt of gratitude.
: -=Drew



  #7  
Old August 5th, 2004, 06:35 PM
hgrove
external usenet poster
 
Posts: n/a
Default Trying to import several million rows- help!

-=Drew wrote...
...
I know that Excel is not the optimal tool- my problem is that I
need to go step by step to get the data into a DB- ultimately a
SQL w/ Access Front End interface.

As I said before, I would love to chop it into sequential pieces
as it would make the data much easier to handle, but MS Word
takes about 3.5 hours to open this sucker, and the cut/paste
options are grey'ed out when i try to hack it into 10000 page
sections. (FYI- it's so big, I haven't had the patience to see how
long it is, but it's probably between 70,000-100,000 pages of
flat text records)

1. Can anyone recommend a more powerful text editor than
Word to try out? If i'm able to parse it into smaller files- i.e.
those of less than 1.048 million lines, then i'm in business.


Don't use any text editor. Don't use any nonbatch software at all. This
is a old fashioned DP task, and an old fashioned DP is best.

If you can use other software, consider downloading an installing the
GnuWin32 text utilities,

http://sourceforge.net/project/showf...ckage_id=26492

which includes one named split which provides may options for splitting
any sort of file. Piece of cake splitting a large text file at each
formfeed or ever N lines.

2. Can anyone recommend/write a better script to enter the
data into Excel?


If the file takes forever to load into Word, and Word doesn't let you
do anything useful with it, Excel would be MUCH, MUCH worse. That said,
you could use something like this.


Sub foo()
Const YOUR_FILENAME As String = "d:\tmp\deleteme.prn"
Const YOUR_LINES_PER_FILE As Long = 100

Dim ifd As Integer, ofd As Integer
Dim k As Long, n As Long, s As String

ifd = FreeFile
Open YOUR_FILENAME_HERE For Input As #ifd

Do While Not EOF(ifd)

Line Input #ifd, s
n = n + 1

If n Mod YOUR_LINES_PER_FILE = 1 Then
If k 0 Then Close #ofd
k = k + 1
ofd = FreeFile
Open YOUR_FILENAME & Format(k, "\.0000") For Output As #ofd
End If

Print #ofd, s

Loop

Close #ofd
Close #ifd

End Sub


---
Message posted from http://www.ExcelForum.com/

  #8  
Old August 5th, 2004, 06:37 PM
hgrove
external usenet poster
 
Posts: n/a
Default Trying to import several million rows- help!

hgrove wrote...
...
Open YOUR_FILENAME_HERE For Input As #ifd


make that

Open YOUR_FILENAME For Input As #ifd


---
Message posted from http://www.ExcelForum.com/

  #9  
Old August 6th, 2004, 09:33 AM
Jamie Collins
external usenet poster
 
Posts: n/a
Default Trying to import several million rows- help!

-=Drew wrote ...

I know that Excel is not the optimal tool- my problem is that I need to go
step by step to get the data into a DB- ultimately a SQL w/ Access Front End
interface.


You may be able to use ADO to query the text file to get meaningful
in-memory *sets* of data:

http://msdn.microsoft.com/library/de...ng03092004.asp

Row-by-row processing must surely be impractical. How long have you
forecasted it will take you to step through 3 million rows? Are you
planning to transform you data with 3 million cell formulas? Don't hit
F9 g.

Jamie.

--
  #10  
Old August 9th, 2004, 07:33 AM
Alan
external usenet poster
 
Posts: n/a
Default Trying to import several million rows- help!

"-=Drew" wrote in message
...

All-

Thank you for the quick responses.
I know that Excel is not the optimal tool- my problem is that I need
to go step by step to get the data into a DB- ultimately a SQL w/
Access Front End interface.

As I said before, I would love to chop it into sequential pieces as
it would make the data much easier to handle, but MS Word takes

about
3.5 hours to open this sucker, and the cut/paste options are grey'ed
out when i try to hack it into 10000 page sections. (FYI- it's so
big, I haven't had the patience to see how long it is, but it's
probably between 70,000-100,000 pages of flat text records)

1. Can anyone recommend a more powerful text editor than Word to try
out? If i'm able to parse it into smaller files- i.e. those of less
than 1.048 million lines, then i'm in business.
2. Can anyone recommend/write a better script to enter the data into
Excel?


What was the outcome o trying to use Access?

Did it work?

Alan.


 




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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Inserting multiple rows into a table while maintaining data? JAnderson General Discussion 2 July 8th, 2004 05:45 PM
Import the Address Book from Palm Desktop to Pocket PC jdramosgo Contacts 1 May 27th, 2004 11:55 PM
Access2000: Copy a range of rows to another range of rows using VBA Arvi Laanemets Worksheet Functions 1 May 10th, 2004 08:48 AM
trying to run a sort to highlight rows and clear the cells in those rows.... James Worksheet Functions 2 December 23rd, 2003 02:38 PM


All times are GMT +1. The time now is 04:44 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.