splitting csv files

I'm sure I've ranted about this before. I'll rant about this again. I just hate seeing examples from people in C# where they say that you just split a CSV file by using mystring.Split(',') and you will get an array where each item is the value for each field. One would assume, since they mention C#, that at some point in their lives they have worked with excel style CSV files that have data like

"Bob","Hello, Dear","""Dude, where's your car"""

The string split method obviously will not correctly handle this case at all. Here is a page I found with a nice little function that works for me in most cases.

http://www.tedspence.com/index.php?entry=entry070604-124237

Hopefully this will work for you, too.

Finding potential problems in CSV files

I have to periodically work with CSV files from a variety of sources and the problems with them are pretty well known. Here is a little python script I use that helps me find values that contain double quotes, which are often not properly escaped in the files I receive

flines = open(fn,"r").readlines()
currline = flines[1]

for l in flines:
    vals = l.split(",")
    for v in vals:
        if '"' in v.strip()[1:-1]:
            print v

Simple utility for getting data from CSV files

Most programmers at some point spend a lot of time hacking apart CSV files. I recently got to yank apart yet another csv and while Excel is handy for these sorts of tasks, it can't do everything quickly, so I hacked up a little utility using php5 and sqlite to help query data and get results fast.

Here is csvsql

You just upload the file, first row is headers and you can type in the filter part of a where clause to narrow the data. click a button and put out a CSV representation of the data that should be excel friendly.