How to fix a CSV file that is not being converted

Error opening CSV file
I you are having errors when converting your CSV file, it might be because the columns are not being recognized.
CSV Converter supports a wide range of CSV formats, but sometimes, it's difficult to figure out the structure.
 
Each operating system has its own encoding, the most common is UTF-8. Linux writes files in a different way that Mac OS or Linux do. Each provider will have a different field delimiter, like commas or semicolons.
The example below uses semicolons:
01 Jan 2015; Supermarket; -100,00
 
Each country has it's own character set with specific characters that are used only on that country. The decimal points and date format will also vary depending on the country.
The example below uses the date format YY/MM/DD, comma as field delimiter and dot as decimal point.
2015/07/31, Supermarket, -100.00
 
They will have different enclosures, for example: double quotes, or no enclosure at all. The line endings will also vary depending on the operation system.
 
One handy way of fixing this problem is by opening the CSV file in Excel and re-saving the file, while choosing the field delimiter and text delimiter. Requires Excel or Open Office (Free).
 

Step by step:

1. Open the file manager and double click the CSV file, or right click and Open in Excel. On this example I am using Open Office on Mac OSX. The options on Windows might be a little different but the principle is the same.

Step 1. Open in Excel
Step 1. Open in Excel, by admin

2. The first screen will ask how you want Excel to recognize the field delimiters and text delimiter. You can select one or more, as soon as you click on the options, you will see the effect below.

Step 2. Select filters
Step 2. Select filters, by admin

3. Click on the File menu and Save As. Excel will ask you where to save the file. You can either override the same file or save with a different name.

Step 3. Re-save the CSV
Step 3. Re-save the CSV, by admin
 

4. When the prompt for field delimiter shows, choose semicolon (;). For text delimiter choose double quotes ("). I also tick the box to Quote all text cells.

Step 4. Choose delimiters
Step 4. Choose delimiters, by admin
 
Done, now try to import the re-saved file.

Watch the video tutorial

 

If you don't have Microsoft Excel, you can download it for FREE:

Open office logo Libre Office logo
Categories: 

Comments

When I right click on file and select to 'open with Excel' it takes me directly to the opened spreadsheet IE I'm not given the option to select the separators of the CSV so I'm unable to move past that step. Any suggestions? Thank you!!

Are you using Microsoft Excel? OpenOffie? Numbers?

she said rightclick and 'open with excel'...

On this video its Open Office for Mac, but you will find similar option in Microsoft Excel

? followed the steps. Why a comment?

I have commas in my data, unrelated to being a delimiter, which Excel exports correctly by enclosing the line in quotes. CSV Converter ignores what is enclosed by quotes and just adds the quotes as data characters. And so every comma becomes a separator, jumbling by fields. What a mess. When opening the csv file back in Excel, there are no prompts to edit text and field delimiters, as user Carlie Risk also experiences. How do I get CSV Convertor to honor standard csv file quotes?

The approach of the video using Excel to standardize the CSV is to have all fields enclosed by quotes and separated by semi-colon. Then it won't split on commas.

I tried to convert my cvs file from my brokerage house, but it doesn't have the correct designators to specify what each column if for. How can I fix this?

I have office 2013. I do not have any of the options you mention re the separators etc. When I open it in excel it opens as normal. Would be handy if you had an excel walk through as its not very clear as it is when non MAC users don't have the options you use/show.

Not sure if this worked

this program has come the closest to "working".....but Quicken wouldn't import...I reread the tips and I think the fields had duplication that made it not work. I've gone into Excel and tried to cleanup so now we'll see.....

excel on mac does not give you any options to change the delimiters.

Blogs

site_upgrade.jpg

Site upgrade

Dear users

This week we reached the highest peak of users browsing the site to convert CSV files. This pushed the servers a bit harder than usual and we had to upgrade it.