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?

muru
  • 69,900
  • 13
  • 192
  • 292
user3844609
  • 11
  • 2
  • Is it actually an excel file, with extension `xls` or `xlsx`? Or a plain-text CSV file? – muru Jul 10 '19 at 05:51
  • yes its an excel file with .xlsx extension – user3844609 Jul 10 '19 at 05:54
  • Then you can't use awk directly. You'll first have to get a CSV file using, for example, the command in https://unix.stackexchange.com/q/259361/70524 – muru Jul 10 '19 at 05: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 07:39
  • Excel should be able to export the file with whatever delimiter you'd want to use. – Kusalananda Jul 12 '19 at 09:01
  • I got the CSV file now. Unfortunately our prod linux is on EL6 and GAWK ver 3.x. Below command is using FPAT which is availabe only in EL7 GAWK 4.x. so, pls help How to convert CSV file to Pipe delim file using GAWK 3.x? gawk -v FPAT='[^,]*|"[^"]+"' -v OFS='|' '{print $1,$2,$3,$4,$6,$7}' file – user3844609 Aug 07 '19 at 05:20

1 Answers1

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

JJoao
  • 11,887
  • 1
  • 22
  • 44