Ask Your Question
0

how to convert csv to xlsx keeping column format as text

asked 2017-09-02 05:44:50 +0200

skd070292 gravatar image

while converting csv to xlsx, need to keep all the format of the cell in text format

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-09-02 08:59:44 +0200

JBF gravatar image

updated 2017-09-02 13:02:26 +0200

In the csv import dialog, select each column and choose Text in the dropdown list just above.

Once your csv file is imported you can check that each column is text formatted. If there are numbers they are aligned right instead of aligned left as they would be if they have been imported as numbers.

I think the correct command is (after pre-process to add double quotes around numbers):

libreoffice --convert-to xlsx:"Calc MS Excel 2007 XML" --infilter=CSV:44,34,UTF8,,,,true test.csv

Note the "true" in the --infilter parameters. It works for me.

Reference: https://wiki.openoffice.org/wiki/Docu...

edit flag offensive delete link more

Comments

Hi JBF,

thanks for reply.

But I am trying to convert csv to xlsx from command line as i need to embed it in shell script.

I am able to convert the same by following set of command:- soffice --convert-to xlsx:"Calc MS Excel 2007 XML" file.csv

it is converting fine but the cell format gets changed to Number whereas i need text.

skd070292 gravatar imageskd070292 ( 2017-09-02 09:21:01 +0200 )edit

Well, giving all important information in the question is better. :-(

Is it conceivable to pre-process the csv files to enclose each field between double-quotes?

JBF gravatar imageJBF ( 2017-09-02 09:44:01 +0200 )edit

yes it can be.

i have tried the same but its not working.

like:-

file.csv has data like this

"barcode","0781","01"

"123456789012345678901","0781","01"

"109876543210987654321","0781","01"

then if i execute following command :-

soffice --convert-to xlsx:"Calc MS Excel 2007 XML" file.csv

it converts csv to xlsx but cell values gets changed to exponential values where as i need in text format

skd070292 gravatar imageskd070292 ( 2017-09-02 10:12:25 +0200 )edit

Indeed, I get the same thing in the UI. That said it works if I check the checkbox "Quoted fields as text" it works but this setting seems not be taken in account if LO is used in CLI. :-(

JBF gravatar imageJBF ( 2017-09-02 12:23:00 +0200 )edit

yeah...is there any way to do it

skd070292 gravatar imageskd070292 ( 2017-09-02 12:36:51 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2017-09-02 05:44:50 +0200

Seen: 927 times

Last updated: Sep 02 '17