[SOLVED] Mutipule .xls files convert into single .csv

satyarankireddy · 09-22-2019, 02:05 AM

On a daily basis, I have received 20 .xls files in Linux box. Each file starts from 6 rows and converts into single.csv file.

ex:-

.xls file 1 contains 106 rows
.xls file 2 contains 206 rows
.xls file 3 contains 56 rows

all three files data convert into a single CSV file. Single CSV file count should be 350.

Using below command i can able to convert .xls file to csv with single file
unoconv -f csv -e FilterOptions="59,0,0,1" test_amount1.xls

can anyone let me know how to implement single shot to all files conversion from .xls to .csv

berndbausch · 09-22-2019, 04:38 AM

Quote:

Originally Posted by satyarankireddy

can anyone let me know how to implement single shot to all files conversion from .xls to .csv

According to the man page, unoconv can process several xls files at once:

Code:

unoconv -f csv -e FilterOptions="59,0,0,1" test_amount*.xls

I haven't tried it out, though.

satyarankireddy · 09-22-2019, 08:11 AM

Quote:

Originally Posted by berndbausch

According to the man page, unoconv can process several xls files at once:

Code:

unoconv -f csv -e FilterOptions="59,0,0,1" test_amount*.xls

I haven't tried it out, though.

Ok, how to add starting point line? I mean each file start with 6 row onwards ?

Awk command we can't use this command. Are there any method to tell the code to start data extraction from 6 row onwards for each file.

berndbausch · 09-22-2019, 08:23 AM

Quote:

Originally Posted by satyarankireddy

Ok, how to add starting point line? I mean each file start with 6 row onwards ?

You could use --stdout and pipe the output into awk:

Code:

unoconv ... --stdout | awk 'NR>6' > out1.csv

then cat the three files

Code:

cat out[123].csv >out.csv

I don't know what you mean by "single shot".

Firerat · 09-22-2019, 08:59 AM

this is the same question as your "solved" thread

https://www.linuxquestions.org/quest...0/#post6037519

Manpage
https://linux.die.net/man/1/unoconv

Code:

For example you might want to use this for a
real comma-separated document:

    -i 44,34,utf-8,2,1/5/2/1/3/1/4/1

which will
use a comma (44) as the field separator,
a double quote (34) as the text delimiter,
UTF-8 for the input encoding,
start from the second row
and use the specified formats for each column (1 means standard, 5 means YY/MM/DD date)

why are you having problems working this out?

satyarankireddy · 09-22-2019, 09:21 AM

Quote:

Originally Posted by berndbausch

You could use --stdout and pipe the output into awk:

Code:

unoconv ... --stdout | awk 'NR>6' > out1.csv

then cat the three files

Code:

cat out[123].csv >out.csv

I don't know what you mean by "single shot".

how to capture files name list which are involved in below command? I mean how many files are converted from .xls to CSV , I have to capture those file names into .txt file? Does it possible?

unoconv -f csv -e FilterOptions="59,0,0,1" test_amount*.xls

berndbausch · 09-22-2019, 09:39 AM

Quote:

Originally Posted by satyarankireddy

how to capture files name list which are involved in below command? I mean how many files are converted from .xls to CSV , I have to capture those file names into .txt file? Does it possible?

unoconv -f csv -e FilterOptions="59,0,0,1" test_amount*.xls

I don't know if I understand your requirement. First I thought you wanted to put everything into a single file ("single shot", you said). Now?

In case you want one csv file for each xls file:

Code:

for file in *.xls
do
  unoconv .... $file | awk ..... > ${file%.xls}.csv
done

${file%.xls} strips the xls extension from the filename.

Firerat · 09-22-2019, 09:56 AM

Code:

#!/bin/bash
Output="/path/to/onebigfile.csv"

for xls in /path/to/incomingDir/*.xls
do
    unoconv -f csv \
    -e FilterOptions="59,0,0,1" \
    "${xls}" \
    --stdout \
    | awk 'NR>5' "${csv}" >> "${Output}"
done

Firerat · 09-22-2019, 10:36 AM

This one does away with awk

Code:

#!/bin/bash
Output="/path/to/onebigfile.csv"
for xls in /path/to/incomingDir/*.xls
do
    unoconv -f csv \
    -e FilterOptions="59,34,76,6" \
    "${xls}" \
    --stdout \
    >> "${Output}"
done

; as field separator
" as text delimiter
utf-8 encoding
start at row

satyarankireddy · 09-22-2019, 10:47 AM

Quote:

Originally Posted by Firerat

This one does away with awk

Code:

#!/bin/bash
Output="/path/to/onebigfile.csv"
for xls in /path/to/incomingDir/*.xls
do
    unoconv -f csv \
    -e FilterOptions="59,34,76,6" \
    "${xls}" \
    --stdout \
    >> "${Output}"
done

; as field separator
" as text delimiter
utf-8 encoding
start at row

Error:

unoconv: file `--stdout' does not exist.
unoconv: RuntimeException during import phase:
Office probably died. Unsupported URL <file:///conv/--stdout>: "type detection failed"
unoconv: file `--stdout' does not exist.

satyarankireddy · 09-22-2019, 10:51 AM

[QUOTE=satyarankireddy;6039393]Error:

Firerat · 09-22-2019, 10:52 AM

post output of

Code:

unoconv --help

and did you replace /path/to/incomingDir/ with correct dir?

satyarankireddy · 09-22-2019, 10:55 AM

Quote:

Originally Posted by Firerat

post output of

Code:

unoconv --help

and did you replace /path/to/incomingDir/ with correct dir?

Finally, I am able to convert all .xls file into single .csv. Everything looks good. Now, I just want to see what are the files are converted using this command. Just for audit purpose. How to inject the audit log in this code?

for input in test_amount*.xls; do
unoconv -f csv -e FilterOptions="59" --stdout $input
done > test_data.csv

Ex: If this code 10 files converted to .csv then I have to capture and store the all 10 files name into one .log file.

Firerat · 09-22-2019, 10:56 AM

Quote:

Originally Posted by satyarankireddy

Error:

Finally, I am able to convert all .xls file into single .csv. Everything looks good. Now, I just want to see what are the files are converted using this command. Just for audit purpose. How to inject the audit log in this code?

Code:

for input in test_amount*.xls; do
    unoconv -f csv -e FilterOptions="59,0,0,1" --stdout $input | tail -n +6 
done > test_data.csv

Ex: If this code 10 files converted to .csv then I have to capture and store the all 10 files name into one .log file.

why are you skipping line 1 and then skipping 6 lines ?

do you want to ignore the first 7 rows?

edit you are starting at row 1, then dropping 6
why not start at 7?

satyarankireddy · 09-22-2019, 10:58 AM

Quote:

Originally Posted by Firerat

why are you skipping line 1 and then skipping 6 lines ?

do you want to ignore the first 7 rows?

the code was skipping first 6 rows for each file and converted to single .csv file. It is working fine as expected. Now, I want to capture files names into single .log file for audit purpose.

Do you have any idea ?