Import dynamic csv into MySQL

Question

I am looking for a way to import a csv file that have columns that may or may not be in the same order every time. also there may be extra columns some times..

This is what I have so far... it loops through each row and inserts it into the mysqldb.

my problem is that the columsn and column orders change sometime, NEVER the name of the columns tho. they just add and remove columns.

i am trying to avoid having my client have to modify the csv extensivly before they import.

I woulds like the script to see the column name in the csv and match it automatically to the column in the table..

rather than relying on $data[1]... that specifies a specific column.

// Process Scrubbed acounts import
if ($_FILES['scrubImport']['tmp_name']) {

    // Get the Rep Account ID
    ${'Rep Account'} = $_POST['importToRep'];

    // Get our total # of contacts
    $contacts = mysql_result(mysql_query('SELECT count(0) FROM `accounts` WHERE `owner` = '.${'Owner'}), 0);

    // Get our import file extension
    ${'Extension'} = strtolower(array_pop(explode('.', $_FILES['scrubImport']['name'])));

    // Store our contacts array
    ${'Accounts'} = array();
    // If our file is a CSV
    if (${'Extension'} == 'csv') {

        // Get our row data
        ini_set('auto_detect_line_endings',true);
        if (($handle = fopen($_FILES['scrubImport']['tmp_name'], "r")) !== FALSE) {
            while (($data = fgetcsv($handle)) !== FALSE) {

                // Sanitize all our data
                foreach ($data as $i => $v) { $data[$i] = inputCleanText($v); }

                // Add the data to our contacts array
                if($data[0] != 'Keyword'){
                    ${'Accounts'}[] = array('Keyword' => $data[0], 'Company Name' => $data[1], 'Owner First Name' => $data[2], 'Owner Last Name' => $data[3], 'Email' => $data[4], 'Phone' => str_replace('-','',str_replace(' ','',str_replace('(','',str_replace(')','',$data[5])))), 'Ranking On The 1st Page' => $data[6], 'Citations' => $data[7], '1st Category' => $data[8], 'Categories' => $data[9], 'Google+ Score' => $data[10], 'Google Reviews #' => $data[11], 'Google Reviews Found' => $data[12], '3rd Party Site Details' => $data[13], '3rd Party Site Summary' => $data[14], 'Google Bad Reviews #' => $data[15], 'Google Bad Reviews' => $data[16], 'IP Bad Reviews #' => $data[17], 'IP Bad Reviews' => $data[18], 'Images #' => $data[19], 'Videos #' => $data[20], 'Google+ Offers #' => $data[21], 'Google+ Offers Found' => $data[22], 'Groupon Offer' => $data[23], 'Groupon Offer Title' => $data[24], 'Groupon Purchase' => $data[25], 'Groupon Offer Expiration' => $data[26], 'Living Social Offer' => $data[27], 'Living Social Offer Title' => $data[28], 'Living Social Purchases' => $data[29], 'Living Social Expiration' => $data[30], 'Website URL' => $data[31], 'Keywords In Homepage' => $data[32], 'Mobile Optimized Site' => $data[33], 'Website Backlinks' => $data[34], 'Website Page Rank' => $data[35], 'Google+ URL' => $data[36], 'Owner Verified' => $data[37], 'Facebook Page URL' => $data[38], 'FB Likes #' => $data[39], 'FB Talking About This #' => $data[40], 'Twitter Page URL' => $data[41], 'Twitter Tweets #' => $data[42], 'Twitter Followers #' => $data[43], 'Twitter Page Found?' => $data[44], 'Address' => $data[45], 'City' => $data[46], 'State/Region' => $data[47], 'Postal Code' => $data[48], 'Country' => $data[49]);

                }
            }
        }
        fclose($handle);
    }

    // Start looping thru the rows
    $row = 0;
    $valid_count = 0;
    foreach (${'Accounts'} as ${'Account'}) {

        // Increase our valid count
        ++$valid_count;

        // Add the account
        mysql_query('INSERT INTO `accounts` (`owner`, `company_name`, `contact_fname`, `contact_lname`, `office_phone`, `email_primary`, `address_1`, `city`, `state`, `zip`, `relationship`, `acquired_via`, `website`) VALUES ("'.${'Rep Account'}.'", "'.${'Account'}['Company Name'].'", "'.${'Account'}['Owner First Name'].'", "'.${'Account'}['Owner Last Name'].'", "'.${'Account'}['Phone'].'", "'.${'Account'}['Email'].'", "'.${'Account'}['Address'].'", "'.${'Account'}['City'].'", "'.${'Account'}['State/Region'].'", "'.${'Account'}['Postal Code'].'", "Scrubbed Prospect", "Scrubbed Account", "'.${'Account'}['Website URL'].'")') or die(mysql_error());

        ${'New Account ID'} = mysql_insert_id();

        // Add the additional scrub info                        
        mysql_query('INSERT INTO `crm`.`accounts_scrubbed` (`account_id`,`keyword`,`ranking_first_page`,`citations`,`1st_category`,`categories`,`google_plus_score`,`google_reviews`,`num_google_reviews`, `3rd_party_site_details`,`3rd_party_site_summary`,`#_bad_google_reviews`,`google_bad_reviews`,`#_ip_bad_reviews`,`ip_bad_reviews`,`#_images`,`#_videos`,`google_plus_offers`,`google_plus_offers_found`,`groupon_offer`,`groupon_offer_title`,`groupon_purchase`,`groupon_offer_expiration`,`living_social_offer`,`living_social_offer_title`,`living_social_purchases`,`living_social_expiration`,`keywords_in_homepage`,`mobile_optimized_site`,`website_backlinks`,`website_page_rank`, `google_plus_url`,`owner_verified`,`facebook_page_url`,`#_facebook_likes`,`#_facebook_talking_about_this`,`twitter_page_url`,`#_twitter_tweets`,`#_twitter_followers`,`twitter_page_found`,`country`) VALUES ("'.${'New Account ID'}.'", "'.${'Account'}['Keyword'].'", "'.${'Account'}['Ranking On The 1st Page'].'", "'.${'Account'}['Citations'].'", "'.${'Account'}['1st Category'].'", "'.${'Account'}['Categories'].'", "'.${'Account'}['Google+ Score'].'", "'.${'Account'}['Google Reviews Found'].'", "'.${'Account'}['Google Reviews #'].'", "'.${'Account'}['3rd Party Site Details'].'", "'.${'Account'}['3rd Party Site Summary'].'", "'.${'Account'}['Google Bad Reviews #'].'", "'.${'Account'}['Google Bad Reviews'].'", "'.${'Account'}['IP Bad Reviews #'].'", "'.${'Account'}['IP Bad Reviews'].'", "'.${'Account'}['Images #'].'", "'.${'Account'}['Videos #'].'", "'.${'Account'}['Google+ Offers #'].'", "'.${'Account'}['Google+ Offers Found'].'", "'.${'Account'}['Groupon Offer'].'",  "'.${'Account'}['Groupon Offer Title'].'", "'.${'Account'}['Groupon Purchase'].'", "'.${'Account'}['Groupon Offer Expiration'].'", "'.${'Account'}['Living Social Offer'].'", "'.${'Account'}['Living Social Offer Title'].'", "'.${'Account'}['Living Social Purchases'].'", "'.${'Account'}['Living Social Expiration'].'", "'.${'Account'}['Keywords In Homepage'].'", "'.${'Account'}['Mobile Optimized Site'].'", "'.${'Account'}['Website Backlinks'].'", "'.${'Account'}['Website Page Rank'].'", "'.${'Account'}['Google+ URL'].'", "'.${'Account'}['Owner Verified'].'", "'.${'Account'}['Facebook Page URL'].'", "'.${'Account'}['FB Likes #'].'", "'.${'Account'}['FB Talking About This #'].'", "'.${'Account'}['Twitter Page URL'].'", "'.${'Account'}['Twitter Tweets #'].'",  "'.${'Account'}['Twitter Followers #'].'", "'.${'Account'}['Twitter Page Found?'].'", "'.${'Account'}['Country'].'")');

        // Add the generated report information
        mysql_query('INSERT INTO `crm`.`report` (`account_id`, `generated`, `sent`, `report_id`) VALUES ("'.${'New Account ID'}.'", "0", "0", "'.md5(microtime()).'")');

        // Add the last touched Record
        mysql_query('INSERT INTO `last_touched` (`account_id`, `user_id`, `reason`) VALUES ("'.${'New Account ID'}.'", "'.$_SESSION['userID'].'", "Imported Scrub")');

        ++$row;
    }

    // Post a message
    ${'Message'} = '<div class="success">Import successful. '.number_format($valid_count).' account(s) were imported sucessfully!</div>';

}

