Strategy for editing comma separated value (CSV) files

When I work on data analysis projects I often store data in comma or tab-delimited (CSV, TSV) data files. While data often belongs in a dedicated database management system. For many of my applications, this would be overdoing things.

I can edit CSV and TSV files in Excel (or presumably another Spreadsheet program). This has benefits:

• spreadsheets make it easy to enter data

There are also several problems:

• Working with CSV and TSV files leads to a wide range of warning messages about various features being lost and how only the active sheet will be saved and so forth. Thus, it's annoying if you just want to open the file and make a little change.
• It does many "supposedly intelligent" conversions. For example, if you enter 12/3, it will think that you want to enter a date. UPDATE: I should have mentioned that the date example is just one of many examples; most problems seem to be related to inappropriate conversion. In particular, text fields that look like numbers or dates cause problems.

Alternatively, I could work directly with the text file in a standard text editor. This ensures that what I enter is what is recorded. However it is a very awkward way to enter data (columns don't line up; it's difficult to enter data simply into multiple cells; etc.).

Question

• What is a good strategy for working with CSV or TSV data files? i.e., what strategy makes it easy to enter and manipulate the data while also ensuring that what you enter is actually interpreted correctly?
• What kind of operations do you actually do on the files? In my mind, that dramatically affects the range of acceptable options. Also, it seems to me that you could edit TSV data in a word processor and line up tabs to preserve columns -- as long as your word processor can be set to not do "smart" conversions and can save as plaintext, which many can. – Wayne Dec 8 '10 at 19:30
• @Wayne Good point. Because I've grown up with spreadsheet programs, there are many shortcuts that I'm familiar with (functions, copy and paste, adding an extra column, and much more). Note, I'm not talking here about data analysis, but just creating a simple tabular data file (e.g., some meta information needed to process my R code). While I could do all these basic table manipulations in R, it's not quite as intuitive for me. Probably over time, opening a csv in R, making a few little changes and saving it again will become my preferred option. – Jeromy Anglim Dec 9 '10 at 2:01

1. If you are comfortable with R, you can create your basic data.frame and then use the fix() function on it to input data. Along the same line as #5, once you set up the data.frame you can use a series of readLines(n=1) (or whatever) to get your data in, validate it, and the provide the opportunity to add the next row. Then leave the fixing to fix(). See an implemented example below using scan().

2. Another option in excel would be messy, but you could type in 12/9, then have another column evaluate =IFERROR(MONTH(DateEntryCell)/DAY(DataEntryCell),DataEntryCell). But then you'll have to maintain the excel sheet AND the csv sheet and all of the complaining as you write the csv will persist.

3. Alternatively, so long as your fields are relatively short and have a consistent length a regular text editor should serve you well with TSV. You can always load it up in excel when you are done and make sure the number of columns for each row is what you expect it to be.
4. Emacs is available on a number of platforms and probably has something just for this, e.g. http://www.emacswiki.org/emacs/CsvMode.
5. If you are a hearty soul, programming something quick up in a programming language to do the data entry is trivial, the data editing will be a lot harder.
6. A quick google search shows software with just this purpose, but no free software seemed to be any good.
7. It sounds insane, but someone on superuser suggested editing tables in access and then exporting them as CSV... that is just crazy enough to work.
8. It doesn't stop excel from complaining as you save as .csv, but you can type a single apostrophe before your data entry field and that makes it leave it alone in terms of auto-formatting. Nicely, this (in Office 2007 at least) doesn't leave apostrophes in the csv file.

Update: I've been poking around a lot on this problem because it is an issue I also have. So far the best/easiest solution for data-entry I've seen so far is KillinkCSV. It isn't "free" software, it is shareware with a 30 day trial duration and a reasonable price (~\$27). I'm not sure how much I trust it for editing existing CSVs though - I handed it an insanely large (and presumably well formatted) CSV and it failed to read all of the rows. However it seemed to work well for one that was reasonably large (20 MB) and the problem with the larger file may be user error on my part.

R Example:

