Recently I had to do my taxes and having an online store, I had to go through 9000 transactions in Paypal that I needed categorize. Paypal lets you download your transaction history as a csv (comma-delimited text) file with double quotes (“) as a text qualifier which is a standard format. Unfortunately, you can still run into problems if you have double quotes and commas in a field’s value as follows:
“Confirmed”,”8″”, 10″”, 12″” AR550 Gong Round Steel Shooting Target Set 1/2″” Thick ( AR500)”,”300977807743″,”0.00″,”0.00″,”0.00″,””,””,””,””,”Ebay”, ….
Notice that the 2nd field contains double quotes and commas as follows:
8″”, 10″”, 12″” AR550 Gong Round Steel Shooting Target Set 1/2″” Thick ( AR500)
The original text for this field is
8″, 10″, 12″ AR550 Gong Round Steel Shooting Target Set 1/2″ Thick ( AR500)
where the double quotes represent inches as a unit of measure and the comma indicates that there are multiple sizes (8 inch, 10 inch, and 12 inch). The double quotes were escaped with double quotes to indicated that the double quotes are part of the field’s value. However, a double quote followed by a comma messes everything up because there’s no way to know whether that comma is part of the field value or a field delimiter.
The fact that this field contains double quotes and commas breaks the csv format because it appears now that this row has more columns that those in other rows. This is one of many things that can break a csv file. To fix this and other issues, I have found that it is most easy to use a tool called CSV Easy. It’s fast and shows you clearly which rows need to be fixed. Here’s an example of the problem I faced earlier.
Continue reading Fix CSV Files & Import/Export Them Using MySQL & Excel