Handling very large files (too many rows)

I'm trying to analyse a set of data. However, the data is a CSV fil
with almost 1.5 million rows, which somewhat excedes the 65 thousand o
so that excel can handle. However, excel is what I'm used to. If ther
any convenient way to import so much data? Alternately, can anyon
recomend a different means to observe and process that amount of data

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

0
7/15/2004 10:28:32 AM
excel.misc 78881 articles. 5 followers. Follow

11 Replies
789 Views

Similar Articles

[PageSpeed] 20

I really think that is way too much for Excel, even if you break it into bits.

What you should use depends on how you want to analyse the data. Access can handle that quantity of data. Maybe, if you are going to filter the data down to a manageable subset or summary you could import it to Access and then query it using MS Query in Excel if using Excel is important to you.

"Kingston999 >" wrote:

> I'm trying to analyse a set of data. However, the data is a CSV file
> with almost 1.5 million rows, which somewhat excedes the 65 thousand or
> so that excel can handle. However, excel is what I'm used to. If there
> any convenient way to import so much data? Alternately, can anyone
> recomend a different means to observe and process that amount of data.
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 
> 
0
Vaughan (64)
7/15/2004 10:51:01 AM
Do you really need all 1.5 million rows?

If not, maybe you could reduce the rows to import before you try to import it.

(your favorite text editor or even a little program that keeps just the records
you want.)

"Kingston999 <" wrote:
> 
> I'm trying to analyse a set of data. However, the data is a CSV file
> with almost 1.5 million rows, which somewhat excedes the 65 thousand or
> so that excel can handle. However, excel is what I'm used to. If there
> any convenient way to import so much data? Alternately, can anyone
> recomend a different means to observe and process that amount of data.
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
7/15/2004 11:05:31 PM
In reply to the clarifications requested above:

1. It looks like I do need at least a substantial  proportion of th
rows. However,  being able to import every tenth value might work, i
someone could suggest a way of doing that.

2. The reason I'm trying to use Excel is that I know how to use it. 
don't know how to use Access, for example, and I am not sure what els
I could use. If anyone could suggest a different program suitable fo
analysising large amounts of data, I'd be very grateful

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

0
7/16/2004 10:42:29 AM
Depending what you mean by analysis, you may be able to use Data|Pivottable and
point at the .CSV file.

Or put the data in Access and use excel to extract it.

(I've read posts that this is possible--I don't use Access.)

If you don't get enough help here, you may want to go to one of the Access
newsgroups and tell them your problem.  Maybe it'll turn into a simple (haha)
solution.



"Kingston999 <" wrote:
> 
> In reply to the clarifications requested above:
> 
> 1. It looks like I do need at least a substantial  proportion of the
> rows. However,  being able to import every tenth value might work, if
> someone could suggest a way of doing that.
> 
> 2. The reason I'm trying to use Excel is that I know how to use it. I
> don't know how to use Access, for example, and I am not sure what else
> I could use. If anyone could suggest a different program suitable for
> analysising large amounts of data, I'd be very grateful.
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
7/16/2004 9:56:24 PM
Vaughanwrote...
>As far as I know, there isn't any spreadsheet that will cope with
>1.5m rows.
...

I can't either, though Quattro Pro could handle 1.0 million rows

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

0
7/16/2004 10:36:12 PM
Kingston999 wrote...
...
>1. It looks like I do need at least a substantial  proportion of the
>rows. However,  being able to import every tenth value might
>work, if someone could suggest a way of doing that.

Every 10th row would still mean approx. 150,000 rows, and that stil
can't fit into a single worksheet, so you'd still be faced with th
same sort of problems (analyzing data spanning multiple worksheets) a
you would if you imported all 1.5 million rows.

What *specifically* are you trying to do?

>2. The reason I'm trying to use Excel is that I know how to use
>it. I don't know how to use Access, for example, and I am not
>sure what else I could use. If anyone could suggest a different
>program suitable for analysising large amounts of data, I'd be
>very grateful.

If all you know how to use is a hammer, everything looks like nails.

Spreadsheets are only good at analyzing small to moderate amounts o
data. 1.5 million records is way to much for any spreadsheet to handl
at all well.

What you should use depends critically on what you're trying to do. S
what are you trying to do

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

0
7/16/2004 10:42:03 PM
Hi!

Slightly OT.

The thought occurs that there might be occasions when "paralle
processing" could be used to extend the usable rows of Excel.

What I mean is: if the content of the rows runs to, say, 32 column
(A-AF) then A-IV would accommodate 8 such parallel sets of columns
each set self-contained. That could, effectively, give access to 8 
(2^16-1) working rows: say 0.5 million.

