Link to home
Start Free TrialLog in
Avatar of brake4blonds
brake4blondsFlag for United States of America

asked on

aix: read comma delimited file and put into a temp sybase table

I have a test file that I need to take the contents and place into a temp Sybase Table.  
From the command line I am able to read the file and echo it to the screen; however, I can't get it loaded into the temp table.  I am doing this in a procedure

For my example, it is assumed that the is present and read.

File contents example is like this

123456789,Apr 7 2010 9:22:41:800AM
234567891,Apr 8 2010 9:22:41:800AM
etc....

Here is what I have so far:
cat > ${SQL_SCR} << END_OF_PROC
...
...
...
while read file

do
echo "field1 = " `echo $file | cut -d, -f1`
echo "field2 = " `echo $file | cut -d, -f2`
 
insert  into TEMP_TABLE values (${field1},${field2})
done  < $CLR_FILE

END_OF_PROC

I am unable to get the data into the table.  What am I doing wrong.

I am getting the following error mesage:

Incorrect syntax near the keyword 'read'.
 000009 Msg 102, Level 15, State 1:
 000010 Server 'TEST', Line 8:
 000011 Incorrect syntax near ','.
Avatar of wilcoxon
wilcoxon
Flag of United States of America image

If you want to use shell scripting, I'd do something like this...

There's no reason to parse out a file like you have (that I can tell from the info given anyway).

I think the problem in your code is not enclosing ${field2} in quotes in the insert.
#!/bin/bash

# assuming it is a #temp table (rather than a permanent "temp" table)
echo 'create table TEMP_TABLE (field1 integer,field2 datetime) go' | isql -S $SERVER -D $DATABASE -U $USER -P $PASS
# -d might be -t - I always forget which
bcp -c -d , -S $SERVER -D $DATABASE -U $USER -P $PASS file_to_insert

Open in new window

Alternately, I'd do this as a perl script...
#!/usr/local/bin/perl
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect('dbi:Sybase:$DATABASE@$SERVER', $USER, $PASS, { RaiseError => 1, AutoCommit => 1 }) or die $DBI::errstr;
open IN, 'file_to_insert' or die "could not open file: $!";
# uncomment this line to create temp table if needed
# $dbh->do("create table TEMP_TABLE (field1 integer, field2 datetime)") or die $dbh->errstr;
while (<IN>) {
    chomp;
    my (@vals) = m{^(\d+),(.*)$};
    $vals[1] = $dbh->quote($vals[1]);
    $dbh->do("insert TEMP_TABLE values ($vals[0], $vals[1])") or die $dbh->errstr;
}

Open in new window

Avatar of brake4blonds

ASKER

ok, maybe I am just a little slow but here is more of the code:

cat > ${SQL_SCR} << END_OF_PROC

BEGIN
      truncate table TEMP_TABLE
      drop table TEMP_TABLE
END
go

Create table TEMP_TABLE (
REF_ID              char(09),
DATE_TIME           char(29))
go

select 'Creating Procedure TEMP_TABLE Started at ', getdate()
go

while read file

do
echo "field1 = " `echo $file | cut -d, -f1`
echo "field2 = " `echo $file | cut -d, -f2`
 
insert  into TEMP_TABLE values (${field1},${field2})

done  < $CLR_FILE

END_OF_PROC

isql -S${Server} -D${Database} -U${UserId} -P${Password} -i${SQL_SCR}A -o${SQL_LOG} 2>${SQL_ERR}


it works fine until the read statement.  I enclosed the values in the insert statement and that work either...any other suggestions>
I took another look at the error message you're getting and it looks like the while read is being included in $SQL_SCR directly rather than executed.  I think this will fix the issue (it should at least change what the error is).
cat > ${SQL_SCR} << END_OF_PROC

BEGIN
      truncate table TEMP_TABLE
      drop table TEMP_TABLE
END
go

Create table TEMP_TABLE (
REF_ID              char(09),
DATE_TIME           char(29))
go

select 'Creating Procedure TEMP_TABLE Started at ', getdate()
go

END_OF_PROC

while read file

do
echo "field1 = " `echo $file | cut -d, -f1`
echo "field2 = " `echo $file | cut -d, -f2`
 
echo "insert  into TEMP_TABLE values (${field1},'${field2}')" >> $SQL_SCR
echo go >> $SQL_SCR

done  < $CLR_FILE

isql -S${Server} -D${Database} -U${UserId} -P${Password} -i${SQL_SCR}A -o${SQL_LOG} 2>${SQL_ERR}

Open in new window

I just noticed REF_ID is also a char field so ${field1} should also be quoted in the insert.
Niether of the suggestions worked...still has a problem with the read statement...
In that case, I'd try this as an alternate version...
cat > ${SQL_SCR} << END_OF_PROC

BEGIN
      truncate table TEMP_TABLE
      drop table TEMP_TABLE
END
go

Create table TEMP_TABLE (
REF_ID              char(09),
DATE_TIME           char(29))
go

select 'Creating Procedure TEMP_TABLE Started at ', getdate()
go

END_OF_PROC

isql -S${Server} -D${Database} -U${UserId} -P${Password} -i${SQL_SCR}A -o${SQL_LOG} 2>${SQL_ERR}
bcp ${Database}..TEMP_TABLE in $CLR_FILE -S${Server} -U${UserId} -P${Password} -c -d,

Open in new window

now I get this error message:

Copy direction must be either 'in' or 'out'.
  Syntax Error in 'TEMP_TABLE'.
  usage: bcp [[database_name.]owner.]table_name[:slice_number] {in | out} datafile
       [-m maxerrors] [-f formatfile] [-e errfile]
       [-F firstrow] [-L lastrow] [-b batchsize]
       [-n] [-c] [-t field_terminator] [-r row_terminator]
       [-U username] [-P password] [-I interfaces_file] [-S server]
       [-a display_charset] [-q datafile_charset] [-z language] [-v]
       [-A packet size] [-J client character set]
       [-T text or image size] [-E] [-g id_start_value] [-N] [-X]
       [-M LabelName LabelValue] [-labeled]
       [-K keytab_file] [-R remote_server_principal]
      [-V [security_options]] [-Z security_mechanism] [-Q]
ASKER CERTIFIED SOLUTION
Avatar of wilcoxon
wilcoxon
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If the bcp statement looks like this it will work

bcp ${Database}..TEMP_TABLE in $CLR_FILE -S ${Server} -U ${UserId} -P ${Password} -c -t,
 
It won't work with the quotes.  Nonetheless, you did resolve my problem.

Thanks a bunch