file2db utility

file2db is command line program intended for loading files into the database.

There are two versions available: file2db_ib for Interbase and Firebird and file2db_ado for other SQL database servers. You need Interbase client library (gds32.dll) installed or ADO database driver for you database installed on your system.

To load list of text strings specify field (attribute) delimiter and list of attributes:

file2db_ib -L <delimiter> -f <fields>

To load file entirely into BLOB field, specify options: name of BLOB field and optionally field for keeping name of loaded file and MIME type of the file.

file2db -N -u <URL> -c <content1>{,<content2>} -y < content MIME type>

In –c option list one or more fields delimited by colon.

Reverse operation is extraction file from the BLOB fields ( -X command).

To specify database connection you can use –s option or combination of -h -@ -k -# -! and –w options.

In first case (-s option) you can pass connection string thru one global environment variable.

Otherwise you can at least specify database path, database user name such SYSDBA, password (masterkey if you forgot) (- h < DB > -@ < USER > - k < PWD >).

Optionally you can set SQL dialect, role and connection character set code page (-# < DIALECT > -! <ROLE> -w <CODEPAGE> ).

There are three commands:

- L – loading lists;

- N – loadind files into BLOBs;

- X – extracting BLOBs to the files.

You must provide database connection and specify table name (- t < table >).

Other options controls loading and extraction.

Delimiter option –L .

- l < column delimiter type >

Available types –

Also it is possible set any symbol:

All examples shows CRLF control characters sequence. <CR><LF>

By default tab is used.

Option -0 inserts NULL if data is not available (text string is empty)

-0

By default -0 is off.

Key cipher option –N

Data can be encrypted and decrypted using Blowfish algorithm (Bruce Schneier, 1994). For both cipher -N and decipher -X options -j option must be provided:

-j < cipher key>

By default cipher key is ‘NONE’

Each field you want to be encrypted or decrypted must be entered in uppercase–

-n -U <URL> -C <content1>{,<content2>} -Y < content MIME type> - field values 'll be encrypted

-n -u <URL> -c <content1>{,<content2>} -y < content MIME type> - field values 'll NOT be encrypted

Only –u , -c and -y (or –U , -C and -Y ) option values are case sensitive.

Loaded files (in -L and –N commands) can be listed with file mask symbols '*' and '?'.

By default files from the nested directories is not loaded. You myst set –r option to allow recurse subdiretories.

Note that ‘\’ character used in DOS/Windows file path 'll be replaced with '/' character .

If directory name is specified, by default files with file name extension of

.wml .htm .html .xhtm. xhtml .oeb .pkg .txn .xml .txt .gif .png .jpg .jpeg .tif .tiff .wav .css .xls

will be loaded. If you want load all files from the directories, specify –a option.

Option –y (–Y) set table attribute where MIME type must be stored. If file2db can not determine MIME type of the file, text/html MIME type is used. To override default MIME type use –z option.

For -L command -2 < line number > option sets line number (starting with 1) from which data must be loaded. It is useful in case when text file starting with header- in this case use option value -2 2. By default value of this option is 1.

fil2db process Excel worksheet files if Microsoft Office is installed (at least of 2000 version or later). -1 < line number > set row number where table fields (attributes) are listed. By default value of this option is 1.

Excel worksheet consists of one or more sheets corresponing to the different tables. Name of each sheet MUST be the same as database table name.

Loading data from the Excel therefore are a little bit tricky. You must remove all empty sheets, and correctly named all other sheets.

Loading data from the Excel is intended especially fo avoiding –f option, because corresponing attributes are listed in the sheet. Note you must set -1 and -2 options both.

Excel table can contain columns with data with no data to be loaded. file2db first find out row with field list by -1 option value and then set left and right limits from which data 'll be loaded.

By default BLOB data extracted into the current directory (–X command). You can set output directory by -o option.

file2db provides two options for file name manipulation stored in the database. You can add suffix to the file name by -u option. You can remove prefix from the file name by -m option and add new prefix by-q option.

Often clear data from the database is required before loading data. For Interbase/Firebird you can use isql command line utility to do. file2db use –i option to clear data from the table (–N и -L commands) BEFORE loading or AFTER extraction (-X command).

Passing parameters is possible thru QUERY_STRING environment variable. This feature is useful when file2db is called from the web (GET method) using –g option or reading command line from the stdin file using –p opton (POST method). In both cases all other options passed as url-encoded like x=&o=path

file2db uses initialization file file2db.ini in the folder where file2db is located. You can create this file to store options such database connection.

Opton –d supress loading intialization file.

Example 1

Load each line from the customer.txt CSV text file to the customer table:

SET DB=localhost:\src\aims\db\aims.fdb

SET CP=WIN1251

SET USER=SYSDBA

SET PWD=masterkey

SET ROLE=

SET DIALECT=3

SET DB_OPTS=-h "%DB%" -@ "%USER%" -k "%PWD%" -w "%CP%" -! "%ROLE%" -# %DIALECT%

SET F_CUSTOMER=CNO,CNAME,CINN,CMODIFIER,CCREATED

file2db_ib -L "|" %DB_OPTS% -v -r -i -t customer -f %F_CUSTOMER% customer.txt

Example 2

Load from the Excel worksheet file customer.xls to the customer table:

file2db_ib -L "|" %DB_OPTS% -v -r -i -t customer customer.xls -1 1 -2 2

Using file2db you can customize view of some application downloaded from the commandus software development group web site. It is recommended make a backup copy of the database before changes are made.

To see list of commands an available options, execute file2db without parameters:

Usage: file2db _ib -[?|e|a|x] [Options] File(s)|Mask|Path
File list: e.g. file1.txt ..\file2.xml
File mask can include willcards e.g. *.htm
Commands:
e - register program -u "user name" -t "registration code"
l <delimiter> - load delimited text. Use f to list fields
n <column delimiter> - add content files to database. #9 []
x - extract files from database
Options: ? - this screen
s <database connection string file>
or -h <DB> -@ <USER> -k <PWD> -# <DIALECT> -! <ROLE> -w <CODEPAGE>
t <table> - table name
f <field list> - comma separated list e.g. fld1,"Fld 2"
u <column name> - url column. Use U to cipher
c <column1>{,<column2>} - content column(s). Use C to cipher column
y <column name> - content type column. Use Y to cipher
m <prefix> - remove file name prefix
q <prefix> - add file name prefix
z <mime type> - default MIME type(default text/html)
i - clear table before add or after extract
j <cipher> - cipher key
o <folder> - extract database to folder
v - verbose
r - recurse subfolders
a - any files, otherwise:.wml.htm.xhtm.xhtml.oeb.pkg.txn.xml.txt.gif.png.jpg.jpeg.tif.tiff.wav.css.xls
g - get command line options from QUERY_STRING environment variable <GET>
p - get command line options from input <POST>
1 <1..n> - excel column list (field names row number) [1]
2 <1..n> - first data row [1]. Excel: 1,2,3.. Text: 1|2
9 <record delimiter>. [#13#10, ^M^J, #$D#$A]
0 - make NULL
d - skip default settings (don't load file2db.ini)

file2db Copyright © 2005 Andrei Ivanov. All rights reserved.