Importing Data to Google Cloud SQL via CSV

Question

Google Cloud SQL restricts use of LOAD DATA INFILE in SQL statements.

I'm trying to make a feature that would allow users to batch upload user data to my web app so they don't have to make each entry one by one.

How would I go about accomplishing this within the restrictions of GAE PHP and Cloud SQL?

Update: Users of my app are able to manage a list of students. I want my users to be able to submit a CSV file and have that input into their student database. I can't use sync or MySQL workbench, etc. It has to be done within the web app.

Thanks for your input!

Answer 1

I too was recently looking for a way for power users to bulk upload. My first successful attempt was variable specific so check my mapping. This trial was completed without a frontend so you'll need to build a form for users to upload to your bucket. My answer assumes familiarity with Google Storage.

<?php

$databasehost = ":/cloudsql/something:here"; //Name of your CLOUD SQL INSTANCE ID
$databasename = "db";
$databaseusername ="insert_usr";
$databasepassword = "pwd";
$csv = "gs://APPID.appspot.com/csv_uploads/bulk.csv"; //APPID is the specific name of your app, followed by other bucket(s), if any, and the filename.csv

$column0 = ''; //insert the name of your columns as appeared in MySQL
$column1 = '';
$column2 = '';
$column3 = '';
$column4 = '';
$column5 = '';
$column6 = '';
$column7 = '';
$column8 = '';
$column9 = '';
$column10 = '';
$column11 = '';

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error()); //Don't remember why I added '@' might be error
@mysql_select_db($databasename) or die(mysql_error());

$csv_file = $csv; // Name of your CSV file
$csvfile = fopen($csv_file, 'r');
$theData = fgets($csvfile);
$i = 0;
while (!feof($csvfile)) {
$csv_data[] = fgets($csvfile);
$csv_array = explode(",", $csv_data[$i]);
$insert_csv = array();
$insert_csv[$column0] = $csv_array[0]; //array[#] correspondes to columns in order
$insert_csv[$column1] = $csv_array[1];
$insert_csv[$column2] = $csv_array[2];
$insert_csv[$column3] = $csv_array[3];
$insert_csv[$column4] = $csv_array[4];
$insert_csv[$column5] = $csv_array[5];
$insert_csv[$column6] = $csv_array[6];
$insert_csv[$column7] = $csv_array[7];
$insert_csv[$column8] = $csv_array[8];
$insert_csv[$column9] = $csv_array[9];
$insert_csv[$column10] = $csv_array[10];
$insert_csv[$column11] = $csv_array[11];

$query = "
INSERT INTO TABLENAME(".$column1.",".$column1.",".$column2.",".$column3.",".$column4.",".$column5.",".$column6.",".$column7.",".$column7.",".$column9.",".$column10.",".$column11.",)
VALUES('".$insert_csv[$column0]."','".$insert_csv[$column1]."','".$insert_csv[$column2]."','".$insert_csv[$column3]."','".$insert_csv[$column4]."','".$insert_csv[$column5]."','".$insert_csv[$column6]."','".$insert_csv[$column7]."','".$insert_csv[$column8]."','".$insert_csv[$column9]."','".$insert_csv[$column10]."','".$insert_csv[$column11]."')";
$n=mysql_query($query, $con );
$i++;
}
fclose($csvfile);

echo "File data successfully imported to database!!";
mysql_close($con);
?>

In my trial, rows with special characters (single and double quotes and hyphens, that I can recall) did not import so pay attention to your row_count().

Answer 2

The only way I think you can do this is by creating a PHP script to read the file and insert it into the Database using SQL INSERT statements, the normal way by which you would insert data into a cloudSQL instance.