Below is some code to hopefully get you started. You will
want to read about opendir and readdir
or even file globbing
to process a directory full of these files.
NOTE: SQL was not tested for correct syntax and no handling
of special characters is provided. You should use placeholders
or $dbh->quote() to account for special characters.
use strict;
my @field;
my @value;
while (split/\|/,<DATA>) {
chomp $_[2];
# not sure if you want to remove trailing <br> tags
#$_[2] =~ s/\<br>$//i;
if ($_[2]) {
push @value, "'$_[2]'";
push @field, $_[1];
}
}
my $insert = "INSERT INTO my_table (" .
join(',',@field) . ") VALUES (" .
join(',',@value) . ")";
print $insert , "\n";
__DATA__
<BR><BR>
1|ref_no|SPC200202198<BR>
2|order_completed|Y<BR>
3|time|21:59:00<BR>
4|date|02/19/2002<BR>
5|name|John Doe<BR>
6|address|123 Main Street<BR>
7|city|Baltimore<BR>
8|state|MD<BR>
9|zip|21201<BR>
10|country|2<BR>
11|ship_address|<BR>
12|ship_city|<BR>
13|ship_state|<BR>
14|ship_zip|<BR>
15|ship_country|<BR>
16|phone|555-555-5555<BR>
17|fax|555-555-4444<BR>
18|email|john@doe.com<BR>
19|product_info|SC40F::SupraCart 4.0::Purchase <BR>Installation: no in
+stallation; Reseller: No; How did you hear about SupraCart 4.0: None
+of the Above::1::59.99<BR>
20|shipping_option|None<BR>
21|discount_number|<BR>
22|discount_price|0.00<BR>
23|shipping_price|0.00<BR>
24|sales_tax_price|0.00<BR>
25|total_price|59.99<BR>
26|time_last_update|21:59:00<BR>
27|date_last_update|02/19/2002<BR>
28|text_description|This order has been completed and processed.<BR>
|