Load SQL table from text file Linux command line

Recently I was required to read in a text file into an SQL database table. The pre-requisite was that the fields of the file were an even width, ie, each column was 50 characters wide even if the text filling it was less than 50 characters.

To run the below script, I ftp’d it to a directory on a linux machine. The sql database was on the same machine.

First I wrote the housekeeping function. This set up logs and took care of general good housekeeping practices.

Then I had a function create the table I required.

Then I had a function read from the text file (which just happened to be saved with a .OUT file extension.  Of course, the file which you want to read from should be in the directory as specified in the housekeeing function.

Below are the contents of the shell script. Of course, to run the script the command on linux command line was

./script_name.sh

 

 

 

housekeeping() {

work=/vectorwise/rawdata/dNiall log=$work/mydirectory/log data=$work/mydirectory/data tmp=$work/mydirectory/tmp my_database=xynt_db my_database_logfile=$log/mydirectory/my_database_sql.log FILE_DELIMITER=$

if [ ! -d $work ]; then      mkdir $work fi

if [ ! -d $log ]; then      mkdir $log fi

if [ ! -d $data ]; then      mkdir $data fi

if [ ! -d $tmp ]; then      mkdir $tmp fi

}

create_table() {

sql $my_database << !! > $my_database_logfile

drop table people \p\g

create table people (         type char(2),         name char(50),         addr1 char(50),         addr2 char(50),         addr3  char(50),         addr4 char(50) )

\p\g

CALL VECTORWISE( COMBINE ‘people-people’) \p\g !! } load_table_from_file() { sql $my_database << !! > $my_database_logfile copy people (         type = char(2),         name char(50),         addr1 char(50),         addr2 char(50),         addr3  char(50),         addr4 char(50)         nl ) from ‘$work/files/PEOPLE_FILE.OUT’ \p\g !! } #below calls the functions in order housekeeping create_table load_table_from_file