Re^2: Skip a row of csv file.

I removed the -1 but it still doesnt work... the data is inserted to the table with no any issues but I' am still having the following error msg. DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A.SERIAL_NUMBER||'',firmware_version=''||C.NAME||'',source_ip=''||A.SOURCE_IP||' at line 1 at /home/c8098/bin/new_srp_rms.pl line 191, <CSVFILE> line 5005.

Comment on Re^2: Skip a row of csv file.

Replies are listed 'Best First'.
Re^3: Skip a row of csv file. by roboticus (Chancellor) on May 08, 2012 at 09:52 UTC
Pan20: It sounds like you've made some progress, as now you're seeing a different problem. That's the sort of message I would expect to see if your CSV file has a field in it containing an embedded quote in it. You can avoid those sorts of problems by using placeholders. (You can see them described in DBI under 'Placeholders and Bind Values'.) It lets you set up the code for your insert once, and then you can use it over and over, something like: `. . . my $ST = $DB->prepare("insert into my_table(foo, bar, baz) values (?, +?, ?)"); . . . code to prepare CSV file . . . # For each record, read it while (my $fields = read_data_from_csv_file()) { # Ignore records we don't want next if $fields->{BLECCH} = 'REJECT'; . . . other stuff . . . # Stuff our record into the database $ST->execute( $fields->{FOO}, $fields->{BAR}, $fields->{BAZ} ); }` [download] ...roboticus When your only tool is a hammer, all problems look like your thumb.	[reply] [d/l]

Replies are listed 'Best First'.

Re^3: Skip a row of csv file.
by roboticus (Chancellor) on May 08, 2012 at 09:52 UTC

Pan20:

It sounds like you've made some progress, as now you're seeing a different problem. That's the sort of message I would expect to see if your CSV file has a field in it containing an embedded quote in it. You can avoid those sorts of problems by using placeholders. (You can see them described in DBI under 'Placeholders and Bind Values'.) It lets you set up the code for your insert once, and then you can use it over and over, something like:

. . .
my $ST = $DB->prepare("insert into my_table(foo, bar, baz) values (?, 
+?, ?)");
. . .
code to prepare CSV file
. . .
# For each record, read it
while (my $fields = read_data_from_csv_file()) {
   # Ignore records we don't want
   next if $fields->{BLECCH} = 'REJECT';
   . . .
   other stuff
   . . .
   # Stuff our record into the database
   $ST->execute( $fields->{FOO}, $fields->{BAR}, $fields->{BAZ} );
}
[download]

...roboticus

When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]