Nicholas Cheek

LITTLE ROCK WEB DEVELOPER

This retrieves and parses a csv file.  It would be a lot quicker to use mysql inline if you have a large file.

<?php
//bump up timeout
ini_set('max_execution_time', 300);	

//timer
$time_start = microtime(true);

//set path	
$file_path = "ind.txt";


include('Net/SFTP.php');
$sftp = new Net_SFTP('url for ftp');
if (!$sftp->login('username', 'pw')) {
    exit('Login Failed');
}

//storing the file on our server
$sftp->get('file you would like to get', $file_path);



//for display of the rows and records if needed
//or for testing
/*$row = 1;
if (($handle = fopen($file_path, "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, "|")) !== FALSE) {
        $num = count($data);
        //echo "<p> $num fields in line $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
           // echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
}
*/

//read the file in to the database
//this take a good amount of time and resources
//file should be ~40k records.


// Connect to Database
    $link = mysql_connect('localhost', 'root', 'pw');
    if (!$link) {
        die('Could not connect to db.');
    }

// Grab the correct DataTable.
    $db_selected = mysql_select_db('data', $link);
    if (!$db_selected) {
        die ("Can't access the db");
    }
    
//for testing, remove all data first
//What are we going to do with this?
$remove="Truncate Table mytable";
mysql_query($remove);


//open the csv file for reading
        $handle = fopen($file_path, 'r');
		$skiprow = 0;
		while (( $data = fgetcsv( $handle, 0, '|', '"', '"' )) !== FALSE) {
        $skiprow++;
		if ($skiprow == 1) { continue; }

            //Access field data in $data array ex.
                        $ind0 = $data[0];
                        $ind1 = $data[1];                           
                        $ind2 = $data[2];
                        $ind3 = $data[3];
                        $ind4 = $data[4];
                        $ind5 = $data[5];
                        $ind6 = $data[6];
                        $ind7 = $data[7];                          
                        $ind8 = $data[8];
                        $ind9 = $data[9];
                        $ind10 = $data[10];
                        $ind11 = $data[11];
                        //Strip whitespace from this one
                        $ind12 = preg_replace('/\s+/', '', $data[12]);
                        $ind13 = $data[13];
                        $ind14 = $data[14];
                        $ind15 = $data[15];
                        $ind16 = $data[16];
                        $ind17 = $data[17];
                        $ind18 = $data[18];   
                        $ind19 = $data[19];  
                        $ind20 = $data[20];                            
                        $ind21 = $data[21];
                        $ind22 = $data[22];
                        $ind23 = $data[23];
                        $ind24 = $data[24];
                        $ind25 = $data[25];
                        $ind26 = $data[26];                          
                        $ind27 = $data[27];
                        $ind28 = $data[28];
                        $ind29 = $data[29];
                        
                      

            
            //echo $ind0 . " imported";
            //echo '<br/>';

            //Use data to insert into db
            $sql = sprintf("INSERT INTO omitted (itemnum, suppliernum, vendorprefix,vendorname,longdesc,shortdesc,cost,uom,uomeach,uomdesc,hcpcs,medicare_ext,imgname,dropship_flag,special_order,overweight_flag, weight,weight_uom,dimensions,dimensions_uom,segment1,segment2,segment3,segment4,segment5,segment6,segment7,segment8,script_required,date_created) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",
                        mysql_real_escape_string($ind0),
                        mysql_real_escape_string($ind1),                            
                        mysql_real_escape_string($ind2),
                        mysql_real_escape_string($ind3),
                        mysql_real_escape_string($ind4),
                        mysql_real_escape_string($ind5),
                        mysql_real_escape_string($ind6),
                        mysql_real_escape_string($ind7),                           
                        mysql_real_escape_string($ind8),
                        mysql_real_escape_string($ind9),
                        mysql_real_escape_string($ind10),
                        mysql_real_escape_string($ind11),
                        mysql_real_escape_string($ind12),
                        mysql_real_escape_string($ind13),
                        mysql_real_escape_string($ind14),
                        mysql_real_escape_string($ind15),
                        mysql_real_escape_string($ind16),
                        mysql_real_escape_string($ind17),
                        mysql_real_escape_string($ind18), 
                        mysql_real_escape_string($ind19),    
                        mysql_real_escape_string($ind20),                          
                        mysql_real_escape_string($ind21),
                        mysql_real_escape_string($ind22),
                        mysql_real_escape_string($ind23),
                        mysql_real_escape_string($ind24),
                        mysql_real_escape_string($ind25),
                        mysql_real_escape_string($ind26),                            
                        mysql_real_escape_string($ind27),
                        mysql_real_escape_string($ind28),
                        mysql_real_escape_string($ind29)
                    
                        );
            mysql_query($sql) or (mysql_query("ROLLBACK") and die(mysql_error() . " - $sql"));


        }

//end timer
$time_end = microtime(true);

//calculate amount of time ran
$time = $time_end - $time_start;

echo "Completed $time seconds\n";

//delete csv file
//unlink($file_path);
   

?>

LITTLE ROCK WEB DEVELOPER

Living the dream.

nick@nicholascheek.com