You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Export CSV not escaping columns or newlines in 3.9.1 #1037
hi, i tried exporting some tables to CSV and i saw that any columns with newlines weren't quoted, nor was the newlines themselves escaped, causing issues with parsing/importing in other contexts.
I'd also try the nightlies, as v3.9.1 is relatively old now - so many fixes have taken place since that release.
Out of interest (related to this...) how many fixes have taken place since 3.9.1? I might put a note on the download page saying that if you're downloading 3.9.1, it might be worth trying the nightly (pre 18 May, of course...) as its much better .... v3.9.2 if you will.... ;)
@justinclift hey sorry i lost track of emails -- it does not work :( i mean, it doesn't escape newlines within the columns when i set to one of the dropdown options. the issue i think is just escaping the columns properly
@justinclift - How did you enter the multiline text? On Windows, using the 'Edit Database Cell' doesn't work properly. Actually, its more the 'import' and 'export' options don't work properly.
When I created multiline fields and exported them, the CSV was without line breaks.
Interesting. I was testing stuff on OSX, and it seemed to work for me. I'm not sure if I tried multi-line-within-a-field stuff though... I think I may have misunderstood the problem then. Oops. 😇
@chrisjlocke What happens if you open the CSV file in a different editor, e.g. Wordpad (if that still exists on newer Windows version 😉)? Do the line breaks appear then? I'm asking because Notepad doesn't show \n line breaks at all and I can imagine that the line breaks in the columns are \n, not \r\n like the line separator.
If the line break does appear inside the column when viewing it in a different editor (for me it does), then that is a correct CSV file: you'd have the line break (unescaped!) but inside a quoted column. According to the CSV standard (see here, point 2.6: https://tools.ietf.org/html/rfc4180#section-2) that's how you're supposed to do it. And apparently Excel is doing the same thing, at least according to this answer.
Hmm, I'm still not 100% sure about this 😉 I've just made a commit to force-add quotes if there is a newline character inside a field. Prior to this quotes would have only been added when a quote char was selected. But even if none is set, adding quotes might make sense because otherwise the resulting CSV file is pretty much unparsable.
@kaisershahid Does this already help in your case? You can test it by downloading and installing tomorrow's nightly build (see our main page for the link). If it doesn't can you make a screenshot of the CSV settings in the dialog?
I'll close this now, but if it turns out to still need a bit more tweaking to get this issue fully solved you're definitely welcome to re-open it and let us know. 😄
Activity
[-]Export CSV not escaping columns in 3.9.1[/-][+]Export CSV not escaping columns or newlines in 3.9.1[/+]justinclift commentedon Jun 8, 2017
Interesting. Do you have an example .csv file with the problem result which we could take a look at? 😄
kaisershahid commentedon Jun 8, 2017
sure thing! although the examples contain private info that i shouldn't attach here--is there a way i can send you something privately?
justinclift commentedon Jun 8, 2017
Sure, email me directly at justin AT postgresql DOT org. 😄
chrisjlocke commentedon Jun 8, 2017
Don't forget that exporting (on Windows in v3.9.1) didn't set any options...
Check the export options are set...
chrisjlocke commentedon Jun 9, 2017
I'd also try the nightlies, as v3.9.1 is relatively old now - so many fixes have taken place since that release.
Out of interest (related to this...) how many fixes have taken place since 3.9.1? I might put a note on the download page saying that if you're downloading 3.9.1, it might be worth trying the nightly (pre 18 May, of course...) as its much better .... v3.9.2 if you will.... ;)
justinclift commentedon Jun 17, 2017
@kaisershahid Did exporting with newlines selected in the drop down work? 😄
kaisershahid commentedon Jun 17, 2017
@justinclift hey sorry i lost track of emails -- it does not work :( i mean, it doesn't escape newlines within the columns when i set to one of the dropdown options. the issue i think is just escaping the columns properly
justinclift commentedon Jun 17, 2017
Damn, I'm at a loss on how to figure this one out. For me, the newline options work ok with the database you emailed me... gahh. 😦
I'll need to think on this a bit more I guess. 😄
chrisjlocke commentedon Jun 27, 2017
@justinclift - How did you enter the multiline text? On Windows, using the 'Edit Database Cell' doesn't work properly. Actually, its more the 'import' and 'export' options don't work properly.
When I created multiline fields and exported them, the CSV was without line breaks.
justinclift commentedon Jun 27, 2017
Interesting. I was testing stuff on OSX, and it seemed to work for me. I'm not sure if I tried multi-line-within-a-field stuff though... I think I may have misunderstood the problem then. Oops. 😇
chrisjlocke commentedon Jun 27, 2017
Clues in the title.... ;)
If a field contains a carriage return (or newline...) then its not exported at all (as per my screenie above)
MKleusberg commentedon Jun 28, 2017
@chrisjlocke What happens if you open the CSV file in a different editor, e.g. Wordpad (if that still exists on newer Windows version 😉)? Do the line breaks appear then? I'm asking because Notepad doesn't show
\nline breaks at all and I can imagine that the line breaks in the columns are\n, not\r\nlike the line separator.If the line break does appear inside the column when viewing it in a different editor (for me it does), then that is a correct CSV file: you'd have the line break (unescaped!) but inside a quoted column. According to the CSV standard (see here, point 2.6: https://tools.ietf.org/html/rfc4180#section-2) that's how you're supposed to do it. And apparently Excel is doing the same thing, at least according to this answer.
justinclift commentedon Jun 28, 2017
@kaisershahid I can email the database file you sent me to @chrisjlocke and @MKleusberg for them to double check, if that's ok with you?
kaisershahid commentedon Jun 29, 2017
@justinclift yup that's fine! @MKleusberg what i was seeing is that the columns were not quoted at all (unless it had quote chars inside already)
justinclift commentedon Jun 29, 2017
@kaisershahid Just forwarded your database to @MKleusberg and @chrisjlocke. 😄
Enforce quote chars in CSV export for fields with line breaks
MKleusberg commentedon Jun 29, 2017
Hmm, I'm still not 100% sure about this 😉 I've just made a commit to force-add quotes if there is a newline character inside a field. Prior to this quotes would have only been added when a quote char was selected. But even if none is set, adding quotes might make sense because otherwise the resulting CSV file is pretty much unparsable.
@kaisershahid Does this already help in your case? You can test it by downloading and installing tomorrow's nightly build (see our main page for the link). If it doesn't can you make a screenshot of the CSV settings in the dialog?
kaisershahid commentedon Jul 1, 2017
@MKleusberg that should do it! i'll get the new build and test tomorrow, will keep you posted
kaisershahid commentedon Jul 1, 2017
@MKleusberg @justinclift @chrisjlocke looks like this is working now. thanks a lot for looking into this!
justinclift commentedon Jul 1, 2017
Awesome @kaisershahid. 😄
I'll close this now, but if it turns out to still need a bit more tweaking to get this issue fully solved you're definitely welcome to re-open it and let us know. 😄