Im not sure if i am being completely clear on what i am trying to do.

You gotta show us some sample content... otherwise: https://www.google.com/search?q=php+csv+import&oq=php+csv+import&aqs=chrome.0.57j0l2j60j0j62.2764j0&sourceid=chrome&ie=UTF-8 particularly http://stackoverflow.com/questions/5813168/how-to-import-csv-file-in-php — user1477388, Jun 04 '13 at 20:22
Welcome to StackOverflow! Please read [FAQ](http://stackoverflow.com/faq) and tell us what is your question exactly? — peterm, Jun 04 '13 at 20:23
From your limited information in the question, you most likely will encounter major issues with a dynamically changing flat file, as you will be more successful reading the file into php and parsing the header columns, to determine what to do with additional columns and data filtering along with handling malformed files and errors, mysql alone won't be able to do all this. — on_, Jun 04 '13 at 20:31
what about a way to get a list of the column names from the table and have them in an array, then match the column header from the csv file to something in that array, if it matches then add to a variable holding the sql statemnnt? — JD Vangsness, Jun 04 '13 at 20:37
You really shouldn't be using `mysql_query` in new applications. It's deprecated, dangerous if used incorrectly like you have, and will be removed in future versions of PHP. Before you get yourself into serious trouble, please read up on [proper SQL escaping](http://bobby-tables.com/php) and explore using a modern MySQL database layer like [PDO](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/) instead. — tadman, Jun 04 '13 at 20:46
This is my testing script. I have a bunch of mysql escape functions written for certain types of data.. just not using them now. i iwll add them in after the script is functioning properly. as far as the MySQL database layer for this application, i agree, i would rather use PDO but the lead developer wants me to script just like the rest of the code. GRRR. i guess they arent planning on upgrading the PHP version on this server and we will be rewriting this entire system once the core layer is done. I dont lik eit but it is helping [pay the bills. :) — JD Vangsness, Jun 04 '13 at 21:01

score 2 · Accepted Answer · answered Jun 04 '13 at 20:50

2

If the csv file has a header record then you can do this

  $fp = fopen($file, "r");
  $header = fgetcsv($fp);
  while ($line = fgetcsv($fp)){
       $record = array_combine($header, $line);
       // now record contains an array of fields keyed by the associated key in the header record
  }

from here you can reference the fields within the while loop as

  $record["fieldname"]

This should give you what you need to build up your query.

answered Jun 04 '13 at 20:50

Orangepill

24,500
3
42
63

thanks i will see what i can do with that to match the columns up. – JD Vangsness Jun 04 '13 at 21:02

Import dynamic csv into MySQL

1 Answers1