Re: [PHP-DB] Re: splitting CSV rows into multiple SQL inserts?
I can't help but wonder why you're using the line_order field at all.
Can't you nix it and count the number of records with each order# as a
stored procedure or some such? It would make your entire order system
easier.
Also, you may think about capturing that first row in its own list:
$fp = fopen("/tmp/"."$txt_file", "r") ;
$headers = fgets($fp,1024) ;
while( $line = fgets( $fp,1024) ) {
$row[] = csv_explode($line);
$table[][] = $row[]
} // Now you've got your whole table in $table[][]
In your csv_explode function, you can use split() to make things
easier on yourself:
$out[] = split(',' $line, 7) ; // Your file has 7 fields, so don't overrun that
Even if you insist on calculating an order_line, you can still use
split. Just push a count onto the end of the list.
$out[] = $order_line ; // according to php.net, array_push() shouldn't be used
I strongly urge you whack the order_line. Or maybe I can bother you
to give me more info about it? It's hard to judge without being
closer to the situation.
Peace,
Doug
On 6/22/05, Doug Hernandez <[EMAIL PROTECTED]> wrote:
> On 6/22/05, Doug Hernandez <[EMAIL PROTECTED]> wrote:
> > I can't help but wonder why you're using the line_order field at all.
> > Can't you nix it and count the number of records with each order# as a
> > stored procedure or some such? It would make your entire order system
> > easier.
> >
> > Also, you may think about capturing that first row in its own list:
> >
> > $fp = fopen("/tmp/"."$txt_file", "r") ;
> > $headers = fgets($fp,1024) ;
> >
> > while( $line = fgets( $fp,1024) ) {
> > $row[] = csv_explode($line);
> > $table[][] = $row[]
> > } // Now you've got your whole table in $table[][]
> >
> > In your csv_explode function, you can use split() to make things
> > easier on yourself:
> >
> > $out[] = split(',' $line, 7) ; // Your file has 7 fields, so don't overrun
> > that
> >
> > Even if you insist on calculating an order_line, you can still use
> > split. Just push a count onto the end of the list.
> >
> > $out[] = $order_line ; // according to php.net, array_push() shouldn't be
> > used
> >
> > I strongly urge you whack the order_line. Or maybe I can bother you
> > to give me more info about it? It's hard to judge without being
> > closer to the situation.
> >
> > Peace,
> > Doug
> >
> > PS: I'm new to the list. What address will post this to the entire user
> > group?
> >
> > On 6/22/05, Vince LaMonica <[EMAIL PROTECTED]> wrote:
> > > I wanted to clarify my question from yesterday, as re-reading it, it
> > > sounds quite confusing.
> > >
> > > 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,,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','','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!
Re: [PHP-DB] Re: splitting CSV rows into multiple SQL inserts?
On 6/22/05, Doug Hernandez <[EMAIL PROTECTED]> wrote:
> I can't help but wonder why you're using the line_order field at all.
> Can't you nix it and count the number of records with each order# as a
> stored procedure or some such? It would make your entire order system
> easier.
>
> Also, you may think about capturing that first row in its own list:
>
> $fp = fopen("/tmp/"."$txt_file", "r") ;
> $headers = fgets($fp,1024) ;
>
> while( $line = fgets( $fp,1024) ) {
> $row[] = csv_explode($line);
> $table[][] = $row[]
> } // Now you've got your whole table in $table[][]
>
> In your csv_explode function, you can use split() to make things
> easier on yourself:
>
> $out[] = split(',' $line, 7) ; // Your file has 7 fields, so don't overrun
> that
>
> Even if you insist on calculating an order_line, you can still use
> split. Just push a count onto the end of the list.
>
> $out[] = $order_line ; // according to php.net, array_push() shouldn't be used
>
> I strongly urge you whack the order_line. Or maybe I can bother you
> to give me more info about it? It's hard to judge without being
> closer to the situation.
>
> Peace,
> Doug
>
> PS: I'm new to the list. What address will post this to the entire user
> group?
>
> On 6/22/05, Vince LaMonica <[EMAIL PROTECTED]> wrote:
> > I wanted to clarify my question from yesterday, as re-reading it, it
> > sounds quite confusing.
> >
> > 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,,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','','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;
> > }
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
>
>
> --
> Douglas Hernandez
> (612) 229-9684
>
--
Douglas Hernandez
(612) 229-9684
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: splitting CSV rows into multiple SQL inserts?
I wanted to clarify my question from yesterday, as re-reading it, it
sounds quite confusing.
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,,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','','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;
}
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
