Ask Your Question
0

Convert-to from csv to ods and viceversa

asked 2015-07-13 16:39:11 +0100

mrta gravatar image

updated 2017-08-22 17:40:36 +0100

erAck gravatar image

I have this problem. A ".csv" file where delimiter is tab and content is a "Little-endian UTF-16 Unicode text" If i try to open it with gui in libreoffice my true settings are

Charset: Unicode Language: Default - English (USA) From row: 1 Separated by: tab, comma

via shel i'm trying

unoconv -f ods -e FilterOptions="59/44,34,UNICODE,1" [FILE]

but the result is a file with wrong separation. What's wrong?

And what's is the best shell command in order to convert this ods to file to a well generated csv (Unicode UTF8, comma separated, ecc.)?

I have tried to use LibreOffice from the Command line to convert from CSV file to Excel.While the software works great while using the GUI; it does not recognise Unicode characters, or semicolon delimited fields, and the output is only one chunk of data ( one column). How should I write my comenad line to let LibreOffice know I am using ";" as delimiter in a Unicode CSV file ? My command is: "C:\Program Files\LibreOffice 4\program\soffice.exe" --headless --convert-to xls --outdir "C:\DBA\scripts\CSV-to-Excel" "C:\DBA\scripts\CSV-to-Excel\CSV_file.csv" The output leaves a blank row in between two rows, and does not properly respect Unicode characters. The data ins left in only one column

edit retag flag offensive close merge delete

Comments

59/44 stand for separated by ; or , try with 9 instead

karolus gravatar imagekarolus ( 2015-07-14 12:11:37 +0100 )edit

With unoconv -f ods -e FilterOptions="9,34,UNICODE,1" is the same

mrta gravatar imagemrta ( 2015-07-15 17:52:17 +0100 )edit

FYI: I merged a question re saving as xls from csv to this one, so don't be suprised that some of the answeres refer to Excel export filter :-)

cloph gravatar imagecloph ( 2017-08-22 16:23:11 +0100 )edit

3 Answers

Sort by » oldest newest most voted
1

answered 2017-02-18 11:15:51 +0100

Mike Kaganski gravatar image

The CSV import/export filter flags for OpenOffice/LibreOffice is best described here.

There's no mention of endianness setting there. However, I suppose that Unicode UCS2 option mentioned on that page is OK for little-endian UTF-16.

So my suggestion would be

soffice --infilter="CSV:9/44,34,65535,1,,1033" --convert-to csv:"Text - txt - csv (StarCalc)":9,34,76,1,1 -outdir <some-another-dir> file.csv
edit flag offensive delete link more

Comments

Hi Mike, I've been trying to figure out all the options you have used on the --infilter but have a hard time finding the right info about this. Would you be so kind to point me to a place where I can find an explanation for these options? Especially the 65535 and 1033 were hard to find. Thanks in advance !

NicoVanLeeuwen gravatar imageNicoVanLeeuwen ( 2017-04-11 21:41:45 +0100 )edit

@NicoVanLeeuwen: did you look carefully at the answer you commented? It has the link to the information you asked - right in the first sentence (last word of it); and searching for both 1033 and 65535 on that page brings you to the places where those values are explained. Or did you have another issue? Then be specific please.

Mike Kaganski gravatar imageMike Kaganski ( 2017-04-12 06:42:29 +0100 )edit
1

answered 2017-02-03 10:47:53 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

This functionality is pretty cryptic and the best documentation unfortunately is from openoffice here but basically what you need is to lauch this command:

soffice --headless --convert-to xlsx:"Calc MS Excel 2007 XML" --infilter="csv:59,34,UTF8" file.csv

This will generate a file.xlsx with the proper content. Some important remarks:

  • Do not add the output file name, it's guessed automatically from the input, and it will give errors
  • The first value after the : is the ASCII code for the separator (59 is ;, 45 is - and so on..)
  • The second value is the text quoting (34 is ", 44 is ' and so on..)

This are the basics

edit flag offensive delete link more

Comments

If the origin file has '.' as the decimal separator a 1033 in the 6th place should be added to the filter (--infilter="csv:59,34,UTF8,,,1033")

aseques gravatar imageaseques ( 2017-02-03 16:26:33 +0100 )edit
0

answered 2017-01-22 17:38:46 +0100

JohnHa gravatar image

updated 2017-08-22 16:21:30 +0100

cloph gravatar image

Open the csv file (rename it to fred.csv if necessary). Choose the semi-colon separator.

NEVER save as a csv file or as a .xls or .xlsx file - always save as . .ods as you may lose data and/or formatting.

You may have switched off the warning LO gives you - switch it back on in Options.

Please upload a small example file so it can be tested.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-07-13 16:39:11 +0100

Seen: 3,341 times

Last updated: Aug 22 '17