Question Locked

Required to read only the column with a specific header of a CSV file

0 helpful
10 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi all
I have to read only the values from a specific column header of a CSV file using UNIX shell scripting (say the header will be Asset ID), I have the CSV file already saved in the UNIX server and am trying to extract data only from the column to be used for another application, I am novice in the UNIX shell scripting, can someone help me on this regards
Thanks

10 replies

by older mycroft

As far as I am aware, CSV files do not contain any column headers at all - just the columns. So if you know where the CSV file came from, you can figure out which column you are searching for.

If there are 6 columns and you want to find the values of column #2, then the values you are searching for are contained in the Comma Separated Value #2, #8, #14, #20 etc (each value being 6 away from the one before).

A CSV file is simply a continuous series of values separated by commas, intended to be fed into the same format as they were extracted from.

by nrshan

Mycroft,
Thanks for the reply, actually users generate this CSV file from a excel spread sheet, they have column names on the first row followed by values, but while saving they save it as a CSV, now I have this file in the UNIX environment and I would like to read a specific column with the header like Asset ID, how can this be achieved.
Thanks

by nrshan

Thumsup,
Actually that's the goal, but we don't require all the data in the CSV file, we only require one column out of it, if I import this to a table and then call the data from the table again to populate my transaction data then it's going to be a burden to the whole workflow, I beleive there is something like cut operation in UNIX which can be used but am not sure how thats handled.
Thanks

by older mycroft

Why not create the solution ?

So instead of farting about with code you neither know nor understand, why don't you export a different CSV file from the original database - containing only what you need?

Unless this is an elaborate attempt at leeching the answer to a homework question? ;)

< ? ;) ?>

by nrshan

Mycroft
The CSV values are entered by the user manually, they are not available in any database unless I import them to be available, tht's the issue here :)
Thanks

by thumbsup2

.... short of writing your own program to do it for you, that will be able to pull one certain 'column' of data out of a CSV file. In CSV format, columns don't exist. If you want to see what the data looks like, open the file with a simple text editor (whatever the UNIX equivalent of Notepad is) and see for yourself.

So, since you're trying to 'script' this, I guess you'll need to learn how to parse the data.

by older mycroft

Well don't try to WALK, try to HOP instead.

[i]- "Oh I can't do that because my left leg is missing!"[/i]

I'm sorry for the stupid analogy, but your 'predicament' makes as much sense to me. ;)

by Former member

This might be of use to you. :)

A useful application of the RISQL Entry Tool or RISQL Reporter is to extract selected data from a database and place that data into a file for transfer to another program. You can also use a pipe (|) to send row data output directly from the RISQL Entry Tool or RISQL Reporter to another program, such as the Table Management Utility (TMU).

When using the RISQL Entry Tool or RISQL Reporter in data extraction applications, you typically want to restrict data output to only query result rows, omitting the product copyright notice, column headings, blank lines, and so on. Quiet mode restricts output to query results only.
Redirecting extraction results from the shell

You can perform data extractions at the operating-system level directly from the UNIX or Windows shell (for example, in a shell script).

When you perform data extraction from the shell by specifying quiet mode (-q tool-startup option), all nonerror output from the program is redirected to a file or pipe with standard UNIX or Windows file-redirection commands. Using the -q option also changes the default setting of the following statements:

* SET ECHO is set to OFF.
* SET ROW DATA is set to ONLY.

Examples

Use quiet mode (-q command-line option) to extract only query result rows to a file as shown below:

$ risql -q username password < weekly_extract > excel_import

To pipe extracted data to another program, such as the TMU, enter:

$ risql -q username password < weekly_extract | rb_tmu load.tmu
username password

Redirecting extraction results from within the tool

When used together, the following statements capture query results in a file for transfer to another program during a RISQL Entry Tool or RISQL Reporter session:

* SET ROW DATA ONLY causes the query result columns to be returned without column headings or other extraneous information.
* SET ECHO OFF suppresses the RISQL prompt and input commands, as described on Including input in output.
* SET OUTPUT filename sends row data output to a file instead of the terminal (standard output), as described on Saving or discarding output.

Example

To send only the row data for the specified period to a file named weekly_extract, enter:

RISQL> set row data only;
RISQL> set echo off;
RISQL> set output weekly_extract;
RISQL> select *
> from period join sales
> on period.perkey = sales.perkey
and qtr = 'Q1_99';

Exit status codes

The RISQL Entry Tool and RISQL Reporter use the following exit status codes:

Status Code > Meaning
0 > Information or statistics messages might have been issued during execution, but no warning, error, or fatal messages were issued.
1 > Warning messages cause an exit status of 1 to be returned.
2 > Error messages cause an exit status of 2 to be returned.
3 > Fatal messages cause an exit status of 3 to be returned.
The RISQL Entry Tool and RISQL Reporter return the highest status code encountered during processing. For example, if the RISQL Entry Tool generates only informational messages, it will return an exit status code of 0; if, however, it generates both information messages and a fatal message, it will return an exit status code of 3.

Formatting reports

This chapter describes report-formatting features included with the RISQL Reporter. These features are not available with the RISQL Entry Tool.
http://publib.boulder.ibm.com/infocenter/rbhelp/v6r3/index.jsp?topic=/com.ibm.redbrick.doc6.3/risql/risql32.htm

Please post back if you have any more problems or questions.
If this information is useful, please mark as helpful. Thanks.

This discussion is closed to new replies.