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

nouse · 09-18-2013, 08:01 AM

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!