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

satyarankireddy · 09-22-2019, 11:05 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?

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

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 ?

Firerat · 09-22-2019, 11:21 AM

Quote:

Originally Posted by satyarankireddy

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 ?

you do not need the tail

change
FilterOptions="59,0,0,1"
to
FilterOptions="59,0,0,6"

what file names?
the original xls files or the new csv ?
where is the log?

audit?
I doubt you guys have any real audit system in place after what I have seen.

You would have been retrained,
if that didn't work reassigned
if that didn't work dismissed.

Code:

#!/bin/bash
Output="/path/to/onebigfile.csv"
LogFile="/path/to/somelog.log"
for xls in /path/to/incomingDir/*.xls
do
    unoconv -f csv \
    -e FilterOptions="59,34,76,7" \
    "${xls}" \
    --stdout \
    >> "${Output}"
    printf  "$(date +%F\ %T.%N\ %Z) \"${xls}\" appended to \"${Output}\"\n" >> "${LogFile}"
done

berndbausch · 09-22-2019, 04:41 PM

Quote:

Originally Posted by satyarankireddy

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 ?

Well, you must know the file names in order to convert them. To write something to a log file, use echo.

Code:

echo test_amount1.xls >> logfile.log
echo test_amount2.xls >> logfile.log
....

You could also use ls:

Code:

ls test_amount*.xls >> logfile.log

Firerat · 09-22-2019, 05:05 PM

a cleaner printf

Code:

printf "%s %s appended to %s\n" \
       "$(date +%F\ %T.%N\ %Z)" \
        ${xls} \
        ${Output}

satyarankireddy · 09-23-2019, 01:38 AM

Quote:

Originally Posted by berndbausch

Well, you must know the file names in order to convert them. To write something to a log file, use echo.

Code:

echo test_amount1.xls >> logfile.log
echo test_amount2.xls >> logfile.log
....

You could also use ls:

Code:

ls test_amount*.xls >> logfile.log

This will capture all the files which are present in the current directory. But i want list of files are converted druing below code process ?

ls test_amount*.xls >> logfile.log -- This will not work for me.

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

The above code were processed 10 .xls files to single .csv file. I want to capture all 10 file names into >> filnames.log file. This time it was converted 10 files next run it may convert into 100 files etc...

Hope you understand my point?

I tried below option but didn't work for me.

for input in /test/test_amount_*.xls; do
unoconv -f csv -e FilterOptions="59,0,0,1" --stdout $input | tail -n +6
done > /test/bigfile.csv | $input >> filename.log

satyarankireddy · 09-23-2019, 01:42 AM

Quote:

Originally Posted by Firerat

you do not need the tail

change
FilterOptions="59,0,0,1"
to
FilterOptions="59,0,0,6"

what file names?
the original xls files or the new csv ?
where is the log?

audit?
I doubt you guys have any real audit system in place after what I have seen.

You would have been retrained,
if that didn't work reassigned
if that didn't work dismissed.

Code:

#!/bin/bash
Output="/path/to/onebigfile.csv"
LogFile="/path/to/somelog.log"
for xls in /path/to/incomingDir/*.xls
do
    unoconv -f csv \
    -e FilterOptions="59,34,76,7" \
    "${xls}" \
    --stdout \
    >> "${Output}"
    printf  "$(date +%F\ %T.%N\ %Z) \"${xls}\" appended to \"${Output}\"\n" >> "${LogFile}"
done

the above code is not working for me. Getting error.

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

Output:-

unoconv -h
usage: unoconv [options] file [file2 ..]

Convert from and to any format supported by LibreOffice

unoconv options:
-c, --connection=string use a custom connection string
-d, --doctype=type specify document type
(document, graphics, presentation, spreadsheet)
-e, --export=name=value set export filter options
eg. -e PageRange=1-2
-f, --format=format specify the output format
-i, --import=string set import filter option string
eg. -i utf8
-l, --listener start a permanent listener to use by unoconv clients
-n, --no-launch fail if no listener is found (default: launch one)
-o, --output=name output basename, filename or directory
--pipe=name alternative method of connection using a pipe
-p, --port=port specify the port (default: 2002)
to be used by client or listener
--password=string provide a password to decrypt the document
-s, --server=server specify the server address (default: 127.0.0.1)
to be used by client or listener
--show list the available output formats
--stdout write output to stdout
-t, --template=file import the styles from template (.ott)
-T, --timeout=secs timeout after secs if connection to listener fails
-v, --verbose be more and more verbose (-vvv for debugging)

Firerat · 09-23-2019, 01:50 AM

try this

Code:

set -x
Output="/path/to/onebigfile.csv"
LogFile="/path/to/somelog.log"
for xls in /path/to/incomingDir/*.xls
do
    unoconv -f csv \
    -e FilterOptions="59,34,76,7" \
    "${xls}" \
    --stdout \
    >> "${Output}"
    printf  "$(date +%F\ %T.%N\ %Z) \"${xls}\" appended to \"${Output}\"\n" >> "${LogFile}"
done;set +x

I need the output of that

satyarankireddy · 09-23-2019, 03:14 AM

Quote:

Originally Posted by Firerat

try this

Code:

set -x
Output="/path/to/onebigfile.csv"
LogFile="/path/to/somelog.log"
for xls in /path/to/incomingDir/*.xls
do
    unoconv -f csv \
    -e FilterOptions="59,34,76,7" \
    "${xls}" \
    --stdout \
    >> "${Output}"
    printf  "$(date +%F\ %T.%N\ %Z) \"${xls}\" appended to \"${Output}\"\n" >> "${LogFile}"
done;set +x

I need the output of that

getting following error.

line 5:: No such file or directory

I have placed below code into .sh file and executing.

Code which was place in .sh file as follow.

Getting below error now.
++ for xls in '/test/test_amount*.xls'
++ unoconv -f csv -e FilterOptions=59,34,76,7 /test/test_amount1.xls --stdout
unoconv: file `--stdout' does not exist.
unoconv: RuntimeException during import phase:
Office probably died. Unsupported URL <file:///test/--stdout>: "type detection failed"
+++ date '+%F %T.%N %Z'
++ printf '2019-09-23 10:29:36.414894352 CEST "/test/test_amount1.xls" appended to "/test/big_file.csv"\n'

Firerat · 09-23-2019, 03:31 AM

well, set -x should show the expansions
so you didn't run it

copy and paste this

Code:

cat > script.sh <<\EOF
#!/bin/bash
set -x
Output="/test/test_amount.csv"
LogFile="/test/filelist.log"
for xls in /test/test_amount*.xls
do
    unoconv -f csv \
    -e FilterOptions="59,34,76,7" \
    "${xls}" \
    --stdout \
    >> "${Output}"
    printf  "$(date +%F\ %T.%N\ %Z) \"${xls}\" appended to \"${Output}\"\n" >> "${LogFile}"
done
set +x
EOF

and run it with

Code:

bash script.sh

satyarankireddy · 09-23-2019, 03:40 AM

Quote:

Originally Posted by Firerat

well, set -x should show the expansions
so you didn't run it

copy and paste this

Code:

cat > script.sh <<\EOF
#!/bin/bash
set -x
Output="/test/test_amount.csv"
LogFile="/test/filelist.log"
for xls in /test/test_amount*.xls
do
    unoconv -f csv \
    -e FilterOptions="59,34,76,7" \
    "${xls}" \
    --stdout \
    >> "${Output}"
    printf  "$(date +%F\ %T.%N\ %Z) \"${xls}\" appended to \"${Output}\"\n" >> "${LogFile}"
done
set +x
EOF

and run it with

Code:

bash script.sh

==================================================================================================== =======

below are the errors during execution:-

[root@xxxxxx test]# bash script.sh
+ Output=/test/test_amount.csv
+ LogFile=/test/filelist.log
+ for xls in '/test/test_amount*.xls'
+ unotest -f csv -e FilterOptions=59,34,76,7 /test/test_amount_2014-W01.xls --stdout
unotest: file `--stdout' does not exist.
unotest: RuntimeException during import phase:
Office probably died. Unsupported URL <file:///test/--stdout>: "type detection failed"
++ date '+%F %T.%N %Z'
+ printf '2019-09-23 10:36:33.491082289 CEST "/test/test_amount_2014-W01.xls" appended to "/test/test_amount.csv"\n'
+ for xls in '/test/test_amount*.xls'
+ unotest -f csv -e FilterOptions=59,34,76,7 /test/test_amount_2014-W02.xls --stdout
unotest: file `--stdout' does not exist.
unotest: RuntimeException during import phase:
Office probably died. Unsupported URL <file:///test/--stdout>: "type detection failed"
++ date '+%F %T.%N %Z'
+ printf '2019-09-23 10:36:33.663234234 CEST "/test/test_amount_2014-W02.xls" appended to "/test/test_amount.csv"\n'
+ for xls in '/test/test_amount*.xls'
+ unotest -f csv -e FilterOptions=59,34,76,7 /test/test_amount_2014-W03.xls --stdout
unotest: file `--stdout' does not exist.
unotest: RuntimeException during import phase:
Office probably died. Unsupported URL <file:///test/--stdout>: "type detection failed"
++ date '+%F %T.%N %Z'

Firerat · 09-23-2019, 03:42 AM

what is unotest ?

satyarankireddy · 09-23-2019, 04:48 AM

Quote:

Originally Posted by Firerat

what is unotest ?

It is throwing an error like that, I don't see anything in code. Below code, as it is present in script.sh file

#!/bin/bash
set -x
Output="/test/test_amount.csv"
LogFile="/test/filelist.log"
for xls in /test/test_amount*.xls
do
unoconv -f csv \
-e FilterOptions="59,34,76,7" \
"${xls}" \
--stdout \
>> "${Output}"
printf "$(date +%F\ %T.%N\ %Z) "${xls}" appended to "${Output}"\n" >> "${LogFile}"
done
set +x

Firerat · 09-23-2019, 05:43 AM

from the help you posted earlier I see --stdout

the outpot you posted had unotest it, which isn't in the script

hmm, maybe unoconv cares about the order

Code:

#!/bin/bash
Output="/test/test_amount.csv"
LogFile="/test/filelist.log"
for xls in /test/test_amount*.xls
do
    unoconv -f csv \
    -e FilterOptions="59,34,76,7" \
    --stdout \
    "${xls}" \
    >> "${Output}"
    printf  "$(date +%F\ %T.%N\ %Z) \"${xls}\" appended to \"${Output}\"\n" >> "${LogFile}"
done

satyarankireddy · 09-23-2019, 07:13 AM

Quote:

Originally Posted by Firerat

from the help you posted earlier I see --stdout

the outpot you posted had unotest it, which isn't in the script

hmm, maybe unoconv cares about the order

Code:

#!/bin/bash
Output="/test/test_amount.csv"
LogFile="/test/filelist.log"
for xls in /test/test_amount*.xls
do
    unoconv -f csv \
    -e FilterOptions="59,34,76,7" \
    --stdout \
    "${xls}" \
    >> "${Output}"
    printf  "$(date +%F\ %T.%N\ %Z) \"${xls}\" appended to \"${Output}\"\n" >> "${LogFile}"
done

The above code is working but it does not skip the first 6 rows for each file. It was converted entire data fro all files into CSV.

Firerat · 09-23-2019, 07:26 AM

no need to keep quoting
and
[code]<-- paste your output between these -->[/code]

Code:

#!/bin/bash
Output="/test/test_amount.csv"
LogFile="/test/filelist.log"
files=(/test/test_amount*.xls)
for xls in "${files[@]}"
do
    for row in 0 7
    do
        unoconv -f csv \
        -e FilterOptions="59,34,76,${row}" \
        --stdout \
        "${xls}" \
        >> "${Output%.csv}_startrow_${row}.csv"
        Outs+=("${Output%.csv}_startrow_${row}.csv")
    done
done
wc -l "${Outs[@]}"