|
Posted by "Sergey" on 10/20/70 11:19
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.
"Vince LaMonica" <vjl@cullasaja.com> ???????/???????? ? ???????? ?????????:
news:Pine.OSX.4.61.0506211303140.5885@crichton.vjl.org...
>I sent this note off to the php-db list last night, but the more I thought
>about it, the more I figured this was a general looping question rather
>than a mysql-specific one.
>
> I am attempting to take a CSV file that has order header and line item
> data on each line and split it into mulitple insert queries into a mysql
> db. Each line in the CSV file may only be one order, though it is common
> for there to be more than one item ordered, so I need to be able to loop
> to insert the line item values into the proper line item table. I also
> need to build a counter for a value that is not provided in the CVS for
> each line item.
>
> Here is a sample of a few lines of the CSV:
>
> 1110,6/20/2005,Jan Doe,1234 Spring
> St.,Anytown,PA,17033,0618456990,22.50,1,The Sample Book
> 1114,6/22/2005,Jon Smith,888 Main St.,Big
> City,CA,92648,0444409444,19.95,1,Coloring Book
> 1114,6/22/2005,Jon Smith,888 Main St.,Big
> City,CA,92648,9834119933,4.40,1,Picture Book
> 1114,6/22/2005,Jon Smith,888 Main St.,Big
> City,CA,92648,9482222922,59.99,4,Coffee Book
>
> In the above file, the last 4 fields [item_num, cost, quantity, title]
> belong in a line_items table. The first number, the order_number, also
> goes into the line_items table, as well as the order_header table. The
> contact info for each customer also goes into the order_header table. I do
> not want duplicate entries in the order_header table, so I can't just to a
> simple loop through each line in the text file and do an insert. I need to
> be able to group an order by the order_number [the 1st field] and insert
> the correct number of rows in both tables. I also need to create a counter
> per order showing which line item number each item is. Eg: the Coloring
> Book would be assigned a 1, the Picture book a 2, and the Coffee Book a 3
> for order #1114. The Sample Book in order #1110 would be given a 1, since
> it is the first [and only] item in that order.
>
> I have been successful in assigning each value to a varable and looping
> through the file via:
>
> while ($line = fgets($fp,1024))
> {
> $i++;
> if ($i > 1) { // using 1 because CSV includes a header row
> list($order_number, ...) = csv_explode($line);
>
> [i am using an Excel generated CSV with double quotes around each value
> and so i have a csv_explode function setup to properly extract each value;
> also the real CSV has about 2 dozen fields - i just cut it down to its
> basics for the example here]
>
> Doing 2 inserts here and closing the loop is obviously not the answer,
> since I get duplicate header rows, and I haven't built a counter for the
> line_item's table's line counter field. The primary key in the line item
> table is a combo of the order_number and the line_counter. Each fresh
> order_number needs to reset the line_counter to 1, until all line items
> for that order are inserted.
>
> I am having difficulty figuring out how to loop through the CSV to do the
> inserts and create a line item counter. Any tips?
>
> TIA,
>
> /vjl/
Navigation:
[Reply to this message]
|