Bug report #8421

"import delimited text layer" does not work with files created on excel/osx

Added by Arthur Vignacq over 6 years ago. Updated 8 months ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/Delimited Text
Affected QGIS version:master Regression?:No
Operating System:osx Easy fix?:No
Pull Request or Patch supplied:No Resolution:end of life
Crashes QGIS or corrupts data:No Copied to github as #:17190

Description

Hello,

I just tried to import a CSV file in QGIS 1.9 master. But even if I select ; as the separator factor, QGIS just think everything is on the same row.

The import of the same file work very well in 1.8

Cheers

58.png (184 KB) Giovanni Manghi, 2013-08-08 01:03 AM

CSV.tiff (1.22 MB) Arthur Vignacq, 2013-08-08 01:55 AM

XY_Ports_VNF.csv Magnifier (748 Bytes) Arthur Vignacq, 2013-08-08 02:05 AM

History

#1 Updated by Giovanni Manghi over 6 years ago

  • Status changed from Open to Feedback

did you choose custom delimiter -> semicolon?

#2 Updated by Arthur Vignacq over 6 years ago

Giovanni Manghi wrote:

did you choose custom delimiter -> semicolon?

I don't have this option. In custom delimiter I can choose between; "," "tab" "space" "colon" and ";"
Neither work. All the content is place in 1st row. the exact same file with same procedure work fine in 1.8

#3 Updated by Giovanni Manghi over 6 years ago

Arthur Vignacq wrote:

Giovanni Manghi wrote:

did you choose custom delimiter -> semicolon?

I don't have this option. In custom delimiter I can choose between; "," "tab" "space" "colon" and ";"
Neither work. All the content is place in 1st row. the exact same file with same procedure work fine in 1.8

see attached image.

PS
Are you sure you are running the latest master release?

#4 Updated by Arthur Vignacq over 6 years ago

Yes, I think it's the last release as I downloaded it yesterday (Mac osX).

My visual attached.

#5 Updated by Giovanni Manghi over 6 years ago

Arthur Vignacq wrote:

Yes, I think it's the last release as I downloaded it yesterday (Mac osX).

My visual attached.

please attach a sample of you data

#6 Updated by Arthur Vignacq over 6 years ago

please see attached file

#7 Updated by Regis Haubourg over 6 years ago

Your field nom as sometime text delimiters, sometimes not. Could you try to export csv with another tool and see if it changes something?
I had many troubles here to export correctly csv for all possible cases (encoding, decimal delimiter, text delimiter.. )

#8 Updated by Giovanni Manghi over 6 years ago

regis Haubourg wrote:

Your field nom as sometime text delimiters, sometimes not. Could you try to export csv with another tool and see if it changes something?
I had many troubles here to export correctly csv for all possible cases (encoding, decimal delimiter, text delimiter.. )

saving the file to libreoffice calc and then resaving it as csv make the resulting file work. My guess is that there is something wrong on how the file was originally created, I remember that the OsX for example had problems of this kind.

#9 Updated by Giovanni Manghi over 6 years ago

The issue seems to be this one

http://developmentality.wordpress.com/2010/12/06/excel-2008-for-macs-csv-bug/

[email protected] ~/Downloads $ file XY_Ports_VNF.csv
XY_Ports_VNF.csv: ASCII text, with CR line terminators

when resaved with libreoffice

[email protected] ~/Downloads $ file test2.csv
test2.csv: ASCII text

please check and report back.

#10 Updated by Arthur Vignacq over 6 years ago

The weird thing, is that QGIS 1.8, perfectly read this same file with the possible mistake office mac made.
I'll try it with the 2011 version of Microsoft office in few minutes

#11 Updated by Giovanni Manghi over 6 years ago

Arthur Vignacq wrote:

The weird thing, is that QGIS 1.8, perfectly read this same file with the possible mistake office mac made.

the tool has been completely rewritten, so no surprise that now works this way.

#12 Updated by Arthur Vignacq over 6 years ago

Just used Office 2011. Same problem when saved as .. "csv "
Re-tried with a save as " windows _ csv". This time QGIS read it fine.
Although 3 element coming with 2x " at beginning and ending of name ? I've seen no reason for that.

#13 Updated by Regis Haubourg over 6 years ago

