\*\*However\*\*:
If the file is truly CSV (numeric values unquoted, date and time stamps in official format for your locale, and all text fields quoted) there is actually a way you can treat it like a table in a database with ODBC. Open your little ODBC data sources gizmo and create a "File DSN". Use the "text" driver. It will let you configure it so that the "database" is the directory where your CSV file lives and your CSV file is a table in that database. Then anything that can connect to an ODBC Data Source Name will be able to query your CSV file.
If the sort is numeric, add a "-n" to the sort command above. If it's descending, add "-r" and if it's both numeric and descending add "-nr". "man sort" is your friend.
> **路路路**
>
> --
> M. Edward (Ed) Borasky, FBG, AB, PTA, PGS, MS, MNLP, NST, ACMC(P)
> [http://borasky-research.blogspot.com/](http://borasky-research.blogspot.com/)
>
> If God had meant for carrots to be eaten cooked, He would have given rabbits fire.
---
### Author:  [@Reid\_Thompson1](https://rubytalk.org/u/Reid_Thompson1)
#### Post date: [29 November 2006 20:26 UTC](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312/24 "2006-11-29T20:26:28Z")
rthompso@jhereg:~$ cat splitandhead
#!/bin/bash
fname=$1
split -l 4000 -a 6 -d $fname $fname
for file in `ls ${fname}0*`
do
sed -e '1i\Col1 Col2 Col3 Col4\' $file \> ${file}.tmp
mv ${file}.tmp $file
done
rthompso@jhereg:~$ head testsed
this is a test
this is a test
this is a test
this is a test
this is a test
this is a test
this is a test
this is a test
this is a test
this is a test
rthompso@jhereg:~$ wc testsed
40140 160560 602100 testsed
rthompso@jhereg:~$ time ./splitandhead testsed
real 0m0.499s
user 0m0.140s
sys 0m0.092s
rthompso@jhereg:~$ head -2 testsed0000\*
==\> testsed000000 \<==
Col1 Col2 Col3 Col4
this is a test
==\> testsed000001 \<==
Col1 Col2 Col3 Col4
this is a test
==\> testsed000002 \<==
Col1 Col2 Col3 Col4
this is a test
==\> testsed000003 \<==
Col1 Col2 Col3 Col4
this is a test
==\> testsed000004 \<==
Col1 Col2 Col3 Col4
this is a test
==\> testsed000005 \<==
Col1 Col2 Col3 Col4
this is a test
==\> testsed000006 \<==
Col1 Col2 Col3 Col4
this is a test
==\> testsed000007 \<==
Col1 Col2 Col3 Col4
this is a test
==\> testsed000008 \<==
Col1 Col2 Col3 Col4
this is a test
==\> testsed000009 \<==
Col1 Col2 Col3 Col4
this is a test
==\> testsed000010 \<==
Col1 Col2 Col3 Col4
this is a test
rthompso@jhereg:~$
> **路路路**
>
> On Thu, 2006-11-30 at 04:44 +0900, Drew Olson wrote:
>
> > Reid Thompson wrote:
> > \> On Thu, 2006-11-30 at 02:25 +0900, ChrisH wrote:
> > \>\> Also since he's feeding Excel this is most likely on Windows rather
> > \>\> than \*NIX
> > \> not a show stopper..
> > \> [error](http://www.research.att.com/sw/tools/uwin/)
> > \>
> > \> [http://unxutils.sourceforge.net/](http://unxutils.sourceforge.net/)
> >
> > Reid -
> >
> > I also have cygwin installed on my box, so if there is a simply way to
> > do this in unix I'd love to know. My unix is rusty (that's being
> > generous).
---
### Author:  [@Reid\_Thompson1](https://rubytalk.org/u/Reid_Thompson1)
#### Post date: [29 November 2006 20:55 UTC](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312/25 "2006-11-29T20:55:24Z")
sort -n +4 out
or
sort +4 out
rthompso@jhereg:~$ head -50 out
this is a test 10202
this is a test 23990
this is a test 11056
this is a test 9606
this is a test 28590
this is a test 18264
this is a test 12902
this is a test 12856
this is a test 27571
this is a test 5495
this is a test 15965
this is a test 22229
this is a test 18865
this is a test 31339
this is a test 21913
this is a test 406
this is a test 8602
this is a test 5329
this is a test 10048
this is a test 6458
this is a test 20069
this is a test 19771
this is a test 21844
this is a test 24719
this is a test 30894
this is a test 25239
this is a test 9900
this is a test 1727
this is a test 12042
this is a test 20832
this is a test 23735
this is a test 28768
this is a test 10283
this is a test 390
this is a test 10480
this is a test 1337
this is a test 2745
this is a test 26398
this is a test 32288
this is a test 3797
this is a test 22251
this is a test 458
this is a test 14679
this is a test 29642
this is a test 19943
this is a test 26342
this is a test 24232
rthompso@jhereg:~$ sort +4 out
this is a test 0
this is a test 10
this is a test 10
this is a test 100
this is a test 100
this is a test 100
this is a test 1000
this is a test 1000
this is a test 10001
this is a test 10001
this is a test 10005
this is a test 10006
this is a test 10006
this is a test 10007
this is a test 10007
this is a test 10008
this is a test 10008
this is a test 10009
this is a test 10009
this is a test 1001
this is a test 10010
this is a test 10011
this is a test 10012
this is a test 10012
this is a test 10012
this is a test 10012
this is a test 10012
this is a test 10013
this is a test 10014
this is a test 10014
this is a test 10015
this is a test 10017
this is a test 10017
this is a test 10017
this is a test 10018
this is a test 10019
this is a test 1002
this is a test 1002
this is a test 10021
this is a test 10026
this is a test 10026
-------------------- OR
rthompso@jhereg:~$ sort -n +4 out
this is a test 0
this is a test 2
this is a test 3
this is a test 3
this is a test 4
this is a test 7
this is a test 10
this is a test 10
this is a test 12
this is a test 13
this is a test 14
this is a test 14
this is a test 15
this is a test 16
this is a test 16
this is a test 20
this is a test 21
this is a test 24
this is a test 25
this is a test 28
this is a test 29
this is a test 29
this is a test 29
this is a test 30
this is a test 30
this is a test 31
this is a test 32
this is a test 32
this is a test 34
this is a test 35
this is a test 35
this is a test 35
this is a test 36
this is a test 36
this is a test 37
this is a test 37
this is a test 38
this is a test 38
this is a test 41
this is a test 41
this is a test 41
this is a test 41
this is a test 41
this is a test 41
this is a test 42
this is a test 42
this is a test 42
this is a test 43
this is a test 45
this is a test 46
this is a test 46
this is a test 47
this is a test 47
> **路路路**
>
> On Thu, 2006-11-30 at 04:04 +0900, James Edward Gray II wrote:
>
> > On Nov 29, 2006, at 12:56 PM, Drew Olson wrote:
> >
> > \> Here's another question: in some cases I need to sort the
> > \> file before splitting it (in this case sorting by the 4th cell in each
> > \> row). However, the current file I'm trying to sort and split is around
> > \> 76 MB and ruby fails when trying to store the CSV as an array. The
> > \> code
> > \> and output are below. How else can I go about this?
> >
> > Hmm, that's a good question.
> >
> > Perhaps you can collect just the key values in an Array and then use
> > those to reorder the lines bit by bit. That's not going to be fast
> > with any library helping you, but I don't have a better idea.
> >
> > James Edward Gray II
---
### Author:  [@Edwin\_Fine](https://rubytalk.org/u/Edwin_Fine)
#### Post date: [29 November 2006 23:25 UTC](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312/26 "2006-11-29T23:25:52Z")
I am perplexed by CSV.open. In IO and File, open returns something that
quacks like an IO object. You can then call gets, puts, read, write and
so on. The CSV open seems to return an array (or gives you a row at a
time). This makes it very hard to write code that can take advantage of
duck typing, e.g.
def get\_batch(f, count)
lines = nil
count.times do
break unless line = f.gets
(lines ||= []) \<\< line
end
lines
end
get\_batch(File.open("foo"), 40000) # No problem
get\_batch(CSV.open("foo", "r"), 40000) # Error
I wonder why it was not patterned more closely on IO? Any thoughts?
> **路路路**
>
> --
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).
---
### Author:  [@M\_Edward\_Ed\_Borasky1](https://rubytalk.org/u/M_Edward_Ed_Borasky1)
#### Post date: [30 November 2006 04:12 UTC](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312/27 "2006-11-30T04:12:56Z")
Paul Lutus wrote:
> James Edward Gray II wrote:
>
[snip]
Hmmmm ... seems like a bunch of folks are going to a bunch of trouble and to baby a tool that can only handle a CSV file with less than 65536 rows. Excel is broken ... Excel sucks at math ... Excel can't handle lots of rows and lots of columns ... Excel costs money. Excel was a great wonder in 1986 -- 20 years later it's lame beyond words.
I'm not sure about the column count limitations, but I know almost any database will deal with a CSV file with more than 65536 rows. There are some awfully good free databases. Did I mention how bad Excel is at math? Sometimes you \*don't\* get what you pay for. 馃檪
> **路路路**
>
> --
> M. Edward (Ed) Borasky, FBG, AB, PTA, PGS, MS, MNLP, NST, ACMC(P)
> [http://borasky-research.blogspot.com/](http://borasky-research.blogspot.com/)
>
> If God had meant for carrots to be eaten cooked, He would have given rabbits fire.
---
### Author:  [@Lou\_Scoras](https://rubytalk.org/u/Lou_Scoras)
#### Post date: [30 November 2006 21:13 UTC](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312/28 "2006-11-30T21:13:46Z")
Indeed. That problem is difficult in general because you need to have
the whole set of elements in memory before you can begin sorting them.
As James pointed out, you might be able to use some sort of
memoization technique to track only the bits relevent to sorting. The
problem is you'll also need some way to get back to the original
record.
Depending on how you ending up parsing the records, you might be able
to store the file position of the start of the record and the record
length.
Records -\> [sort\_key, file.pos, record.length]
Then sort those arrays if you can fit them all in memory. Finally,
you can use the offsets for random access to grab the records and
stick them into the new files as you've been doing.
Basically, you're looking at a complicated swartzian transformation.
If it will work depends on how big your records are. If they are
fairly large, you might be able to pull if off; however, if they're
small and the problem is only that there are too many records, you'll
still have a problem.
In that case, you might want to just shove them in an RDBMS and let it
sort it for you.
> **路路路**
>
> On 11/29/06, James Edward Gray II \ wrote:
>
> > On Nov 29, 2006, at 12:56 PM, Drew Olson wrote:
> >
> > \> Here's another question: in some cases I need to sort the
> > \> file before splitting it (in this case sorting by the 4th cell in each
> > \> row). However, the current file I'm trying to sort and split is around
> > \> 76 MB and ruby fails when trying to store the CSV as an array. The
> > \> code
> > \> and output are below. How else can I go about this?
> >
> > Hmm, that's a good question.
> >
> > Perhaps you can collect just the key values in an Array and then use
> > those to reorder the lines bit by bit. That's not going to be fast
> > with any library helping you, but I don't have a better idea.
> >
> > James Edward Gray II
>
> --
> Lou.
---
### Author:  [@M\_Edward\_Ed\_Borasky1](https://rubytalk.org/u/M_Edward_Ed_Borasky1)
#### Post date: [1 December 2006 02:57 UTC](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312/29 "2006-12-01T02:57:34Z")
Thomas Mueller wrote:
> **路路路**
>
> > 2006/11/30, Drew Olson \:
> >
> > > I'll give FasterCSV a try when I get home from work and out from behind
> > > this proxy. Here's another question: in some cases I need to sort the
> > > file before splitting it (in this case sorting by the 4th cell in each
> > > row). However, the current file I'm trying to sort and split is around
> > > 76 MB and ruby fails when trying to store the CSV as an array. The code
> > > and output are below. How else can I go about this?
> >
> > You could import it into MS Access, sort there and export again. Maybe
> > you could even do all the splitting up in Access directly, but I don't
> > know too much about Access to help with that.
> > And I guess using Access would make it harder to automate this whole
> > task, in case that's required.
> >
> > That's not very "rubyish", of course 馃檪
> >
> > Thomas
>
> Well ... \*databases\* are highly Ruby-ish! Every major database, including, I'm guessing, MS Abscess\*, has a simple Ruby interface. Reading a huge file into memory is the wrong approach.
>
> \* Abscess -- An abscess is a localized collection of pus in any part of the body, caused by an infection.
>
> > **[Abscess: MedlinePlus Medical Encyclopedia](https://medlineplus.gov/ency/article/001353.htm)**
> >
> > An abscess is a collection of pus in any part of the body. In most cases, the area around an abscess is swollen and inflamed.
>
> --
> M. Edward (Ed) Borasky, FBG, AB, PTA, PGS, MS, MNLP, NST, ACMC(P)
> [http://borasky-research.blogspot.com/](http://borasky-research.blogspot.com/)
>
> If God had meant for carrots to be eaten cooked, He would have given rabbits fire.
---
### Author:  [@James\_Cribbs](https://rubytalk.org/u/James_Cribbs)
#### Post date: [1 December 2006 03:34 UTC](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312/30 "2006-12-01T03:34:55Z")
Thomas Mueller wrote:
> > I'll give FasterCSV a try when I get home from work and out from behind
> > this proxy. Here's another question: in some cases I need to sort the
> > file before splitting it (in this case sorting by the 4th cell in each
> > row). However, the current file I'm trying to sort and split is around
> > 76 MB and ruby fails when trying to store the CSV as an array. The code
> > and output are below. How else can I go about this?
I'm coming to this party really late, so I hope I don't come across as shamelessly plugging KirbyBase, but, you might want to try it for this.
If you are simply trying to take a large csv file, sort it by one of its fields, and split it up into smaller files that each contain 40,000 records, I think it might work.
Here's some code (not tested, could be incorrect) off the top of my head:
require 'kirbybase'
db = KirbyBase.new
tbl = db.create\_table(:foo, :field1, :String, :field2, :Integer, :field3, :String............................
tbl.import\_csv(name\_of\_csv\_file)
rec\_count = tbl.total\_recs
last\_recno\_written\_out = 0
while rec\_count \> 0
recs = tbl.select { |r| r.recno \> last\_recno\_written\_out and r.recno \< last\_recno\_written\_out + 40000 }.sort(:field4)
........ here is where you put the code to write these 40,000 recs to a csv output file .............
last\_recno\_written\_out = recs.last.recno
rec\_count = rec\_count - 40000
end
KirbyBase will even use FasterCSV for it's csv stuff if you have it installed. 馃檪
Anyway, hope this helps. If I have totally misunderstood the request, feel free to ignore!
Jamey Cribbs
> **路路路**
>
> > 2006/11/30, Drew Olson \:
---
### Author:  [@James\_Edward\_Gray\_II](https://rubytalk.org/u/James_Edward_Gray_II)
#### Post date: [29 November 2006 23:31 UTC](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312/31 "2006-11-29T23:31:23Z")
> I am perplexed by CSV.open.
Me too. 馃槈
> I wonder why it was not patterned more closely on IO? Any thoughts?
The author does not believe CSV objects should be IO like. If you search the archives for my messages about including FasterCSV in the standard library you will run into our debates on this subject.
FasterCSV's open() behaves the way you expect.
James Edward Gray II
> **路路路**
>
> On Nov 29, 2006, at 5:25 PM, Edwin Fine wrote:
---
### Author:  [@Paul\_Lutus](https://rubytalk.org/u/Paul_Lutus)
#### Post date: [30 November 2006 07:25 UTC](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312/32 "2006-11-30T07:25:05Z")
M. Edward (Ed) Borasky wrote:
> Paul Lutus wrote:
>
> > James Edward Gray II wrote:
> >
>
> [snip]
>
> Hmmmm ... seems like a bunch of folks are going to a bunch of trouble
> and to baby a tool that can only handle a CSV file with less than 65536
> rows. Excel is broken ... Excel sucks at math ... Excel can't handle
> lots of rows and lots of columns ... Excel costs money. Excel was a
> great wonder in 1986 -- 20 years later it's lame beyond words.
I concur in all respects, based on direct experience.
> I'm not sure about the column count limitations, but I know almost any
> database will deal with a CSV file with more than 65536 rows. There are
> some awfully good free databases. Did I mention how bad Excel is at
> math?
It certainly bears repeating. I've found a number of math errors in Excel
over the years.
> Sometimes you \*don't\* get what you pay for. 馃檪
IMHO Microsoft represents a negative correlation across the board.
[http://arachnoid.com/boycott/index.html](http://arachnoid.com/boycott/index.html)
> **路路路**
>
> --
> Paul Lutus
> [http://www.arachnoid.com](http://www.arachnoid.com)
---
### Author:  [@Paul\_Lutus](https://rubytalk.org/u/Paul_Lutus)
#### Post date: [30 November 2006 18:36 UTC](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312/33 "2006-11-30T18:36:51Z")
Edwin Fine wrote:
> I am perplexed by CSV.open. In IO and File, open returns something that
> quacks like an IO object. You can then call gets, puts, read, write and
> so on. The CSV open seems to return an array (or gives you a row at a
> time).
/ ...
> I wonder why it was not patterned more closely on IO? Any thoughts?
This is an experience with which I am becoming familiar. Someone requests a
solution to a problem. Someone else offers the option of a library to solve
the problem. Then the original problem fades into the background, replaced
by discussion of the library's problems.
This same pattern has repeated itself about four times in the past
fortnight, in just this one newsgroup.
I can be relied on to suggest a terse code solution. Then someone else can
be relied on to point out, correctly, that a terse code solution may miss
edge cases and, if exposed to enough data, will surely fail. Absolutely
correct.
So, just for variety, I will \_not\_ say "Have you considered writing your own
code?" just because that's what people expect me to say. I won't do this
because I now realize "code" is a trigger word, just like saying "abortion"
among fundamentalists -- that is something you just don't want to do.
So I will say "Have you considered writing your own library?" It amounts to
the same thing, since libraries are written using code, and all code is
written by mortals, but this way of saying it avoids the trigger word
"code".
Your own code ... er, excuse me, your own library ... will meet your
requirements exactly, it won't cover cases that are not relevant to the
problem at hand, it will be much faster overall than existing solutions,
and you will learn things about Ruby that you would not if you used someone
else's library.
In this specific case, as has been pointed out to me, a CSV field can
contain linefeeds, which means -- if your data exploits this trait -- you
need to parse the entire database using a state machine that knows about
this possibility.
On the other hand, if your data does not exploit this CSV trait (few
real-world CSV databases embed linefeeds), you can scan the data much more
quickly using a simpler solution, but a solution that will certainly fail
if the above assumption turns out to be false. Code like this:
> **路路路**
>
> ------------------------------------------
>
> #!/usr/bin/ruby -w
>
> max\_output\_lines = 40000
>
> input\_file = "test.txt"
>
> output\_base = "output"
>
> n = 0
>
> ifile = File.open(input\_file,"r")
>
> header = ifile.gets
>
> until(ifile.eof?)
> ofn = output\_base + sprintf("%03d",n) + ".txt"
> ofile = File.open(ofn,"w")
> ofile.write(header)
> line = 2
> until(ifile.eof? || line \> max\_output\_lines)
> ofile.write(ifile.gets)
> line += 1
> end
> ofile.close
> n += 1
> end
>
> ifile.close
>
> ------------------------------------------
>
> Note that I meet your requirement to place the original header line at the
> top of each database section.
>
> If you will call this a "library", it will pass muster with those who prefer
> the word "library" to the word "code". Outside the box, it's all the same.
> Inside the box, not at all.
>
> --
> Paul Lutus
> [http://www.arachnoid.com](http://www.arachnoid.com)
---
### Author:  [@James\_Cribbs](https://rubytalk.org/u/James_Cribbs)
#### Post date: [1 December 2006 14:37 UTC](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312/34 "2006-12-01T14:37:18Z")
Jamey Cribbs wrote:
> Thomas Mueller wrote:
>
> > > I'll give FasterCSV a try when I get home from work and out from behind
> > > this proxy. Here's another question: in some cases I need to sort the
> > > file before splitting it (in this case sorting by the 4th cell in each
> > > row). However, the current file I'm trying to sort and split is around
> > > 76 MB and ruby fails when trying to store the CSV as an array. The code
> > > and output are below. How else can I go about this?
>
> I'm coming to this party really late, so I hope I don't come across as shamelessly plugging KirbyBase, but, you might want to try it for this.
>
> If you are simply trying to take a large csv file, sort it by one of its fields, and split it up into smaller files that each contain 40,000 records, I think it might work.
>
> Here's some code (not tested, could be incorrect) off the top of my head:
>
> require 'kirbybase'
>
> db = KirbyBase.new
>
> tbl = db.create\_table(:foo, :field1, :String, :field2, :Integer, :field3, :String............................
>
> tbl.import\_csv(name\_of\_csv\_file)
>
> rec\_count = tbl.total\_recs
> last\_recno\_written\_out = 0
>
> while rec\_count \> 0
> recs = tbl.select { |r| r.recno \> last\_recno\_written\_out and r.recno \< last\_recno\_written\_out + 40000 }.sort(:field4)
>
> ........ here is where you put the code to write these 40,000 recs to a csv output file .............
>
> last\_recno\_written\_out = recs.last.recno
>
> rec\_count = rec\_count - 40000
> end
I realized this morning that the solution I posted last night won't work because you need the whole dataset sorted \*before\* you start splitting it up into 40,000 record files. Oops!
Anyway, in an attempt to recover gracefully from my mistake and also to give me the opportunity to shamelessly plug another one of my libraries, I present the following proposed solution that is totally untested and probably full of holes:
require 'mongoose'
db = Mongoose::Database.new
db.create\_table(:foo) do |tbl|
tbl.add\_column(:field1, :string)
tbl.add\_column(:field2, :string)
tbl.add\_column(:field3, :integer)
tbl.add\_indexed\_column(:field4, :string)
.
end
Foo.import(csv\_filename)
total\_recs\_written = 0
while total\_recs\_written \< Foo.last\_id\_used
recs = Foo.find(:order =\> :field4, :offset =\> total\_recs\_written, :limit =\> 40000)
........ here is where you put the code to write these 40,000 recs to a csv output file .............
total\_recs\_written += recs.size
end
Jamey
Confidentiality Notice: This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and/or privileged information. If you are not the intended recipient(s), you are hereby notified that any dissemination, unauthorized review, use, disclosure or distribution of this email and any materials contained in any attachments is prohibited. If you receive this message in error, or are not the intended recipient(s), please immediately notify the sender by email and destroy all copies of the original message, including attachments.
> **路路路**
>
> > > 2006/11/30, Drew Olson \:
---
### Author:  [@Mike\_Harris](https://rubytalk.org/u/Mike_Harris)
#### Post date: [1 December 2006 23:15 UTC](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312/35 "2006-12-01T23:15:43Z")
Louis J Scoras wrote:
> > \> Here's another question: in some cases I need to sort the
> > \> file before splitting it (in this case sorting by the 4th cell in each
> > \> row). However, the current file I'm trying to sort and split is around
> > \> 76 MB and ruby fails when trying to store the CSV as an array. The
> > \> code
> > \> and output are below. How else can I go about this?
> >
> > Hmm, that's a good question.
> >
> > Perhaps you can collect just the key values in an Array and then use
> > those to reorder the lines bit by bit. That's not going to be fast
> > with any library helping you, but I don't have a better idea.
> >
> > James Edward Gray II
>
> Indeed. That problem is difficult in general because you need to have
> the whole set of elements in memory before you can begin sorting them.
> As James pointed out, you might be able to use some sort of
> memoization technique to track only the bits relevent to sorting. The
> problem is you'll also need some way to get back to the original
> record.
>
> Depending on how you ending up parsing the records, you might be able
> to store the file position of the start of the record and the record
> length.
>
> Records -\> [sort\_key, file.pos, record.length]
>
> Then sort those arrays if you can fit them all in memory. Finally,
> you can use the offsets for random access to grab the records and
> stick them into the new files as you've been doing.
>
> Basically, you're looking at a complicated swartzian transformation.
> If it will work depends on how big your records are. If they are
> fairly large, you might be able to pull if off; however, if they're
> small and the problem is only that there are too many records, you'll
> still have a problem.
>
> In that case, you might want to just shove them in an RDBMS and let it
> sort it for you.
Let's say you want to sort by the foo column
Read in all the foo values and sort them
Get every 40,000th value from the list.
Now, upon reading any row, you can determine what page it should go on.
Read the file, get the rows for the first N pages, ignoring the rest of the rows, where N is a number that won't run you out of memory. Create the files for those rows
Remove references to the rows you read in.
Repeat with the next N pages until finished.
> **路路路**
>
> > On 11/29/06, James Edward Gray II \ wrote:
> >
> > > On Nov 29, 2006, at 12:56 PM, Drew Olson wrote:
---
### Author:  [@James\_Edward\_Gray\_II](https://rubytalk.org/u/James_Edward_Gray_II)
#### Post date: [30 November 2006 19:49 UTC](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312/36 "2006-11-30T19:49:02Z")
> Your own code ... er, excuse me, your own library ... will meet your
> requirements exactly, it won't cover cases that are not relevant to the
> problem at hand, it will be much faster overall than existing solutions,
> and you will learn things about Ruby that you would not if you used someone
> else's library.
Now you're guilty of a new sin: encouraging people to reinvent the wheel. You just can't win, can you? 馃槈
Different problems require different levels of paranoia. Sometimes a little code will get you over the hump, but you may be making some trade-offs when you don't use a robust library. Sometimes those are even good trade-offs, like sacrificing edge case handling to gain some speed. Sometimes it's even part of the goal to avoid the library, like when I built FasterCSV to address some needs CSV wasn't meeting. As soon as things start getting serious though, \*I\* usually feel safer reaching for the library.
The people reading this list have seen us debate the issue now and be able to make well informed decisions about what they think is best.
> On the other hand, if your data does not exploit this CSV trait (few
> real-world CSV databases embed linefeeds)...
Really? How do they handle data with newlines in it?
Which "CSV databases" are you referring to here?
James Edward Gray II
> **路路路**
>
> On Nov 30, 2006, at 12:36 PM, Paul Lutus wrote:
---
### Author:  [@Edwin\_Fine](https://rubytalk.org/u/Edwin_Fine)
#### Post date: [1 December 2006 08:10 UTC](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312/37 "2006-12-01T08:10:28Z")
Paul Lutus wrote:
> Edwin Fine wrote:
>
> > I am perplexed by CSV.open. In IO and File, open returns something that
> > quacks like an IO object. You can then call gets, puts, read, write and
> > so on. The CSV open seems to return an array (or gives you a row at a
> > time).
>
> / ...
>
> > I wonder why it was not patterned more closely on IO? Any thoughts?
>
> This is an experience with which I am becoming familiar. Someone
> requests a
> solution to a problem. Someone else offers the option of a library to
> solve
> the problem. Then the original problem fades into the background,
> replaced
> by discussion of the library's problems.
>
> This same pattern has repeated itself about four times in the past
> fortnight, in just this one newsgroup.
> /snip/
Ummm, I am not sure exactly how to interpret the above post, but I see
my name quoted there, so I feel compelled to clarify what I was thinking
in making my original post. I had just written a small Ruby program that
would satisfy the OP's stated problem, but using IO/File. While I was
doing this, more posts appeared, which alerted me to the possibility
that I would have to cater for newlines in the input., "Oh well", I
thought, "I'll just replace every use of "IO" with "CSV", and that will
be that. BZZZ! Wrong! Thank you for playing. I couldn't drop in CSV
instead of IO? WTF???
This is where my perplexity came in. Matz himself has regularly and
clearly stated that he designed Ruby along the Principle Of Least
Surprise (or LOLA, Law of Least Astonishment). Well, I was grievously
surprised and astonished when CSV#open behaved differently to every open
I have used in any language. All the other opens that I know of return
the concept of a handle/object, or some \*thing\* that can then be
beseeched to bring forth the contents of the actual I/O "device", one
element at a time, or all at once. The CSV#open skips this step and goes
straight from open to bringing forth entities, and thereby breaks
possible compatibility with IO/File. IMHO, this is inelegant design.
I have written many, many libraries (not in Ruby) and know how important
it is to present to your users a consistent, clean, orthogonal,
robust,reliable set of interfaces and implementations. That's why it is
inadvisable to release a very early version of a library to a large
audience of end users until it has proven itself in battle, as it were.
Otherwise, you face the prospect of having to change the interface to be
less surprising (\*and\* keep a backward-compatible, deprecated one) and
re-releasing it to possibly hundreds of users.
The bottom line is, although I am perfectly capable of doing so, I don't
WANT to reinvent the wheel. I really like reusing good, dependable,
predictable code. I haven't had time to look at FasterCSV yet, but I
predict that I will like what I see, because to my mind, from the works
of his I have seen, the author does his best to embody the "Tao" of Ruby
(LOLA). (Although you can never accurately describe the Tao of anything,
I know...)
Well, that's my 2c worth 馃檪
> **路路路**
>
> --
> Posted via [http://www.ruby-forum.com/\](http://www.ruby-forum.com/%5C).
---
### Author:  [@Drew\_Olson](https://rubytalk.org/u/Drew_Olson)
#### Post date: [2 December 2006 01:06 UTC](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312/38 "2006-12-02T01:06:21Z")
Thanks everyone for ALL the replys. Lots of interesting things to think
about. I'll take a look at using a database approach for this and I'm
looking at FasterCSV now. Also, some very good insight related to
building code from scratch and using libraries.
Another great example of the ruby community at work IMO.
> **路路路**
>
> --
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).
---
### Author:  [@Paul\_Lutus](https://rubytalk.org/u/Paul_Lutus)
#### Post date: [30 November 2006 20:45 UTC](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312/39 "2006-11-30T20:45:10Z")
James Edward Gray II wrote:
> > Your own code ... er, excuse me, your own library ... will meet your
> > requirements exactly, it won't cover cases that are not relevant to
> > the
> > problem at hand, it will be much faster overall than existing
> > solutions,
> > and you will learn things about Ruby that you would not if you used
> > someone
> > else's library.
>
> Now you're guilty of a new sin: encouraging people to reinvent the
> wheel. You just can't win, can you? 馃槈
If the OP has a problem not easily solved with a library, then he isn't
reinventing the wheel. And I don't care about winning.
> Different problems require different levels of paranoia.
Yes, absolutely. The larger the job and the larger the data set, the more
likely one will encounter border conditions, and the more appropriate to
use a state machine that understands the full specification. All at the
cost of speed.
> Sometimes a
> little code will get you over the hump, but you may be making some
> trade-offs when you don't use a robust library.
Yes, but a robust library is not appropriate if it cannot solve the problem,
or if the learning curve is so steep that it would be easier to write one's
own scanner.
Also, there is a hidden assumption in your position -- that libraries, ipso
facto, represent robust methods.
> Sometimes those are
> even good trade-offs, like sacrificing edge case handling to gain
> some speed. Sometimes it's even part of the goal to avoid the
> library, like when I built FasterCSV to address some needs CSV wasn't
> meeting.
That borders on the heretical. 馃檪
> As soon as things start getting serious though, \*I\* usually
> feel safer reaching for the library.
I've noticed that. I want to emphasize once again that my style is a
personal preference, not an appeal to authority or untestable precepts.
> The people reading this list have seen us debate the issue now and be
> able to make well informed decisions about what they think is best.
I think 90% of the readers of this newsgroup won't pay any attention to
either of our opinions on this topic. They will realize that inside every
library is code written by a mortal human, therefore this sort of debate is
primarily tilting at windmills or describing angel occupancy requirements
for heads of pins.
For the newbies, however, it might matter. They might think library contents
differ from ordinary code. And that is true only if the writers of
libraries differ from ordinary coders. Ultimately, they don't, as Microsoft
keeps finding out.
> > On the other hand, if your data does not exploit this CSV trait (few
> > real-world CSV databases embed linefeeds)...
>
> Really? How do they handle data with newlines in it?
Linefeeds are escaped as though in a normal quoted string. This is how I
have always dealt with embedded linefeeds, which is why I was ignorant of
the specification's language on this (an explanation, not an excuse).
> Which "CSV databases" are you referring to here?
MySQL, the database I am most familiar with, uses this method for import or
export of comma- or tab-separated plain-text data. Within MySQL's own
database protocol, linefeeds really are linefeeds, but an imported or
exported plain-text table has them escaped within fields.
I create a lot of plain-text databases, and I am constantly presenting them
to MySQL for parsing (or getting plain-text back from MySQL), and this only
confirmed my mistaken impression that linefeeds are always escaped in
fields of this class of database.
It's obvious why the specification reads as it does, and I should have known
about this long ago. It reads as it does because it just isn't that
difficult to parse a quoted field, and it is no big deal to allow
absolutely anything in the field. It just takes longer if all the database
handling (not just record parsing) must use the same state machine that
field parsing must use.
\\
It's very simple, really. Once you allow the record separator inside a
field, you give up any chance to parse records quickly.
When a group of people sit down to create a specification, the highest
priority is ... utility, common sense? ... no, it's immunity to criticism.
The easies way to avoid criticism is to allow absolutely anything, even if
this hurts performance in real-world embodiments that obey the
specification.
Someone might say, "Okay, but can you drop an entire, internally consistent
CSV database into the boundaries of a single field of another CSV database,
without any harm or lost data?" Using the present specification, the
committee can say "yes, absolutely."
But parsing will necessarily be slow, character by character, the entire
database scan must use an intelligent parser (no splitting records on
linefeeds as I have been doing), and the state machine needs a few extra
states.
I cannot tell you how many times I have foolishly said, "surely the
specification doesn't allow that!", and I cannot remember actually ever
being right after taking such a position. When I make assumptions about
committees, I am always wrong.
\\
> **路路路**
>
> > On Nov 30, 2006, at 12:36 PM, Paul Lutus wrote:
>
> --
> Paul Lutus
> [http://www.arachnoid.com](http://www.arachnoid.com)
---
### Author:  [@Paul\_Lutus](https://rubytalk.org/u/Paul_Lutus)
#### Post date: [1 December 2006 09:30 UTC](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312/40 "2006-12-01T09:30:05Z")
Edwin Fine wrote:
/ ...
> The bottom line is, although I am perfectly capable of doing so, I don't
> WANT to reinvent the wheel. I really like reusing good, dependable,
> predictable code. I haven't had time to look at FasterCSV yet, but I
> predict that I will like what I see, because to my mind, from the works
> of his I have seen, the author does his best to embody the "Tao" of Ruby
> (LOLA). (Although you can never accurately describe the Tao of anything,
> I know...)
Nice, informative post. There are a lot of issues here, primarily the fact
that the database under discussion is too big to hold in memory, and it is
also too big to fit into Excel in one chunk, which appears to be its
destination.
Most people have begin to drift toward suggesting a database approach,
rather than anything that involves direct manipulation of the database in
Ruby. Because of the size of the database and because sorting the records
is one goal, I have to agree.
> **路路路**
>
> --
> Paul Lutus
> [http://www.arachnoid.com](http://www.arachnoid.com)
[Previous page](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312.md?page=1)
[Next page](https://rubytalk.org/t/splitting-a-csv-file-into-40-000-line-chunks/33312.md?page=3)