How to fix a CSV file that is not being converted

Submitted by admin on Sun, 07/16/2023 - 09:37

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, 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, 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, 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, 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:**

Categories:

Tutorials

Videos

Comments

Carlie Risk replied on Tue, 03/22/2016 - 03:32 Permalink

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!!

admin replied on Mon, 05/09/2016 - 23:27 Permalink

Are you using Microsoft Excel? OpenOffie? Numbers?

mellycat replied on Fri, 05/18/2018 - 11:21 Permalink

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

admin replied on Fri, 05/25/2018 - 13:17 Permalink

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

Thomas Dickinson replied on Sat, 04/09/2016 - 19:53 Permalink

? followed the steps. Why a comment?

Patrick McQuillin replied on Sun, 08/14/2016 - 03:15 Permalink

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?

admin replied on Fri, 08/19/2016 - 01:31 Permalink

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.

Edwin Barker replied on Wed, 11/13/2019 - 06:57 Permalink

Patrick is absolutely correct! Excel has no prompts to edit text and field delimiters!

Mary Esseff replied on Thu, 09/15/2016 - 08:02 Permalink

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?

Donury236 replied on Tue, 01/03/2017 - 00:01 Permalink

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.

E W Jones replied on Sun, 03/12/2017 - 17:31 Permalink

Not sure if this worked

connie bailey replied on Wed, 11/01/2017 - 03:14 Permalink

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.....

mellycat replied on Fri, 05/18/2018 - 11:22 Permalink

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

Paul Lastname replied on Wed, 01/23/2019 - 16:42 Permalink

I have no idea what to do. I ran your script. All the fields appeared on your site but the exported QIF file only had a few characters and none of the data. Error said something about date format but the file was just made today and that is not even part of the file?

What is difference between CSV and QIf and can I just change the extension name to import into GnuCash?

admin replied on Mon, 04/08/2019 - 12:33 Permalink

Hi Paul, I suspect the mapping is incorrect, can you please create an issue https://csvconverter.biz/project/issues and attach your csv so we can debug it

Jim Clark replied on Fri, 02/01/2019 - 16:58 Permalink

This is non-information! When I open it it opens in an Excel spreadsheet with non of the options mentioned.

KZeOkXfh8WCLnkib replied on Sun, 02/17/2019 - 00:08 Permalink

The only way to change these options using Excel is to actually change your Language and Region settings in the control panel in Windows. It cannot be done within Excel itself.

You need to update your information and stop telling people it can be done as you're just plain incorrect.

The most frustrating thing, the legacy site handled these Excel csv files without any issue...

David Casson replied on Sat, 03/02/2019 - 18:08 Permalink

Y'all- if you're using a Windows platform -- I'm using Windows 10 with Office 365 -- go to File > Save As and from the "Save as type" dropdown menu choose CSV UTF-8 (Comma delimited) *.csv option. This enabled my budgeting app (You Need A Budget) to read my bank import file whereas it would NOT read the same file when saved as a different option, "CSV (comma-delimited) *.csv". To be clear, there are MULTIPLE CSV options, at least in my version of Excel; you MUST choose the option that explicitly mentions UTF-8. Hope that helps.

P G replied on Fri, 03/22/2019 - 00:09 Permalink

This does not work

Peter Wood replied on Wed, 10/09/2019 - 15:44 Permalink

Definitely does NOT work with Windows10

admin replied on Sat, 12/21/2019 - 16:21 Permalink

This solution is done using OpenOffice, its free to download and try

Jane Hepburn replied on Sat, 08/13/2022 - 13:14 Permalink

Thank you. Just did the free download of OpenOffice and it worked like a dream. Much appreciated. OS X El Capitan

Bea Deecee replied on Sun, 10/30/2022 - 01:45 Permalink

This converter works quite well when working with regular bank accounts. However, I am looking for something that will convert investment accounts to QIF. The current mapping options do not have the heading associated with this. Am I missing something? Thanks.

admin replied on Wed, 11/02/2022 - 00:20 Permalink

Investment accounts are not supported at this occasion

Marcel Gerber replied on Wed, 03/15/2023 - 10:05 Permalink

Sorry, but why do we have to edit the file, when it would be simple enough to allow for these kind of settings. It adds a manual step that should be prevented.

Also date formats are on only offered with / as separator, why not in the international format or a self defined format ?

The service looked promising, but will not use it.

admin replied on Sat, 03/18/2023 - 09:47 Permalink

Hi Marcel

The algorithms to detect date format are complex due to the different date formats used by banks. Unfortunately there is no standards in the way the banks export CSVs so we have to try to figure out the data format the best we can, examples:

Depending on the country, the year comes first
Depending on the country, the month comes before the date
Some dates have the month name, some countries have different translation for month names
Some dates will be separated by / or - or .

Also, the lack of standards is not only for dates, the algorithms also try to detect:

The line endings (this varies depending of operating system i.e Windows, Mac or Linux are all different
Field separators can be , or ; or anything else
Fields are sometimes wrapped by quotes, some times they aren't

There are also other edge cases, which makes it impossible to support every CSV. We are always improving the algorithms and if you have a CSV that is not being converted, feel free to create a Bug issue and attach the CSV for investigation.

The recommended manual step helps to normalise the CSV by having a standard date format, add quotes on all fields and add the same field separator on all rows.

tzina replied on Sun, 12/03/2023 - 01:16 Permalink

I'm trying to do credit card transactions. The mapping looks right - except that Quicken is interpreting everything as a credit/payment. The payments are right, but all the charges are debits. I suspect it's the minus signs before the amounts that moneyspire is adding to the csv file. I'm not sure how to flip them.

admin replied on Sat, 01/27/2024 - 19:34 Permalink

Hi Tzina

On the thrird step, there is a Checkbox: This is a Credit Card statement

Make sure this is ticked for Credit card statements.

Regards,

Marc

John Hall replied on Wed, 08/28/2024 - 18:47 Permalink

I am having a lot of problems using this product. What ever I do i get the same message saying the csv file is invalid.

Butt what I want is the error report saying what is wrong, so I can attempt to fix the errors.

admin replied on Mon, 09/16/2024 - 14:38 Permalink

Hi John, there are many different possible reasons. Due to the fact that CSV files do not have a strict format, it is not easy to give explanation whithout manually looking into each case.

With time, we have added more and more logic to deal with these variations.

What I suggest, is that you create an issue (Bugs & Issues menu) and attach the CSV, so we can check.