-
We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.
MS Excel: Anyway to put in more than 65k records?
- Thread starter uCsDNerd
- Start date
PrincessGuard
Golden Member
There's no built-in way to do it.
It's Microsoft's way of "encouraging" you to buy Access.
It's Microsoft's way of "encouraging" you to buy Access.
I'm analyzing bandwidth stats files that probably contain 1 million+ records in each file. I was hoping excel would take in these records and compute the sums and deviations for me, and then give me a plot of the data points. Perhaps matlab may be a better choice?
The file just under a gig in size, does anyone know of a script that could possible cut this file down into several 65k records? I've worked with linux scripts before, but never windows batch/script files.
About access:
Does access have a record limitation? I've never really played around with access , is it capable of doing the same tasks as excel?
Thanks for the info everyone!
The file just under a gig in size, does anyone know of a script that could possible cut this file down into several 65k records? I've worked with linux scripts before, but never windows batch/script files.
About access:
Does access have a record limitation? I've never really played around with access , is it capable of doing the same tasks as excel?
Thanks for the info everyone!
PrincessGuard
Golden Member
Access has something like a 1 or 2 GB limit per table and you can have multiple tables open at the same time. It is a database so it's not quite like excel. But SQL isn't hard to learn. Doing analysis on the data may be more difficult though.
Matlab most likely can't handle that much data.
If the data is in plain-text lines, then writing a small program to break the file apart is pretty simple.
Matlab most likely can't handle that much data.
If the data is in plain-text lines, then writing a small program to break the file apart is pretty simple.
geoff2k
Golden Member
What do the file records look like? I'd tend towards writing something in perl to do the summaries then output a CSV file for sending into Excel.
As for chopping the file into batches of 65000 K records, you could either use split (standard Unix command -- use it with the -l parameter) or:
#!/usr/bin/perl
$i = 0;
$fileno = 0;
open OUT, sprintf(">split.file.%03d",$fileno);
while ( <STDIN> )
{
print OUT $_;
$i++;
if ( $i == 65000 )
{
close OUT;
$fileno++;
open OUT, sprintf(">split.file.%03d",$fileno);
$i = 0;
}
}
close OUT;
That should do the trick. Save it as splitter.pl and use as follows:
perl splitter.pl < bigfile.txt
As for chopping the file into batches of 65000 K records, you could either use split (standard Unix command -- use it with the -l parameter) or:
#!/usr/bin/perl
$i = 0;
$fileno = 0;
open OUT, sprintf(">split.file.%03d",$fileno);
while ( <STDIN> )
{
print OUT $_;
$i++;
if ( $i == 65000 )
{
close OUT;
$fileno++;
open OUT, sprintf(">split.file.%03d",$fileno);
$i = 0;
}
}
close OUT;
That should do the trick. Save it as splitter.pl and use as follows:
perl splitter.pl < bigfile.txt