I've never tried such a device, but it has to work (in theory), doesn'
it? The practice?
Al

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

0
7/18/2004 7:53:09 PM
I'm sure you could do something with the data--but you'd lose a lot of the stuff
that you can do in excel easily--just inserting/deleting rows would be a pain.

And I know that when I approach 40k-50k rows (and 50 columns (say)) in a single
worksheet (with lots of vlookup()'s and other formulas), excel can slow down to
almost a halt.





"AlfD <" wrote:
> 
> Hi!
> 
> Slightly OT.
> 
> The thought occurs that there might be occasions when "parallel
> processing" could be used to extend the usable rows of Excel.
> 
> What I mean is: if the content of the rows runs to, say, 32 columns
> (A-AF) then A-IV would accommodate 8 such parallel sets of columns,
> each set self-contained. That could, effectively, give access to 8 x
> (2^16-1) working rows: say 0.5 million.
> 
> I've never tried such a device, but it has to work (in theory), doesn't
> it? The practice?
> Alf
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
7/18/2004 11:50:34 PM
Specifically, I am looking at recorded movement of a part in a machin
over time. I am trying to find the peak values.

However, I am tending to agree with you that Excel isn't the best too
for the job, which is why I am also interested in what you'd sugges
instead

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

0
7/19/2004 9:35:24 AM
I don't have a suggestion.

The last time I had to work with that many rows, I brought it to our mainframe
and trimmed it down so that it would fit into excel.  

"Kingston999 <" wrote:
> 
> Specifically, I am looking at recorded movement of a part in a machine
> over time. I am trying to find the peak values.
> 
> However, I am tending to agree with you that Excel isn't the best tool
> for the job, which is why I am also interested in what you'd suggest
> instead.
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
7/19/2004 10:20:20 PM
> Specifically, I am looking at recorded movement of a part in a machine
> over time. I am trying to find the peak values.

Do you have no experience with some programming language?  Even BASIC would 
serve and be easy to learn.

Bill -- (Remove KILLSPAM from my address to use it)
0
7/20/2004 4:23:44 PM
Reply:

Similar Artilces:

How do you change the rows to columns and columns to rows.
Ok, I am stumped on how to do this? I have a spreadsheed that has Dates in the A column and Data in the row. I want to make is so, the data is in the A column and Dates ru accross in the 1 column. How can I do this? Sound easy, but I am stumped??? :eek -- cc4digita ----------------------------------------------------------------------- cc4digital's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2656 View this thread: http://www.excelforum.com/showthread.php?threadid=39843 Highlight your data and copy it into the clipboard. Move to an open spot in the sprea...

how to freeze pane with only one row?
Hello all, I am trying to create a view so that I can see my header row as I scroll down a table with thousands of records/rows. When I select this row1 and then go to Window->Freeze Panes, I get a pane that contains the first 12 rows. I would like just the 1st row to be frozen in this pane. Have tried selecting the entire row and just the columns in the first row that are required - to no avail. Can anyone help me do this? Thanks! symp news, select a cell in row 2 then freeze panes -- Paul B Always backup your data before trying something new Please post any response to the newsgroups...

Hiding and Unhiding Rows.. Unhide does not work..
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I use this formating feature quite often. But, on this one worksheet which is a rather large data list (6000 Rows) I can NOT unhide rows that I have previously hidden. It just does not work. <br><br>I have other workbooks open and I have no problem hiding and unhiding in the normal way. <br><br>The workbook is NOT protected. The cells are not locked. But the file was originally a windows file. <br><br>Is there some switch I do not know about? This is possibly a rare occurren...

Hidden files in Ms-Query cause ODBC connect errors or Query is wac
I have a Query fetch_from_bob that moves data (collapses records,unique key search, etc...) from excel spreadsheet "bob" to excel spreadsheet "fred". Fred and bob live in the same directory" c:\123directory" on the same computer. I allow this query to execute/refresh data automatically when one of the query paramaters is changed. This works great. I also have a macro that invokes the query to refresh the data whenever the user desires. This works great as well. The macro literally has all the VB code to execute the query. But I'm just a part time progra...

Outlook: Can't open JPEG files
I have Outlook 2000 an unable to open JPEG files. What setting has to be changed to open these files? ...

File Edit toolbar is missing
The File Edit toolbar in Excel is missing First thing to check: Tools|customize|toolbars tab|make sure worksheet menubar is selected. Well, maybe the first thing to check is to verify that you can see all of excel--it's not just off the display window??? stellarmellor wrote: > > The File Edit toolbar in Excel is missing -- Dave Peterson ...

8192 rows limit when opening wk1 files ?
Hello, I am opening a .wk1 file, which contains around 60K rows, but only the first 8192 rows appear in Excel. Is there any way to have all the rows in the Excel file ? Thanks, Paul "paul" wrote... >I am opening a .wk1 file, which contains around 60K rows, >but only the first 8192 rows appear in Excel. > >Is there any way to have all the rows in the Excel file ? WK1 as in Lotus 1-2-3 Release 2.x native file format? Such .WK1 files support a single worksheet of 256 columns and 8,192 rows. PERIOD. Did you generate this '.WK1' file using a program other then...

Select Rows dependant on certain field data
How can I select certain rows in a spreadsheet based upon the text foun in one column? To explain: All rows have a column that contains tex "immediate" or "ongoing" or "closed". How can I select only the row that contain the word "immediate" in that column? When I say select a mean select as in ready to 'copy' or 'cut' tha data ready to be pasted elsewhere. I know what I mean :confused -- Madd ----------------------------------------------------------------------- Maddy's Profile: http://www.excelforum.com/member.php?action=getinf...

Best way to read an xml file
Hi; If I need to read an XML file - basically the SAX approach where I just need each node in order - one pass, read-only - what is the best way to do this. It looks like it's XmlReader but I want to verify that. ??? -- thanks - dave "David Thielen" <thielen@nospam.nospam> wrote in message news:BEA51BCB-B200-4104-B061-CA32FECD9DFD@microsoft.com... > If I need to read an XML file - basically the SAX approach where I just need > each node in order - one pass, read-only - what is the best way to do this. Yes, if your only requirement is a firehose forward reader,...

Updating large pivot source data
Hello, I'm experiencing the following problem with a very simple macro in Excel 2007 to update pivot table data source. That is, my data has more than 74000 rows and I'm trying to update the source data with the following code: Sub Macro1() Dim rng1 As Range Set rng1 = Sheets("Test_sheet").Range("A1:A74000") ActiveSheet.PivotTables("Pivot-taulukko1").ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng1, _ Version:=xlPivotTableVersion12) End Sub The problem is that the code ...

