Store a column from an excel sheet (exported to txt) into a variable
I typically pull a bunch of data via SQL that lists a bunch of users and the server on which they want to access, as well as other attributes, in one row of an excel sheet and the number of rows is directly proportionate to the number of users.
I'm trying to write a loop to read each line of the excel sheet (exported to a text file), store the column containing the server name into a variable called HOST, and the column containing the username into a variable called HUMAN_ID. Obviously I'm not getting the desired result.
The excel export to text puts all columns separated by quotes. I can get the column I want by doing the following:
Ok, that's fine - so now I want to write a loop and store that value into HUMAN_ID for each iteration so it can be run in conjunction with the HOST variable. I'm doing this and adding the "echo $HUMAN_ID" to test that this is working:
It appears to be printing all the other cell contents before column 18 at each iteration. The excel sheet may be inserting control characters like a carriage return and I can look into that but I'm puzzled because the basic cat / awk works fine.
I eventually want to write a loop doing:
Connect to the value stored in HOST at the time of iteration, grep the /etc/passwd file for the value stored in HUMAN_ID at the time of the iteration, and append the results to a log file for viewing until the value of HUMAN_ID is null (end of the file).
That's a useless use of cat and dangerous use of backticks. the for-loop is splitting on spaces, not just lines, which ruins your data. Also, Never use external utilities to process individual lines if you can possibly help it; externals are fast, but also have a fixed cost for loading and quitting time. Running them repeatedly for small quantities of data is like making 9,000 phone calls to say 9,000 words.Shell has a way to do all of these things natively without using awk or cat, which will be hundreds of times faster.
I'll take a look at the links you provided but I got it to work using the aforementioned methods you advised not to use. Appreciate your suggestion in any event.
Hi Pals,
I have a file which contains a set of similar lines, as follows:
Remedy Support Action Triggered by Incident Modification of type: ARA username2 ########## ARA|INC0000178532 INC0000178532 0000000019879 000000000038372
Remedy... (6 Replies)
Hi Folks,
Can you please advise for any script in unix such that for example , i have 3 different excel sheet at the location /ppt/gfr/exc so the name s of the excel sheet are
1excel.xslx
2excel.xslx
3excel.xslx
now in these 3 different excel sheet there is lot of data for example each... (3 Replies)
Hi folks,
I have a requirement in perl to print all the hyperlink from the spreadsheet(xlsx).
Spreadsheet contains few lines of hyperlink data (pic attached).
P.S. Hyperlink is behind the data and not visible in excel sheet directly.
Now using perl script I need to copy the hyperlinks in... (3 Replies)
I am new to scripting/programming, so I apologize for any novice questions.
I have a tab delimited text file that was saved from excel xls file.
I am trying to select only the third column using awk command. My command line is as below:
cat test.txt | awk '{print $3}'
However, above... (8 Replies)
Hello guys
I need to run a script to remove the last column of different comma separated files.
The problem is that the number of columns of my files will be different and I won't know that number every time i run my script.
Is there any command I can use to remove the last column without... (7 Replies)
Hello,
I have an information of about 100 odd file names which i have in my server. I need to attach this information in an excel sheet and sent to the concerned team.
I use uuencode for attaching a text file. But how will i have all the information in attachment. Please assist. Is there... (1 Reply)
Hi,
I need to automate a script.
The process in the script is as follows
Running the script abc.ksh
The report is stored in xyz (flat file)
Need to store the flat file content in an excel sheet.
Your reply is highly appreciated.
Thanks,
Karthick (2 Replies)
I have 3 columns in an excel sheet.
c1 c2 c3
EIP_ACCOUNT SMALL_TS_01 select A.* from acc;
All the above 3 col shoud be passed a variable in the unix code.
1.How to read an excel file
2.How to pass these data as variable to the unic script (1 Reply)
Dear Members,
How do I create an eqvivalant of Excel sheet in Hp-UX.
Is there any application like the Microsoft Excel on HP-UX.
How do I invoke it.
Regards,
PrasadKVS (5 Replies)