Those with quotes are cells that contain carriage returns it seems.
Carriage returns an csv are really a mess. After large testing, we now use here xls 2003 as a pivot format for attribute data. (but it needs good cleaning (no formula, no encoded field names).
In my theorical roadmap, I would like to fund refactoring of vector layer dialog to give impor options and preview, just like csv plugin, but for all data types. that would help. Anyway, csv and carriage return will always be a mess since there is no CSV norm..

#14 Updated by Giovanni Manghi over 6 years ago

regis Haubourg wrote:

Anyway, csv and carriage return will always be a mess since there is no CSV norm..

Then I would propose to close this ticket, or eventually make it low priority and change its title (please propose something).

#15 Updated by Giovanni Manghi about 6 years ago

  • Category set to C++ Plugins
  • Operating System set to osx
  • Target version set to Future Release - High Priority
  • Subject changed from CSV import QGIS not understanding ; as separator to "import delimited text layer" does not work with files created on excel/osx

#16 Updated by Alexander Bruy over 5 years ago

  • Category changed from C++ Plugins to Data Provider/Delimited Text

#17 Updated by John Tull over 5 years ago

I don't know if this could present a potential solution, but you could use dos2unix (i.e., 'dos2unix -c mac foo.csv') to preprocess cdv files on OSX. For the uninitiated, trying to add csv layers with mac line encodings causes qgis to freeze requiring a force quit. Using dos2unix would add a dependency for the mac platform, but this seems preferable to crashing the program when using a feature that is pretty commonly needed.

To implement, there would need to be an OS check to force OSX to run this operation. I can only assume that this is doable. FWIW, dos2unix is available via homebrew.

http://waterlan.home.xs4all.nl/dos2unix.html

#18 Updated by mac joost over 5 years ago

For me, the issue importing CSV files on a Mac is that the importer doesn't support the default mac "record separator" (the line ending).

On a mac, the default line eding is CR (carriage return).

Unix line endings (LF: line feed) and Windows line endings (CRLF) are supported.

Changing the line ending of your csv file to eg. CR will make QGIS import the file just fine (I used TextWrangler).

It would be nice though if the importer could just support CR line endings or have it configurable.

#19 Updated by Giovanni Manghi over 5 years ago

  • Status changed from Feedback to Open

#20 Updated by Dewey Dunnington almost 5 years ago

I realize this issue is over 5 months old, but I'm about to use QGIS to teach a GIS short course to 40 or so people who will use it for various basic biology and geology map making and some basic analysis. I managed to convince our department to choose QGIS instead of Arc because it's become stable and for anything we use it for on a daily basis it does everything we need. This particular issue is a major, major stumbling block in getting people to adopt the program, because the easiest way to get our field data into QGIS is by making a csv. Anybody with a mac in our department (which is almost everybody) does this with Excel 2008 or 2011, which both have the problem of separating lines with carriage returns instead of newline characters. It seems as though this issue is an extraordinarily easy fix (separate lines by \
\
, then by \
, then by \
), and I realize there are more exciting features and developers only have so much time. However, I would just like to reiterate how important this very simple issue is to the adoption of QGIS. Nobody in our department can use the beautiful new symbology editor if they can't get their data into the program.

#21 Updated by Benjamin Warr almost 5 years ago

I repeat the plea made by Dewey. The standard or 'default' encoding for csv imports appears to be UTF-8, please can someone ensure that csv imports can be made on macs's !

#22 Updated by Giovanni Manghi almost 5 years ago

Benjamin Warr wrote:

I repeat the plea made by Dewey. The standard or 'default' encoding for csv imports appears to be UTF-8, please can someone ensure that csv imports can be made on macs's !

If this fix is so vital for you (and in general for Mac users) then please consider supporting its fix.

#23 Updated by Benjamin Warr almost 5 years ago

Hi Giovanni, my apologies for a newbie question, but I am not sure how to "support the fix".
Ben

#24 Updated by Giovanni Manghi almost 5 years ago

Benjamin Warr wrote:

Hi Giovanni, my apologies for a newbie question, but I am not sure how to "support the fix".
Ben

Hire a developer, see for example

http://qgis.org/en/site/forusers/commercial_support.html

#25 Updated by Giovanni Manghi over 2 years ago

  • Regression? set to No
  • Easy fix? set to No

#26 Updated by Giovanni Manghi 8 months ago

  • Status changed from Open to Closed
  • Resolution set to end of life

Also available in: Atom PDF