1

Need to convert an excel file to pipe delimiter file in Linux using shell script. pls help

I'm new to shell script. I saw a post to convert comma to pipe using the below code...

awk '{for(i=1; i<=NF; i+=2) gsub(",","|",$i)}1' FS=\" OFS= file

how to change this command for excel file to pipe?

6
  • Is it actually an excel file, with extension xls or xlsx? Or a plain-text CSV file? – muru Jul 10 '19 at 5:51
  • yes its an excel file with .xlsx extension – user3844609 Jul 10 '19 at 5:54
  • Then you can't use awk directly. You'll first have to get a CSV file using, for example, the command in unix.stackexchange.com/q/259361/70524 – muru Jul 10 '19 at 5:56
  • 1
    Protip: an .xslx is just a zip containing XMLs. Unzip, run awk over all XMLs, rezip should work. Atleast that's what I do with python – Panki Jul 10 '19 at 7:39
  • Excel should be able to export the file with whatever delimiter you'd want to use. – Kusalananda Jul 12 '19 at 9:01
1

I know this is is cheating but if you have gnumeric+ssconvert (my favorite Linux excel) you may

 ssconvert -O 'separator=| format=raw quoting-mode=never' in.xlsx out.txt

See also unoconv, and pandoc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.