How to retrive data from DB(Aqua studio) in CVS format using UNIX script? Post: 302780013

Sponsored Content

Top Forums UNIX for Dummies Questions & Answers How to retrive data from DB(Aqua studio) in CVS format using UNIX script? Post 302780013 by DGPickett on Wednesday 13th of March 2013 04:17:48 PM

03-13-2013

Registered User

The first queston in csv solutions is "Does the data include char with comma or double quotes?"

If not, it never needs quoting or escapes.
In csv, embedded commas must be double-quoted and embedded double quotes must be doubled! For example, if a column contained ' We said "Hello, sailor!" ', then it needs to be ' "We said ""Hello, sailor!"" ' or even ' We said ""Hello"," sailor!"" ' (double quotes right around the comma)! Many double quote every column against commas, but most forget double quote doubling! In a C/C++/JAVA/PERL program, you can detect comma and only quote on need, whole column in case it has multiple commas, for a minimum file size. CSV is very compressible, too.

You can select into excel with odbc and save as csv!

You can select with any command line tool (isql from unixODBC, jisql for xigole w/JDBC) and use sed or awk to reformat it trimmed with comma separators.

If the tool has a column separator, you can set it to comma, or convert it to comma.

You can put the commas in your select: select col_a || ',' || col_b .... Sometimes the concatenated string exceeds the allowed length unless you break it up.

You can put a marker on the data lines so they are simple to detect and separate from headings, error reports and status:

Code:

echo "
 select 'dAtAq,' || col_a || ',' || col_b || ....
" | isql ... | sed -n '
  s/^dAtAq,//p
  t
  w logfile
 ' > xxx.csv

We could process in bash if you insist.

Last edited by DGPickett; 03-13-2013 at 05:31 PM..

DGPickett

View Public Profile for DGPickett

Find all posts by DGPickett

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

help to retrive data from log file

hi i have following file. where i m trying to retrive data on latest date. let us say we are extracting data from this file for Jun 30 where date is highest date in log file. here i want to take output in other file from first line of Jun 30 to the end of file in short i want retrive...

2. Shell Programming and Scripting

to retrive data that appear only once in a file.

hi, I need to get the list of functions that are used more than once in a file list. Thanks in advance

3. Shell Programming and Scripting

Shell script to format a .CSV data

Hi There I needed to write a Unix shell script which will pick up the data from a .CSV file and reformat it as per the requirement and write it to another .CSV file. Currently I am in the proess of Data Import to "Remedy System" (A one kind of incident mangement Application) and this...

4. UNIX for Advanced & Expert Users

shell script to format .CSV data

Hi all, I have written a shell script to search a specified directory (e.g. /home/user) for a list of specific words (shown as ${TMPDIR}/wordlist below). The script works well enough, but I was wondering if there was a way to display the line number that the word is found on? Thanks! cat...

5. Shell Programming and Scripting

Need script to format data specifically

Hi all, I need a script specially using loops to print below output... variables x, a and b will be read from user... x a b x+1 a b+1 x+2 a b+2 x+3 a+1 b x+4 a+1 b+1 x+5 a+1 b+2 for example.... 1 ...

6. Shell Programming and Scripting

Converting windows format file to unix format using script

Hi, I am having couple of files which i used to copy from windows to Linux, so now in case of text files (CTRL^M) appears at end of line. I know i can convert this windows format file to unix format file by running dos2unix. My requirement here is that i want to do it automatically using a...

7. Programming

updating data in cvs file using c programming

hi every one i want to read and write data from cvs file using c program. but my problem is that at run time my data is increasing in both row wise and column wise. that means it is continuously updating in both direction. is there any way through which i can find the next colum or row for eg...

8. Shell Programming and Scripting

Perl -- Script to re-format data

Hi, I have a file with data in the following format BOX -1.000000 -1.000000 0.000000 30.00000 14.00000 0.1000000 0.000000 0.000000 0.000000 0.000000 0.000000 CYLINDER 3.595000 2.995000 0.000000 0.5100000 2.000000 Z 0.000000 0.000000 0.000000 I want to convert these files...

9. Shell Programming and Scripting

Script to generate Excel file or to SQL output data to Excel format/tabular format

Hi , i am generating some data by firing sql query with connecting to the database by my solaris box. The below one should be the header line of my excel ,here its coming in separate row. TO_CHAR(C. CURR_EMP_NO ---------- --------------- LST_NM...

LEARN ABOUT CENTOS

iusql

isql(1) 						       UnixODBC manual pages							   isql(1)

NAME

       isql, iusql - unixODBC command-line interactive SQL tool

SYNOPSIS

       isql DSN [USER [PASSWORD]] [options]

DESCRIPTION

       isql  is  a  command  line  tool which allows the user to execute SQL in batch or interactively. It has some interesting options such as an
       option to generate output wrapped in an HTML table.

       iusql is the same tool with built-in Unicode support.

ARGUMENTS

       DSN    The Data Source Name, which should be used to make connection to the database.  The data source is looked for in	the  /etc/odbc.ini
	      and $HOME/.odbc.ini files in that order, with the latter overwriting the former.

       USER   Specifies the database user/role under which the connection should be made.

       PASSWORD
	      Password for the specified USER.

OPTIONS

       -b     Run isql in non-interactive batch mode. In this mode, the isql processes its standard input, expecting one SQL command per line.

       -dDELIMITER
	      Delimits columns with delimiter.

       -xHEX  Delimits	columns with HEX, which is a hexadecimal code of the delimiting character in the format 0xNN - i.e. 0x09 for the TAB char-
	      acter.

       -w     Format the result as HTML table.

       -c     Output the names of the columns on the first row. Has any effect only with the -d or -x options.

       -mNUM  Limit the column display width to NUM characters.

       -lLOCALE
	      Sets locale to LOCALE.

       -q     Wrap the character fields in double quotes.

       -3     Use the ODBC 3 calls.

       -n     Use the newline processing.

       -e     Use the SQLExecDirect instead of Prepare.

       -k     Use SQLDriverConnect.

       -v     Turn on the verbose mode, where the errors are fully described. Useful for debugging.

       --version
	      Prints the program version and exits.

COMMANDS

       This section briefly describes some isql runtime commands.

       help
	      List all tables in the database.

       help table
	      List all columns in the table.

       help help
	      List all help options.

EXAMPLES

       $ isql WebDB MyID MyPWD -w -b < My.sql

       Connects to the WebDB as user MyID with password MyPWD, then execute the commands in the My.sql file and returns  the  results  wrapped	in
       HTML table.  Each line in My.sql must contain exactly 1 SQL command, except for the last line, which must be blank (unless the -n option is
       specified).

FILES

       /etc/odbc.ini
	      System-wide DSN definitions. See odbc.ini(5) for details.

       $HOME/.odbc.ini
	      User-specific DSN definitions. See odbc.ini(5) for details.

SEE ALSO

       odbcinst(1), odbc.ini(5)

AUTHORS

       The authors of unixODBC are Peter Harvey <pharvey@codebydesign.com> and Nick Gorham <nick@easysoft.com>. For the full list of  contributors
       see the AUTHORS file.

COPYRIGHT

       unixODBC is licensed under the GNU Lesser General Public License. For details about the license, see the COPYING file.

version 2.3.1							  Tue 25 Jun 2013							   isql(1)