QGIS won't read table columns in CSV import on Mac OS

Question

I am trying to import a CSV file into QGIS, but it ends up looking like my print screen below (program is in Swedish but in the preview window you can see how QGIS won't read my data correctly). I can't load the data since for some reason, QGIS reads my CSV table columns as one single row. What is wrong? I want QGIS to read my data as numbers, so I have created an additional csvt-file with the code "String","String","Integer". Is there something else that I need to to in order for QGIS to read the third column as numbers?

enter image description here

---

Still, after doing that it loos like this...enter image description here

enter image description here

• user30184: Add couple of rows of your csv file to the question.
• SAnderka: Yes, please show us at least a couple of lines from your CSV file!
• JohannaH: You can now take a look at my csv data!

Answer 1

I had the same problem of QGIS reading a CSV file (saved from MS Excel 2011 on my Mac) as a single row.

So, I re-saved the Excel worksheet as "Windows Comma Separated (.csv)" and QGIS was able to read it just fine.

Answer 2

Your data does not use commas as separator, but semicolons.

So you have to switch from CSV to user-defined separator, where you can select semicolon.

---

UPDATE

You seem to work on a Mac. There are some issues on line feed/ carriage return with CSV on Mac. That's why all data is in one line. See:

https://stackoverflow.com/questions/22052168/excel-saves-tab-delimited-files-without-newline-unix-mac-os-x

and the bug report http://hub.qgis.org/issues/8421

• JohannaH: Yes, I am working on a Mac. So there's a bug for importing csv's to QGIS in Mac... I'm an amateur in this, so is there anything I can do to overcome this problem or will the only simple solution be to work this out on a PC?
• AndreJ: You can try loading and saving the Excel file in LibreOffice. Or use a good text editor to replace the wrong newline from Excel with the right one, as explained in the linked articles.
• JohannaH: Tried saving the file in LibreOffice, no success unfortunately.
• AndreJ: Ok, so try the text editor.

Answer 3

I also use QGIS (since v1.8) on OS X. I never encountered the carriage-return issue while importing CSV files into QGIS. Did you create the CSV file yourself, i.e. with your computer?

I would suggest you to import the CSV file with a stat software such as R (or Stata, or whichever you prefer/already have), and re-export it.

• JohannaH: Yes, I created the CSV myself from Excel on my Mac. I am not familiar with R or Stata, but if there's a chance that this is the problem I will look in to it.

Answer 4

Hm... first of all, I don't see any coordinates in that data? The Add delimited text layer tool is meant for converting tables of data with coordinates attached into (e.g. shapefile) points.

If you just want to add the csv-file into your project for further joins (e.g. via country names, or ISO-codes), loading it directly via Add vector layer should to the trick. To check your attribute data just right-click on the new entry in the layer manager and choose Open Attribute Data.

• JohannaH: Well, I did this and performed the join successfully but since my ORIGINAL problem was that I could not get QGIS to recognize my data as numbers, I tried this instead. Maybe now I should reformulate my question. Since I want to perform an interval classification of my data, why doesn't QGIS recognize my data as numeric? As I said, I have created an additional csvt file.
• SAnderka: Judging by your screenshot, your CSVT-File should contain something like "string","string","real"?
• JohannaH: Yes, first it contained "String","String","Integer", now I changed it to "String",""String","Real", but I still have the same problem
• SAnderka: I guess QGIS misinterprets the formatting in your data. It looks like e.g. 1,52E+05 is a rounded value of a high-precision number in your original Excel data, but when you save as CSV, it will actually save this value (1,52E+05) in that cell, NOT the original higher precision number (just tested that). If there is a letter (E in this case)in there, QGIS will force string as your data type. You can check this if you open the csv file in a simple text editor.
• JohannaH: I tried changing my data so that it displays the whole decimal number instead of using E. This still doesn't do the trick through...

Answer 5

QGIS looks for a CR/LF at the end of each row in a .csv file. This is how Excel (and other applications) on Windows machines ends each row. On a Mac the default Excel .csv file only has a CR (i.e., there is no LF) at the end of each row. Therefore QGIS reads the file as one long row. The solution, as first suggested by ericO, is to save the file as a Windows comma separated (csv) file. This can be done in Excel on the Mac. Once saved in that format it will be read correctly by QGIS.

Answer 6

Want to improve this post? Provide detailed answers to this question, including citations and an explanation of why your answer is correct. Answers without enough detail may be edited or deleted.

I had the same problem but moved data over to Open Office and converted to CSV from OO. Then no problem

• Michael Stimson: This is no doubt a good suggestion but I'd prefer to see a bit more explanation to an answer, like how to convert CSV to OO perhaps. Can you expand a bit on this please; I realize there's not a whole lot more that can be added, please try though. This is probably because of the difference between the Windows CRLF line separators and NewLine on Mac.