brake4blonds
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 ','.
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 ','.
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;
}
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>
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}
I just noticed REF_ID is also a char field so ${field1} should also be quoted in the insert.
ASKER
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,
ASKER
now I get this error message:
Copy direction must be either 'in' or 'out'.
Syntax Error in 'TEMP_TABLE'.
usage: bcp [[database_name.]owner.]ta ble_name[: slice_numb er] {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]
Copy direction must be either 'in' or 'out'.
Syntax Error in 'TEMP_TABLE'.
usage: bcp [[database_name.]owner.]ta
[-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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.
Open in new window