reading data from excel using shell script

Shell Programming and Scripting
1

Hi all
I am new to shell scripting.

I need to write a shell script that reads each row of an USER_ID colume in a excel file.

the excel has around 10000 rows of data.

Can someone gives me some example or advice what's best way to do this

thanks

2

I would recommend using a perl script to read EXCEL file.

Modules are readily available in cpan.org

Spreadsheet::ParseExcel - Get information from Excel file - search.cpan.org

3

Thanks for your reponse matrixmadhan

But I was told to use SH.

Does anyone know how to use SH to read excel data?

Thanks

4

Who told you?

Excel files are a proprietary binary format not simply CSV files, I strongly suggest you look to see if the Perl solution will work.

Otherwise it would be OLE Automation programming which normally requires Windows.

5

Tiger99,
Is the file CSV or excel (XLS)?

6

Thank you porter for your reponse

If I convert my excel file to csv file, is it possible to read the first column of data in csv file using shell script?

i.e (in my csv file I have)
1234,bob
2345,tom
3345,mary

What I need to accomplish is reading the first column (1234,2345,3345) using shell script and pass the number to a stored procedure for executing a select statement (select * from db where id in (1234,2345,3345 .....))

Any help in this would be really appreciated

7

This can be done with "IFS=," and "while read A B C D E ..."

What database are you using?

8

Hi porter
Should I be storing the data from csv file in a variable?

I use sybase

Thanks

9

I thought you would be reading the data from the separate columns then generating the "insert" SQL statements.

10

IMHO, If your goal is to select the first column out of your csv/xls file, and then use it in a stored procedure, I would suggest that you use awk to extract out the first column into a flat file, load that file into a temp table and then reference the temp table from your stored procedure.

Not sure how many rows you will have in thie csv/xls file, but the more rows you will have, the tricky it will be to pass it into the stored procedure. Look for a more robust solution.

11

Hi porter

sorry, I would not be doing any "insert" SQL statement.

I need to

  1. use shell script to read the first column of data in csv file
  2. pass the data to a sybase stored procedure using shell script
  3. in the stored procedure, I would have a "select" SQL statement (i.e select * from db where id in (1234,2345,....))

1234,2345... are data from the first column of the csv file.

I hope I make my question clear

Thanks

12

this is one way how you get your first column data

# cut -f1 -d"," < csvfile | paste -sd","
1234,2345,3345

you just have to assign the select statement to a variable using the above method

cmd="select * from table where id in ( `the above command` )"
isql -uUser -Sserver <options> << EOF
 exec procedure $cmd
EOF

Since you are beginner to shell, you can read up some tutorial first. see here for some tutorials