#This function takes a what argument like in scan,
#a list with the types to be used, see usage example
#at the end of this code block
#dataEntry will keep reading in values until
#the values it reads in matches what is in
#"terminateon".
#limitations: Many
dataEntry <- function(what,terminateon)
{
CONTINUE <- TRUE #Make sure we start the loop
data <- NULL #Create empty data so that the data.frame can define itself
ti <- NULL
while(CONTINUE)
{
ti <- NULL
ti <- tryCatch(
{as.data.frame(scan(what=what, nlines=1, multi.line=FALSE, comment.char="",quiet=TRUE))},
error=function (e) {print("Error in data entry! Line not stored.")
return(NULL)},
warning=function(w) {print("Error in data entry! Line not stored.")
return(NULL)},
finally={ti <- NULL}
) #Try getting the data according to the parameters in 'what' one row at a time.
if (!is.null(ti))
{
if ((ncol(ti)==length(what)) & (nrow(ti)==1)) {
data <- rbind(data,ti) #If there wasn't an error, add ti to the previous value
} else {
print("Too many or not enough values on previous entry.")
print("Tail of current data:")
print(tail(data))
}
}
if (!is.null(ti) & all(ti == terminateon))
{
CONTINUE <- FALSE
data <- data[-c(nrow(data)),]
} #if we've recieved the final value we won't continue and the last row is invalid so we remove it
}
return(data)
}

dataEntry(list(x=integer(), y=numeric(), z=character()),terminateon=c(999,999,"Z"))

• Yep, and it turns out I already have csv-mode.el installed thanks to the emacs-goodies-el package in Debian/Ubuntu -- neat. – Dirk Eddelbuettel Dec 9 '10 at 3:50
• +1, For point #1 all stat software I have ever worked (besides R, Stata, SPSS, and SAS) with provides this type of functionality. So your advice generalizes to whatever software someone wants to work with. – Andy W Dec 9 '10 at 13:45

Update: [Having been going through a large backlog of email from R-Help] I am reminded of the thread on "The behaviour of read.csv()". In this, Duncan Murdoch mentions that he prefers to use Data Interchange Format (DIF) files instead of csv for some of the reason Jeromy mentions. I just tried this and Gnumeric gets it wrong (loading 12/3 as a date), but OpenOffice.org reads this correctly and preserves the 12/3 information intact. (Anyone care to check this in MS Excel?)

DIF files are plain text and can be read by spreadsheets and R (as long as you use a recent R revision (SVN revision >= r53778)) will read the data in in the correct format.

Original: I would try to avoid using a spreadsheet full stop for data editing / manipulation whenever possible. It is incredibly difficult, if not impossible, to document any changes you make to an existing data set so that pretty much rules it out from a reproducible research point of view. At most, I use a spreadsheet to quickly view existing data.

For data processing, I tend to write an R script that will take the raw csv file and apply all the necessary processing steps required. I heavily comment that script to explain exactly what I am doing at each stage and why. My data analysis script would then call the data processing script which loads and processes the data.

For data entry, is it more hassle to enter the data in a text editor or in a spreadsheet? I suspect the problems you mention for the latter do not outweigh those of trying to enter CSV data into a text editor.

You could try a better spreadsheet; OpenOffice.org refuses to stop formatting 12/3 as a date (or it converts it to the numeric representation) even if one formats the column as "numeric" first. Gnumeric on the other hand will leave 12/3 as it is if you format the column as "numeric" first.

You can force OpenOffice.org to not reformat 12/3 as a date by prepending a ' to the entries, i.e. '12/3 will get displayed as 12/3 in the spreadsheet and saved out as text. This is probably quite safe to use.

Not sure why you would want 12/3 stored numerically as 12/3 in the text file - how should something like R read this?

Your comment on warnings about losing features or only saving the active sheet aren't really problems are they? (If they are, then I want your problems in my life ;-)

• With regards to reproducible research, if the file format is CSV, then the CSV file could easily be put under version control with commits when changes are made. Would this suffice for reproducible research? With regards to data entry, I often do or get others to do raw data entry in fix width format in order to maximise efficiency; however I prefer to store data in CSV or TSV (I find it more transparent); with regards to formatting a column as numeric. this information would presumably be lost on saving. – Jeromy Anglim Dec 8 '10 at 8:45
• with regards 12/3, I had an example of a multiple choice test where this represented the 12 divided by 3. However, I use it more as an illustrative example. When I have a large CSV file, I don't want even one inappropriate conversion. With regards to warnings, If I'm opening CSV files 10 times a day the warnings get tedious. They also make it less clear whether I have actually saved the CSV file or not. Sorry for the rant. – Jeromy Anglim Dec 8 '10 at 8:48
• @Jeromy yes, it would, if you were strict about making a single change (or single data processing step) that you immediately save and commit. I mean't to include a note about doing the processing in R (as it is my preferred language) using a script. That is how I work with data colleagues send me; I might have a quick look in a spreadsheet, then read the csv into R and write a script that includes all the data processing steps I need to apply. I then comment that script so I have a record of what I did and why I did it and I haven't changed the original data file at all. – Gavin Simpson Dec 8 '10 at 8:50
• @Jeromy: fair point on the warnings. I've long since filtered them out as noise so they don't bother me. I just tested Gnumeric and OpenOffice.org and they convert a csv with 12/3 to dates automagically - that is rubbish! So I see what you mean. The only way to stop that would be to store those data as text and force that data type on load/import. – Gavin Simpson Dec 8 '10 at 8:58
• @Jeromy: re: losing numeric formatting - yes, it will, unless you specify the column type as "numeric" (Gnumeric) or "text" in (OpenOffice.org) upon import. Better might be to store it as text (see my edited answer) to avoid the conversion - you'll still need to specify the data type upon import... – Gavin Simpson Dec 8 '10 at 9:05

