MySQLi CSV Load Data Infile to MySQL (Certain columns in CSV, certain columns in MySQL)

Question

I have searched the last week for something on this, but nothing I have found works, and I have looked at the documents, and nothing in the documents work either.

I have a CSV (link below to download) that I need to upload just a couple columns to MySQL, using MySQLi. The CSV is from a platform we use to serve ads, the client name has been changed, but the data is all true data from two days.

Here is the code I have for the upload (There are 3 different versions of the SQL command, I have tried around 30 different ones):

<? 
(MY USER INFORMATION FOR MY MYSQL SERVER)
$ad_results = mysqli_connect($dbHost, $dbUser, $dbPass, $dbName);
ignore_user_abort(true);
set_time_limit(9999);

$sql1 =  "LOAD DATA INFILE 'CSV_FILE_FOR_STACKOVERFLOW.csv' INTO TABLE ad_info
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'  
(@col1,@col2,@col6,@col12,@col13,@col14) set client=@col2,impressions_delivered=@col12,date=@col1,impressions_booked=@col13,clicks=@col14,creative=@col6";

$sql2 = "LOAD XML INFILE '558385_20130815-153744.xml' INTO TABLE item ROWS IDENTIFIED BY '<row>'";

$sql3 = "LOAD DATA INFILE 'CSV_FILE_FOR_STACKOVERFLOW.csv' INTO TABLE ad_info
        FIELDS TERMINATED BY ','
        LINES TERMINATED BY '\n'
        IGNORE 1 LINES
        (@client, @name)
        set
          client = @client,
          impressions_delivered = @impressions_delivered,
          company_id = '1000001'";
mysqli_query($ad_results, $sql3);`
mysqli_close($ad_results)
?>

The MySQL database is set up with all of the columns from the CSV, just in case it is not possible to do just certain columns, and I am willing to upload all the data. The fun part is that there are 30-40 CSV files that will go through this code every day at 1AM, I have the code to select all the CSVs in the download folder, just need the code to insert them into MySQL. Thanks! (Please reply with true answers, I have searched everywhere, and nothing I have found works, it always gives me an error, or just does not insert the lines into MySQL, the closest I have gotten is parsing the CSV and using

mysqli_query($ad_results, "INSERT INTO ad_info (...)VALUES(...)");

This file lives on the server, and the cron job does run the PHP script every night, it has ignore user abort, and a run time of 9999 seconds. (to make sure it gets all the data). Thanks in advance for any help!

CSV FILE: http://ad-results.com/CSV_FILE_FOR_STACKOVERFLOW.csv

I'm sorry, but what exactly is the problem? The import failed? Wrong or no data inserted? Is there any *warning* (`SHOW WARNINGS`)? — Sylvain Leroux, Aug 16 '13 at 13:36
Looking at the data I noticed that you are using an American date format `8/13/13` which might lead to problems ... I have not tested it yet. — Carsten Massmann, Aug 16 '13 at 13:41
Does anyone know *from where* `LOAD DATA INFILE` picks up the file? Is there a default directory that must be used? — Carsten Massmann, Aug 16 '13 at 15:28
No warnings, data is not showing up in MySQL, all the columns in MySQL are all varchar (255) so any dates can be input no problem, along with any other data (due to the fact when I am building the UI, I don't know what exactly it will be yet). Sylvain, where would I put the `SHOW WARNINGS`? I will create a page that will dump any information from MySQL onto it, and try more options. — Brian Logan, Aug 16 '13 at 15:28
Cars10, as far as I know, `LOAD DATA INFILE` starts from the top of the file, there is code out there to skip lines, but I can't even get it to insert into MySQL... ahh, when code breaks! :P — Brian Logan, Aug 16 '13 at 15:31
@BrianLogan Sorry, I wasn't clear in my question: From which *directory* on which server (database or php server?) does the `LOAD DATA INFILE` command try to pick up the input file? I tried out your command on my shared host and got the error 1045 "Access denied for user 'myusrname'@'localhost' (using password: YES)". I found the following for this context: http://stackoverflow.com/questions/6837061/mysqlimport-error-1045-access-denied . It seems I have not got sufficient access rights ... — Carsten Massmann, Aug 16 '13 at 15:45
I am using PHP to run the commands. They are in the same folder as the PHP script. No warnings are being shown. Here is the full script I am using, (I am not done with it yet of course, I need to add in the code for grabbing each CSV file, right now I am just testing with one file). I updated the script above with the full code I am using (minus the username information). I also have tried using XML, it does not work either. It inserted all the content into one field in MySQL, instead of the seperate fields. — Brian Logan, Aug 16 '13 at 15:50
I added the show warnings, and the only thing coming up on the page now is: `bool(false)` — Brian Logan, Aug 16 '13 at 16:07

score 0 · Accepted Answer · answered Aug 16 '13 at 16:16

0

I cannot find a mysqli_error($ad_results) call in your code. You should save the response of your mysqli_query() in a resource-variable $mysqli and then display a possible MySQL error like:

$mysqli=mysqli_query($ad_results, $sql3);
if (!$mysqli) echo mysqli_error($ad_results);

Maybe there is a message waiting for you after all? ;-)

answered Aug 16 '13 at 16:16

Carsten Massmann

16,701
2
16
39

Now I am getting the same issue you were. Ok, ONE step closer! I chmodded the folder to 777, no difference, it is a login error, which I don't understand. – Brian Logan Aug 16 '13 at 16:22
I have reached out to my host to grant me access to FILE on my MySQL server. Hope it fixes the problem! Thanks! – Brian Logan Aug 16 '13 at 16:45

MySQLi CSV Load Data Infile to MySQL (Certain columns in CSV, certain columns in MySQL)

1 Answers1