Import data into Google Cloud SQL via CSV

Question

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

I am trying to create a function that will allow users to upload user data to my web application, so they do not need to do each entry in turn.

How could I do this within the limitations of GAE PHP and Cloud SQL ?

Update. Users of my application can manage the list of students. I want my users to be able to send a CSV file and enter it into their database. I can not use sync or MySQL workbench etc. This should be done in a web application.

Thanks for your input!

Answer 1

I also recently searched for a way for powerful mass download users. My first successful attempt was a variable, so check my mapping. This trial completion was completed without an interface, so you will need to create a form for users to upload to your bucket. My answer involves getting to know 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 research, strings with special characters (single and double quotes and hyphens that I can remember) did not import, so pay attention to your row_count ().

Answer 2

The only way I can do this is to create a PHP script to read the file and paste it into the database using SQL INSERT , the normal way that you insert data into a cloudSQL instance.