Strategy for editing comma separated value (CSV) files

Question

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?

Answer 1

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"))

Answer 2

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 ;-)

Answer 3

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

Answer 4

I would avoid working with the CSV and TSV files all together. Instead learn to use SQL and operate only on a datamart or database (DB) copy of your data or you can use SAS or R with a passthru connection to your database. That way you can make bulk updates to your data instead of doing the dreaded find and replace in Excel (or whatever spreadsheet program you are using) or copying and pasting which can be prone to errors. The advantage of using a DB system too is that you can enable logging and quickly rollback changes you have made if they are made in error and all changes can be audited. In addition, integrity constraints can be placed on your DB tables to ensure you don't mistakenly update or change variables/column in ways you deem inappropriate (e.g. dates stay as dates and other information is type cast appropriately). I won't even get into the niceties of security of your data in a database versus a text file (it would be especially important to work with a DB and secure it appropriately if your data contains sensitive or personally identifying information).

If you like spreadsheets because it somehow facilities your data entry, that can be overcome in every database I've ever used by using the graphical user interface tools/IDEs that come with databases (e.g. Microsoft's Management Studio) or by pulling in a linked version of your database into a system specifically designed for entering your data and enforcing data constraints (e.g. linked table forms in Access or a custom web interface). You can also use other programs that will allow you to get the best of both worlds and update data in Excel and have those change propagate to your database (see https://www.youtube.com/watch?v=5iyuF_mDSac for example).

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

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/

Answer 9

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.

Answer 10

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.