I suggest you look at google refine (http://code.google.com/p/google-refine/). I think is a very good tool for editing CSV files

• Could you please explain how this responds to the request for a strategy in the question? – whuber May 22 '17 at 18:06

After I asked this question, I started having a look at CSVed.

From the website:

CSVed is an easy and powerful CSV file editor, you can manipulate any CSV file, separated with any separator.

I'm not sure if anyone has experience with it.

• I tried installing it. After a quick try it seemed like rubbish for data entry; maybe if I gave it a longer try I'd be able to figure out how to use it effectively, but I suspect not. – russellpierce Dec 8 '10 at 16:06
• Could you please explain how this responds to the request for a strategy in the question? – whuber May 22 '17 at 18:06

Excel is not very CSV friendly. For example, if you were to enter "1,300" into Excel, and save that as a comma separated value, it would let you! This can be a big problem (I encounter it on a regular basis when receiving files from others).

I personally use OpenOffice.org Calc, I also use many of the solutions listed above, however many of these don't have the functionality and the ease of use that are required for regular editing. OOO Calc is much more intelligent than Excel, although being a spreadsheet program, you will still have to enter "=12/3" instead of "12/3" otherwise you will be entering a value, rather than a calculation.

Give it a whirl, you won't be disappointed.

I like Gnumeric because it does not try to be so much idiot-resistant as others (it doesn't shout about lost functionality) and works with large data... yet I think it is Linux-only.

• following this question, I did a hunt: there is a Windows version projects.gnome.org/gnumeric/downloads.shtml – Jeromy Anglim Dec 8 '10 at 9:11
• it does warn about only saving the current sheet though (which was one of Jeromy's bug-bears), and it has the annoying feature of converting properly saved 12/3 numerics to dates unless you manually specify they are numeric on import (Data > Get External Data > Import Text File...) not load. – Gavin Simpson Dec 8 '10 at 9:31
• Could you please explain how this responds to the request for a strategy in the question? – whuber May 22 '17 at 18:06

Just use Ron's Editor. Its just like Excel without the 'help'.

From the site:

Ron's Editor is a powerful tabular text, or CSV, editor. It can open any format of separated text, including the standard comma and tab separated files (CSV and TSV), and allows total control over their content and structure.

Not only can tabular text files be edited, but they can also be easily filtered and summarized in as many extra views as required, adding powerful analysis functionality.

• License: Free for personal use/evaluation
• Runs on: Windows 32/64-bit 2000/XP/2003/Vista/
• Could you please explain how this responds to the request for a strategy in the question? – whuber May 22 '17 at 18:05

I personally like to use the idea of "relational database" to manage CSV files. CSV files are good for exchange data, but contains no business logic. My experience of working with CSV is "there are many iterations with business to refine the analysis". Working with only plain text files (CSV) will pose many challenges. For example, CSV file will not show "what make data unique", i.e., what is the "primary key to each row". This will cause big problems later on, when we have other data source to join.

SQLite is a good tool to make CSV into relational database, and similar to CSV, it is easy to exchange, and no server set up are needed. More importantly, it supported very well in R and other statistical software.

My strategy is always maintain a "cleaned data" in relational database. And keep it clear on the primary key of each table.

Here is an example of what may happen in real word (suppose we are selling books):

• Day 1, I received a CSV file contain all customer information.
• Day 2, I received another CSV file contain all product (book) information.For some reason, business said no ISBN available and the combination of book name and author name is the primary key.
• Day 3, Business found book edition needs to be accounted for, they send another CSV to "overwrite" day2's CSV.
• Day 4, Business found customer information can be updated (such as address change), they send an updated version of customer information.

Now, you can see the advantage of clean data and keep them in relational database. With the say customer ID as primary key, and book name, author and edition as primary key. It is very easy to make data updates and incorporate changes as needed. Also the primary key also gives "constraints" and "sanity check" for new coming data.

If you use Excel's "Import Data" feature, it will give you option of selecting the data type for each column. You can select all the columns and use the "Text" data type.

• Could you please explain how this responds to the request for a strategy in the question? – whuber May 22 '17 at 18:06