odbc_get

Commands ››
Parent Previous Next

The odbc_get command is used to extract data in CSV format from a database. The database must have a suitable ODBC driver installed on your system in order for this command to work.

This command is only available in the Windows version of CSVfix.

Flag

Req'd?

Description

-cs constr

No

Specifies an ODBC connection string to use to connect to the database. The exact format of the connection string is driver specific, but the following are common settings:

DRIVER = ODBC driver name
SERVER = name (or IP address) of machine hosting SQL database
DATABASE = SQL database name
UID = database user id
PWD = database password

Fields are separated by semicolons - see below for an example connection string.

Note that one of -cs or -dir must be specified, but not both.

-dir dirname

No

Uses the ODBC text file driver and specifies dirname as the default directory to look for files. Using the text driver means you can perform SQL queries on CSV files in that directory. To do this, the files must have the extension .CSV and must contain an initial filed-name record.

-sql stmt

No

A SQL statement that will be used to extract data from the database. If the stmt string begins with the '@' character it is treated as the name of a text file that contains the SQL statement to execute.

Note that no check is made that the statement is a SELECT - it is therefore possible to use this command to execute any SQL, with possibly damaging effects.

You must specify one of -sql or -tbl, but not both.

-tbl table

No

Extract the named table or view as CSV. This is a shorthand for doing a SELECT * FROM tbl on the named table or view.

-ns nullstr

No

Specifies the string used to represent NULL data. By default, this is the empty string.

-h

No

Use SQL column names as CSV field header record on output.



The following example extracts data from the jobs table of the uses Microsoft SQL Server pubs database: running on the dbs server:

csvfix odbc_get -cs "server=dbs;driver=sql server;database=pubs;uid=sa;pwd=" -tbl jobs

which produces:

"1","New Hire - Job not specified","10","10"
"2","Chief Executive Officer","200","250"
"3","Business Operations Manager","175","225"
"4","Chief Financial Officer","175","250"
"5","Publisher","150","250"
...


This example uses the -dir option to allow SQL queries to be performed on the files in the tests/data directory, using the Windows ODBC text driver. In this case, the army.csv file is being queried:


csvfix odbc_get -dir tests/data -sql 'select rank, name from army.csv order by rank'


which produces the output:


"maj","black"

"maj","smith"

"pvt","pink"

"pvt","white"

"sgt","jones"






Created with the Personal Edition of HelpNDoc: Single source CHM, PDF, DOC and HTML Help creation