CSV import to MSSQL Issues with php script

Codewalkers Forums Sponsor:

October 2nd, 2009, 10:56 AM

marksie1988

Registered User

Join Date: Oct 2009

Posts: 8

Time spent in forums: 2 h 1 m 24 sec
Reputation Power: 0

php5 - CSV import to MSSQL Issues with php script

Hi All,

i have been working on a website which has a products database which imports csv files on a night by deleting all db entries and reimporting the products from the csv file.

i want to change this as we are planning on updating products when the system is in use and obviously we cant delete everything from the db so i have been trying to create a script bit by bit that will do something similar to this:

PHP Code:


		
			
 SELECT * from products WHERE supp_id = 1 and db.manf_code = csv.manf_code

if ($rows == 1 && $csvrow == 1){

update $quantity, $cost_price, $description and $RRP 

}

elseif ($rows == 1 && $csvrow == 0){

DELETE From DB where Manf_code == fromselectcommand AND supp_id = 1 

elseif ($rows == 0){

INSERT into products table all info required

}

this is my current code but i know that it isnt going to work the way i want it to. as i want it to look row by row and if the row exists in the database to update it, if the row doesnt exist in the database insert it and if the row is no longer in the csv file to delete it from the database. does anyone know how to do this?

PHP Code:


		
			
<?php



define ('GALTEC'      , 1);

define ('COMPANY_NAME', 2);

define ('OTHER_COMP'  , 3);



// create DB connection

 $host = "localhost";

 $user = "user";

 $pass = "pass";

 $mydb = "db";

 $table = "products";



        $db = mssql_connect($host,$user,$pass);

        mssql_select_db($mydb);



function process_csv( $csvupload, $from_company, $callback )

{

    $handle = fopen($csvupload, 'r') or die("Unable to open $csvupload for reading");

    $head = array();



    //  You said that each company always sends you files with the same columns.

    //  Describe the differenct columns here, for each company separately.  If

    //  the first row of the CSV file already contains the field names, you can

    //  also simply read them, as I did with GALTEC.

    switch( $from_company )

    {

        case GALTEC:        $head = fgetcsv( $handle, 512, ',' );   //  Don't forget to catch any errors.

                            break;



        case COMPANY_NAME:  //  I don't know what the content of each column is, so I'm making the names up.

                            $head[] = 'supp_part_no';    // e.g., REMANG-BT350

                            $head[] = 'manf_part_no';       // e.g., ADG-BT350

                            $head[] = 'description';        // e.g., Qfax BT350/370 ALTERNATE TTR

                            $head[] = 'avail_qty';       // e.g., .000

                            $head[] = 'cost_price';      // e.g., 6.27

                            $head[] = 'SUPPLIER';   // e.g., AD

                            $head[] = 'COMMENT';    // everything after the last comma

                            break;

                            

        case OTHER_COMP:    //  This company supplies us with a CSV file that contains a header, but we

                            //  use different field names, so we read the first lnie, then override the

                            //  field names.

                            $flush = fgetcsv( $handle, 512, ',' );   //  Don't forget to catch any errors.

                            $head[] = 'PRODUCT';    // e.g., REMANG-BT350

                            $head[] = 'SUPPLIER';   // e.g., AD

                            $head[] = 'TYPE';       // e.g., ADG-BT350

                            $head[] = 'SIZE';       // e.g., .000

                            $head[] = 'AKA';        // e.g., Qfax BT350/370 ALTERNATE TTR

                            $head[] = 'PRICE';      // e.g., 6.27

                            $head[] = 'COMMENT';    // everything after the last comma

                            break;

                            

    }



    //  At tihs point, $head should always contain the same column names,

    //  regardless of the company that sent the CSV file.  Only the order

    //  may differ from company to company



    $i = 1;

    while ( false !== ( $data = fgetcsv( $handle, 512, ',' ) ) )

    {

        if( count( $data ) == count( $head ) )

        {

            $callback( array_combine( $head, $data ) );

        }

        else

        {

            die("Erroneus CSV file: incorrect field count on row $i");

        }

    }

}





function process_record($row)

{

    $select = "SELECT * FROM FindMeStock.dbo.products WHERE manf_part_no = '".$row['manf_part_no']."' AND supp_id = '1'";

    $qry = mssql_query($select) or die("Failed to select from DB");

        while($rows = mssql_fetch_assoc($qry)){

            if ($rows == 1 && $row == 1){

                $update = "UPDATE FindMeStock.dbo.products SET avail_qty = '".$row['avail_qty']."', cost_price = '".$row['cost_price']."' , description = '".$row['description']."', date_added = 'getdate()' WHERE manf_part_no = '".$row['manf_part_no']."' AND supp_id = '1' ";

                mssql_query($update) or die("Failed to update DB");

            }

            elseif($rows == 1 && $row == 0){

                $delete = "DELETE FROM FindMeStock.dbo.products WHERE  manf_part_no = '".$row['manf_part_no']."' AND supp_id = '1' ";

                mssql_query($delete) or die("Failed to delete from DB");

            }

            elseif($rows ==0 && $row == 1){

                $insert = "INSERT INTO FindMeStock.dbo.products VALUES supp_id = '1', supp_part_no = '".$row['supp_part_no']."', description = '".$row['description']."', cost_price = '".$row['cost_price']."', avail_qty = '".$row['avail_qty']."'";

                mssql_query($insert) or die("Failed to insert to DB");

            }

            //echo $rows['product_id'];

        }

    //  At this point, $row is an array with the following structure:

    //  $row = Array ( [PRODUCT] => REMANG-BT350 [TYPE] => ADG-BT350 [AKA] => Qfax BT350/370 ALTERNATE TTR [SIZE] => .000 [PRICE] => 6.27 [SUPPLIER] => AD [COMMENT] => )

    //  Note that you can now access each item using standard indexes:



    //echo $row['PRODUCT'], ' ';

    //echo $qry;

}





process_csv( '../advent/pricelist.csv', COMPANY_NAME, 'process_record');

?>

October 7th, 2009, 02:22 PM

icandothat

Super Moderator

Codewalkers Intermediate (1500 - 1999 posts)

Join Date: Apr 2007

Location: San Diego, CA

Posts: 1,667

Time spent in forums: 3 Days 10 m 43 sec
Reputation Power: 4

Check out the mysql keywords "update into" this will update a record if it exists (based on an primary key match) or insert it if it does not exist.

__________________
There is no spoon.

October 9th, 2009, 04:05 AM

marksie1988

Registered User

Join Date: Oct 2009

Posts: 8

Time spent in forums: 2 h 1 m 24 sec
Reputation Power: 0

Quote:

Originally Posted by icandothat

Check out the mysql keywords "update into" this will update a record if it exists (based on an primary key match) or insert it if it does not exist.

this is ok but we don't have a unique key in the csv file, all i could do is a where statement e.g.

update into ... WHERE manf_part_no = "$manf_part_no" AND supp_id = "$supp_id"

but this wouldnt delete a line if it wasnt in the csv file would it?

October 9th, 2009, 01:38 PM

IAmALlama

Join Date: Apr 2007

Location: San Diego, CA

Posts: 2,070

Time spent in forums: 1 Week 6 Days 8 h 15 m 37 sec
Reputation Power: 5

I don't believe MSsql has "update into" anyways. What I would personally do, which is probably a long way around, but what I think is the easiest is make an empty copy of the main table, even a temp table would work, add the csv file to that and then run something like this (probably in a sproc, but can be done by running each query in php):

Code:

--add new rows from csv file.
INSERT INTO mainTable
SELECT *
FROM copyTable
WHERE
    manf_part_no NOT IN (SELECT manf_part_no FROM mainTable)
    AND supp_id NOT IN (SELECT supp_id FROM mainTable)

--remove rows not in csv file.
DELETE FROM mainTable
WHERE
    manf_part_no NOT IN (SELECT manf_part_no FROM copyTable)
    AND supp_id NOT IN (SELECT supp_id FROM copyTable)

--clear everything from the copyTable to prepare for next night.
TRUNCATE TABLE copyTable

October 12th, 2009, 04:57 AM

marksie1988

Registered User

Join Date: Oct 2009

Posts: 8

Time spent in forums: 2 h 1 m 24 sec
Reputation Power: 0

Quote:

Originally Posted by IAmALlama

Code:

--add new rows from csv file.
INSERT INTO mainTable
SELECT *
FROM copyTable
WHERE
    manf_part_no NOT IN (SELECT manf_part_no FROM mainTable)
    AND supp_id NOT IN (SELECT supp_id FROM mainTable)

--remove rows not in csv file.
DELETE FROM mainTable
WHERE
    manf_part_no NOT IN (SELECT manf_part_no FROM copyTable)
    AND supp_id NOT IN (SELECT supp_id FROM copyTable)

--clear everything from the copyTable to prepare for next night.
TRUNCATE TABLE copyTable

Hi,

yes you can do an update function in MSSQL, the idea of putting the CSV into SQL first seems like the best way to go so i have created a table called tempproducts which the csv is imported into but then i have the issue of doing the update.

using your method with the insert into wont update fields which i require for stock level changes etc.

yours will however allow me to insert new and delete old rows which is great.

i need a syntax that will allow me to update specific columns e.g.

UPDATE products SET products.avail_qty = tempproducts.avail_qty WHERE products.manf_part_no = tempproducts.manf_part_no AND products.supp_id = tempproducts.supp_id

the above command doesnt work i get this error:

Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "dbo.tempproduct.manf_part_no" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "dbo.tampproduct.supp_id" could not be bound.

October 12th, 2009, 09:30 AM

MatthewJ

Contributing User

Join Date: May 2007

Location: Davenport, Iowa

Posts: 633

Time spent in forums: 1 Week 1 Day 22 h 11 m 38 sec
Reputation Power: 3

You CAN do UPDATE, but not UPDATE INTO

Comments on this post

IAmALlama agrees:

October 12th, 2009, 11:20 AM

IAmALlama

Join Date: Apr 2007

Location: San Diego, CA

Posts: 2,070

Time spent in forums: 1 Week 6 Days 8 h 15 m 37 sec
Reputation Power: 5

I believe something like this should work for updating

Code:

UPDATE p1
SET p1.avail_qty = t1.avail_qty
FROM Products p1
    INNER JOIN tempproducts t1 ON
        p1.manf_part_no = t1.manf_part_no
        AND p.supp_id = t.supp_id

October 12th, 2009, 04:39 PM

marksie1988

Registered User

Join Date: Oct 2009

Posts: 8

Time spent in forums: 2 h 1 m 24 sec
Reputation Power: 0

Quote:

Originally Posted by IAmALlama

I believe something like this should work for updating

Code:

UPDATE p1
SET p1.avail_qty = t1.avail_qty
FROM Products p1
    INNER JOIN tempproducts t1 ON
        p1.manf_part_no = t1.manf_part_no
        AND p.supp_id = t.supp_id

no this doesnt work just tried it it just reimports the same data again

October 20th, 2009, 10:57 AM

marksie1988

Registered User

Join Date: Oct 2009

Posts: 8

Time spent in forums: 2 h 1 m 24 sec
Reputation Power: 0

Hi,

ok i have managed to get the script working with the queries but i notice that this query never selects anything then i thought of course it doesnt as this is wrong.

Code:

INSERT INTO mainTable
SELECT *
FROM copyTable
WHERE
    manf_part_no NOT IN (SELECT manf_part_no FROM mainTable)
    AND supp_id NOT IN (SELECT supp_id FROM mainTable)

not sure what it should be but it should do the followig

insert data from the temptable that isnt in the maintable.

as there are e.g. 4 suppliers each one could have the same manf_part_no so we need to select the supp_id also?

does the above query do this or not i changed a line in the temp table and id didnt seem to work as 0 rows were affected

Last edited by marksie1988 : October 20th, 2009 at 11:02 AM.

#10

October 20th, 2009, 12:53 PM

IAmALlama

Join Date: Apr 2007

Location: San Diego, CA

Posts: 2,070

Time spent in forums: 1 Week 6 Days 8 h 15 m 37 sec
Reputation Power: 5

you are right that if the same part id from different manufacturers was present in the main table it wouldn't insert the new row. you could probably do something like this, however I don't know how well it would perform because it has to look up the data for each row.

Code:

INSERT INTO mainTable
SELECT *
FROM copyTable
WHERE
    (SELECT manf_part_no FROM mainTable WHERE manf_part_no=copyTable.manf_part_no AND supp_id=copyTable.supp_id) IS NULL

tries to select a column (really doesn't matter which column) from the main table where the manf_part_no and supp_id match the current row in the copy table and if nothing is brought back (is null) then it will add that row.

or something like this with an outter join (which probably would be faster if you have indexes setup on the id columns).

Code:

INSERT INTO mainTable
SELECT CT.*
FROM copyTable CT
    LEFT JOIN mainTable MT ON CT.manf_part_no=MT.manf_part_no AND CT.supp_id=MT.supp_id
WHERE
    MT.manf_part_no IS NULL
    AND MT.supp_id IS NULL

this joins the main table with an outter join on both the id columns and only inserts the data if the ids are null meaning they don't exist in the main table.

#11

October 20th, 2009, 02:43 PM

icandothat

Super Moderator

Join Date: Apr 2007

Location: San Diego, CA

Posts: 1,667

Time spent in forums: 3 Days 10 m 43 sec
Reputation Power: 4

yea, this is a case of my poor attention to detail. I just assumed MySQL.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: