LinuxQuestions.org - is it possible to convert txt to xlsx in command line?

- Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)

- - is it possible to convert txt to xlsx in command line? (https://www.linuxquestions.org/questions/linux-newbie-8/is-it-possible-to-convert-txt-to-xlsx-in-command-line-4175477596/)

nouse

09-18-2013 08:01 AM

is it possible to convert txt to xlsx in command line?

Hi there,
Since you guys really helped me with my last issue:

I have this 300+ txt files (containing character-delimited data) that i would like to convert to xlsx. The manual way would include opening the file with LibraOffice, and save it as xlsx.

However, i feel there must be a batch mode?! It is incredibly tedious)

A really nice addon would be the manipulation of the data
(what i do:
- text to columns, using "semicolon" and "tab" as delimiter
- Insert column in A, add numbers 1-n to each line (to preserve original data structure if needed).

But anything that save me some clicks is very welcome.

Thank you so much!

/edit:

cp/mv *.txt *.xlsx did it. Very simple :(

I need to think about a for loop then. Sorry for bothering.

However, if you have ideas about the other two things (but i would need R for the column adding, i guess).

schneidz

09-18-2013 08:24 AM

this mite help:
https://duckduckgo.com/?q=csv2xls

i never used it ?

pwc101

09-18-2013 08:36 AM

Convert CSV to XLSX:

Code:

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

I'll leave the conversion from character delimited to comma delimited to you.

edit:

This should get you started on the rest.

Code:

for file in *.txt; do

    tr "\t;" "," < "$file" | paste -d, <(seq 1 $(wc -l < "$file")) - > "${file%.*}.csv"

    soffice --headless --convert-to xlsx:"Calc MS Excel 2007 XML" "${file%.*}.csv"

done

julienfmills

09-18-2013 12:22 PM

[QUOTE=pwc101;5029969]Convert CSV to XLSX:

Code:

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

This is great. I wish I had know about this before. Thanks for this.

nouse

09-19-2013 03:52 AM

Dear pwc101,

many thanks for your help. However, two issues occured:

1. The single line
soffice --headless --convert-to xlsx:"Calc MS Excel 2007 XML" somefile.csv
is accepted (i.e. there is no error message), but fails to do anything. No file is produced.
2. I tried to run your script (MANY thanks for your effort, btw) in an executable, but i get:
"ConvertXLSX.sh: Syntax error: "(" unexpected"

What do i do wrong?

Thank you!

All times are GMT -5. The time now is 06:52 AM.