Large Files with Strongly Typed Dataset
I have an xsd file that I use to create a strongly typed dataset in my project. In the past, I have used the ReadXml method to load xml files into the generated class and read data in using this class. These files have now become big (order of 100MB) and this completely kills the readxml performance. I have read multiple posts that tell you not to use datasets for large files. I even tried calling BeginLoadTables before starting the read. However, I really need the strongly typed dataset functionality in my program - it forms the core of my project. Is there a way I can load this data into t...

Missing rtl70bpl file
After receiving what seemed to be a false positive from Avast free Home edition I now get (after every restart), the following... "This application has failed to start because rtl70bpl was now found. Reinstalling the application may fix the problem". Appreciate any advice on how to reinstall same. Have Googled for help but have not found the correct site. TIA. franktee Winxp Pro SP3 On Dec 3, 11:42=A0am, franktee <frank...@discussions.microsoft.com> wrote: > After receiving what seemed to be a false positive from Avast free Home > edition I now get (afte...

Inconsistent Meeting Request handling
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange Entourage meeting requests do not consistently provide attendee lists, meeting detail content, and/or ability to accept/decline. <br><br>As they arrive in my Inbox, <br> &nbsp;-- some requests display a yellow heading box with hypertext blue text that lets me accept or decline or tentative. <br> -- some requests do not have this heading box, but I can right-click on the inbox item and see &quot;accept/decline/tentative&quot; context menu choices <...

Keeping Archive File in Folders List
I have a colleague who placed the Archive file at the top of her list of folders in Outlook so it is easy to open the Archive file and search for a message or appointment if necessary. She can't remember how she managed to do this and I would love to do this myself.Any suggestions? Thank You File-> OPen-> Outlook Data File... and browse to archive.pst -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Joan" <anonymous@discussions.microsoft....

Error saving Excel files in a network drive
I have a problem saving Excel files onto a network drive. I get an error saying it was imposible to save the file. It creates a temporary file and then I have to open it and save it as a new document. This issue doesn�t occur saving the file in my hard disk. This happens with "Full control" access to the shared folder... I have Windows XP and Office 2000. Thanks in advance Mateo. Hi Mateo, > I have a problem saving Excel files onto a network drive. I get an error > saying it was imposible to save the file. It creates a temporary file and > then I have to open it and sav...

File in use .... is locked for editing
Please help. In Microsoft Excel 2000, we seem to be getting the above error message frequently. No other users are in the file, and there aren't any temporary files relating to the file in question. Any ideas on what is causing this? We are running a mixture of Windows 2000 & XP machines on to a Small Business Server 2000. Hope to hear from someone soon Regards Simon I think you killed the usual answer of cleaning the temp folder. But have you rebooted? Maybe there's a hidden instance of excel running that "owns" the file. And if the file/workbook is on a net...

VSTO 2005: Error in Schema Files Generation
In XML schema generation from a WordML document, VSTO 2005 VSTO creates 6 files (general naming pattern): XML.XSD ... XML5.XSD. For our file names we use a special syntax for additional information on the project status. - Syntax for file names: {PRE}={TOPIC}_{POST}.{STATUS}.{FORMAT} Now, VSTO 2005 modifies its general naming pattern to XML.{STATUS}{I}.{FORMAT}. - Example - XML.ABCD.XSD - XML.ABCD1.XSD (instead of XML1.ABCD.XSD) - ... - XML.ABCD5.XSD (instead of XML5.ABCD.XSD) I consider this behavior of VSTO 2005 as a programming error. - Is this consideration correct? - How ...

No menu bars appear on newly installed Outlook 2003 (PST file)
I just built up a new system (Windows XP SP2, Office 2003 Pro) and move my Outlook.pst file over from my old system. Since I had Norton Internet Security 2005 installed, the anti-spam toolbar was displayed. However, after only a few hours of usage, the menu bars are not working properly: 1. When I start up Outlook and mail is displayed, there are no menu bars displayed. None. 2. If I go to the lower left corner navigation bar and select mail and Open in New Window, the standard and formatting menu bars are displayed. But the Norton Anti-Spam bar does not display. 3. When I open an indiv...

Reading EML files
Does anyone know if Outlook can be configured to open .eml files? I wrote a program that creates .eml files. Outlook Express can open them but Outlook can't. Is there a way to get Outlook to open these? If not, does anyone know how I can create a file that Outlook can open (such as a .msg)? Is there a COM object that creates these? Any help will be appreciated. John ...

WMP12 Bug with Large Libraries (confirmed and reproducable)
I have been working with a few of the guys over at we got served and have discovered a live bug in the windows media player version as shipped with windows 7 ultimate x86 I have a home server (WHS) and a media center (Win7). the home server runs PP3 and has 42,000 mp3's in the music share. All mp3s are well tagged and have embedded album art. when windows media player starts scanning the folder in order to import music it gets to 32750 (integer maximum value) and hangs leaving the graphical elements of media player corrupted. this stays like this for approx 5 minutes...

RPC .x files.
I need to talk to some third party equipment. The company has provided an RPC interface consisting of three .x files. The provided third party documentation just glosses over how to compile and use these files. Though the equipment is new, I think this interface is quite old. The documentation example says to use the command "rpcgen32.exe filename". They say the command my differ depending on the software package. I searched the Microsoft site and found a bunch of stuff. At first I looked at the midl compiler. The .idl files look different then the .x files. Can someone please poin...

automatic insertion of row
If I insert one row to a block of data in one sheet, how can this be automatically added to another sheet with the same block of data? Thanks Group the worksheets prior to inserting the row by selecting the first worksheet then hold down the Ctrl key while selecting other worksheets. -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "renegade" wrote: > If I insert one row to a block of data in one sheet, how can this be > automatically added to another sheet with the same block of data? Thanks ...

Money 2005 Delux runs too slowly: after many years of upgrading
I have been using Money since 1996. I have upgraded each year, each time upgrading the file. I also archive all trasactions from Jan 1 - Dec 31. This program runs very slowly even without all the extra transactions. I can't figure it out. I download transactions and it takes forever for them to show up in the register. When I accept them, it takes forever for that to take. This was also the case in Money 2004. Are there any reccomendations? Should I start a new file and import the tranactions? HELP In microsoft.public.money, AMW wrote: >I have been using Money since 1996. I...

convert .csv file to .cub using access
I am using Microsoft Access to work with a .CSV file that contains 250,000 rows of information. I'd like to convert that file to a .CUB file so that I can use Microsoft Data Analyzer to "drill" down the information. Does anyone have any ideas? Hi John, As I understand it, the normal way would be to use the Cube Wizard in the OLAP Manager, after importing and normalising the file into SQL Server tables. It may be possible to use the Cube Wizard against an ODBC connection to an MDB file. But as I understand it the point of using OLAP cubes is to denormalise a set of views...

Combining VISIO files with mail merged Word document
I am trying to create a composite document that has four pages. The first page is a VISIO (2003 Professional) file that contains several pictures and a converted AutoCAD (R14) drawing file. The following three pages are a Word merged file created from an Access database. I would like to be able to have a combined file for for hard copy reproduction. This particular document has 220 reports that all have the same basic format. I have tried exporting various file types (tif, jpg, wmf, bmp, etc.) from VISIO, then inserting into Word as picture or object and most are unacceptable quali...