|
|
Posted by Vince LaMonica on 10/16/95 11:19
On Wed, 22 Jun 2005, Sergey wrote:
} You can insert file data in DB first, using LOAD DATA INTO FILE, after it'll
} be easy to manipulate this DB table with a help of php-script.
Actually, I can't, as the CSV contains fields for two different tables. I
may have explained it better here:
I have a CVS file that has order header *and* line item info on each line,
like:
1110,6/20/2005,Jan Doe,123 Main St,99990000,1,Book
1116,6/22/2005,Jim Smith,44 Here St,19191980,1,CD
1116,6/22/2005,Jim Smith,44 Here St,77736222,1,Tape
The above is actually two orders - one with one line item, and the 2nd
with two line items. I need to insert data from those lines into two
tables:
insert into order_header (o_num, date, name, addr)
values ('1110','6/20/2005','Jan Doe','123 Main St'),
('1116','6/22/2005','Jim Smith','44 Here St');
insert into line_items (o_num, item_num, quan, desc, line_order)
values ('1110','99990000','1','Book','1'),
('1116','19191980','1','CD','1'),
('1116','77736222','1','Tape','2');
Note the line_order field - it needs to increment per order for each line
item added to the line_items table. To complicate matters a bit, I'm
actually massaging the data before inserting [eg: splitting the name field
from the CSV into two fields for the mysql db, formatting the date field
for mysql, etc].
I'm currently doing this process via a form where a user uploads the CVS
file [created with Excel, complete with the first row being made up the
Excel table's header].
I currently do something like this:
$fp = fopen("/tmp/"."$txt_file", "r");
while ($line = fgets($fp,1024))
{
$i++
if ($i > 1) { // skip excel header row
list ($o_num, $date, $name, $addr, $item_num, $quan, $desc) = csv_explode($line);
// i can now print the vars, but i get duplicate header records when
// there are multiple line items for a particular order. also, i
// need to generate the line_order field for insertion into the
// line_items table
}
}
If I try and do any processing up where my comments are, well, the
comments tell you what happen. I know I am reading this file line by line,
so I can't compare order numbers [o_num] to group multiple line item
orders together. So how do I go about doing that? Read the entire CSV into
an array? How can that help? Any tips would be most appreciated!
Thanks!
/vjl/
p/s - FYI, cvs_explode() is:
function csv_explode($str, $delim = ',', $qual = "\"")
{
$len = strlen($str);
$inside = false;
$word = '';
for ($i = 0; $i < $len; ++$i) {
if ($str[$i]==$delim && !$inside) {
$out[] = $word;
$word = '';
} else if ($inside && $str[$i]==$qual && ($i<$len && $str[$i+1]==$qual)) {
$word .= $qual;
++$i;
} else if ($str[$i] == $qual) {
$inside = !$inside;
} else {
$word .= $str[$i];
}
}
$out[] = $word;
return $out;
}
Navigation:
[Reply to this message]
|