Split one long column to multiple CSVs in Excel?

January 21, 2009 3:08 PM

Excel: I have a long list of 2000 items addresses in a csv. That single column is all there is. Need to chop that list into 10 separate csv files of 20 items each. Is there a way to automate this?

Might one send a range of rows in steps to separate sheets within the same book and name those sheets individually as csvs? Might there also be a way to automate the saving (though, with only ten to do, I am not as worried about that). I could do this all with copy/paste/save, but I would like to learn a bit more about how things *could* be done.

posted by skypieces to computers & internet (7 comments total)

A csv file is a text file, so you could just split it. On a unix system, you'd use:

split -l 20 addresses.csv

there are split utilities for windows as well (e.g. this), including cygwin's. Some text editors even come with split functionality I think.
posted by idb at 3:31 PM on January 21

Well, your math doesn't exactly work, so I'm assuming it's 10 lists of 200. Yeah, copy/paste would be easiest... but if you have any programming experience at all, I'd use a macro. Excel macros (using VBA) aren't really that hard to write: you could throw one together that grabs different ranges of the worksheet and exports them.

Some sample code that does something similar:
http://www.ozgrid.com/forum/showthread.php?t=58374

Shouldn't be too hard at all to adapt that to do it 10 times using different ranges of the document.

If you don't have any programming experience, I'd go with idb's suggestion: just spit it out into a single file, and then use some sort of a split utility to split it up.
posted by captainawesome at 3:33 PM on January 21

Not so much bad math as a typo. And, it is the inverse: I need 100 lists of 20 each. The splitter that idb referenced appears to do the old filesplit (e.g. for sending to multiple floppies, to be rejoined later), not actually splitting the contents of a single text or csv.

As for macros, I am a complete ignoramus. I suppose I could use this as a chance to learn, but not sure how that ratios to the productivity aspect of getting this to done.

Will continue to look for the "text editors... with split functionality" that idb mentioned. Any idea if those will split to separate files? And maybe even name them (e.g. with trailing numbers to differentiate)?

Thank you for suggestions so far.
posted by skypieces at 3:53 PM on January 21

If you open the csv file in excel, you can press alt-F11 to open up the VBA code window, then doubleclick the "This Workbook" option in the top left pane. Then, paste the following in the main pane:

Sub exportfiles()
    'the next line can be updated to your preferred output path
    mypath = "C:\MYDIRECTORY\"
    
    myrow = 0
    For x = 1 To 100
        Open mypath & x & ".csv" For Output As #1
            For y = 1 To 20
                
                myrow = myrow + 1
                Print #1, Sheets(1).Cells(myrow, 1)
                
            Next y
        Close #1
    Next x
    
    MsgBox "Done"
End Sub

Replace the MYDIRECTORY part to specify the output directory. then, with you cursor somewhere in the subroutine, you can press F5 and it will run through exactly 2000 rows to create exactly 100 files with the contents of exactly 20 rows, each on a separate row.

There are better ways to write the code, specifically, it would make sense to run through the rows checking each one to see if it is empty, and if so stopping.
posted by i love cheese at 3:54 PM on January 21

Bam. Done. I thank you and my family thanks you!
posted by skypieces at 4:13 PM on January 21

The splitter that idb referenced does exactly what you want. Why mess around writing VB macros??
posted by mr. strange at 3:09 AM on January 22

Because most people who are running Excel aren't doing so under Unix?
posted by zixyer at 5:00 AM on January 22