File

The File tab allows you work with a CSV (delimited text file) and the database without having to load the CSV into the database. This tab has two areas that functionality: CSV Update and Database Update.

CSV Update

CSV Update allows you to add additional columns to a CSV (or other delimited file) using a Select statement, including binding CSV column values to the Select statement.


Figure 1 - File Tab, Update CSV Function

This screen takes an input file from which input values be passed to a select statement. The input file name and location can be typed into the Input File field or the file can be chosen by clicking on the "..." button on the right hand side of the input field.

For each line in the input file that same line will be written to the output file along with the result of the select statement. Values from the input file can be bound into the select statement to individually tailor each result.

For the input file the user must specify whether the file contains a header record and what the delimiter is: a comma, a tab or another specified value. Once this information has been specified it is possible to preview the contents of the input file by clicking on the "Preview CSV" button (see Figure 2). This loads in only a few lines of the csv to view.


Figure 2 - File Preview

Next is the Output file information. This specifies the file that the combined values of the input file and the Sql statement will be written to. As well as the output file name you also need to specify if you want to save a header record and what the delimiter will be.

Under the Output File parameters is the Sql Editor. When in Update CSV mode this is for a Select statement only. The select part of the Select statement is what is appended to the input file line when it is written to the output file. This Sql Editor also has two buttons that provide the Describe and the Sql History functions from the main Sql Editor tab.

Values from the input file can be bound into the select statement by specifying a : followed by a number, i.e. :1 binds in the first column, :6 binds in the sixth column, etc. The bound values can appear anywhere within the Select statement.

Under the Sql Editor is a "Run as Background Job" check box. Checking this will make the job run in the background so that you can continue to use Databrid as the processing is carried out.

At the very bottom are the Execute Query and Test Query buttons. Test Query will test that the query in the Sql Editor is syntactically correct and that the binds relate to input file columns that exist. Execute Query will start to process the Input File and Select statement to produce the Output File.

Example

To illustrate how to use the Update CSV function I will use an employee example where we have a CSV containing the following data (comma delimited file with the employee number and department number). The file (shown below) is "c:\employee_number.csv".

EMPNO,DEPTNO

7369,20

7499,30

7521,30

7566,20

7654,30

7698,30

7782,10

7788,20

7839,10

7844,30

7876,20

7900,30

7902,20

7934,10

The following parameters are entered into the screen:

Input File:

c:\employee_number.csv

Input File Contains Header:

Checked

Input Delimiter:

Comma

Output File:

c:\employee_dept.csv

Create Header Record on Output File:

Checked

Output Delimiter:

Comma

Statement:

SELECT dname
FROM dept
WHERE deptno = :2

Run As Background Job:

Checked

When the Execute Query button is pressed each line in the input file (except the header) is run against the Sql Statement. For example when processing the first row of data the Sql statement will have the value 20 bound in and RESEARCH returned, the second row will bind in 30 and return SALES, the third row will bind in 30 and return SALES, etc. The output of this will be written to the file c:\employee_dept.csv (shown below).

EMPNO,DEPTNO,DNAME

7369,20,RESEARCH

7499,30,SALES

7521,30,SALES

7566,20,RESEARCH

7654,30,SALES

7698,30,SALES

7782,10,ACCOUNTING

7788,20,RESEARCH

7839,10,ACCOUNTING

7844,30,SALES

7876,20,RESEARCH

7900,30,SALES

7902,20,RESEARCH

7934,10,ACCOUNTING

Database Update

Database Update allows you to update the database using insert, updates, deletes or PLSql by binding the CSV column values into your statement.


Figure 3 - File Tab, Update Database Function

For database updates you only need to specify an input file (with file attributes) because no output will be created during the processing. The input file name and location can be typed into the Input File field or the file can be chosen by clicking on the "..." button on the right hand side of the input field. The Input File attributes of "Input File Contains Header Record" and "Delimiter" must be specified.

The Sql Editor can be used with any Sql or DDL statement as long as no output is generated (as a select statement does). This Sql Editor also has two buttons that provide the Describe and the Sql History functions from the main Sql Editor tab.

Below the Sql editor is the Run As Background Job check box, to make processing occur in the background, and the Commit During Processing check box. If the commit check box is checked then a commit will occur during processing at an interval specified in the Rows Processed Before Commit field. WARNING: Any commits that occur here will also commit work done other tabs, like the Sql Editor tab.

At the very bottom are the Execute Modify and Text Modify buttons. Test Modify will test that the query in the Sql Editor is syntactically correct and that the binds relate to input file columns that exist. The Sql syntax will only be checked for insert, update, delete and PLSQL blocks. Execute Query will start to process the Input File and Select statement to produce the Output File.

Example

To illustrate how to use the Update Database function I will use an employee example where we have a CSV containing the following data (comma delimited file with the employee number and salary increase). The file (shown below) is "c:\employee_increase.csv".

EMPNO,INCREASE

7369,2000

7499,1000

7521,3000

7566,2500

7654,3000

7698,3000

7782,2000

7788,2000

7839,1000

7844,1500

7876,2500

7900,3500

7902,2000

7934,1000

The following parameters are entered into the screen:

Input File:

c:\employee_increase.csv

Input File Contains Header:

Checked

Input Delimiter:

Comma

Statement:

UPDATE emp
SET salary = salary + :2
WHERE empno = :1

Run As Background Job:

Checked

Commit During Processing

Unchecked

When the Execute Modify button is pressed each line in the input file (except the header) is run against the update Statement. For example when processing the first row of data with the update statement will have the salary increased by 2000 for employee 7369, the second row will increase employee 7499s salary by 1000, etc.