2

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!

Guy
  • 876
  • 1
  • 10
  • 28
  • Have you looked into this answer? http://stackoverflow.com/questions/13814775/google-cloud-sql-alternative-to-load-data-infile – Mario Sep 24 '14 at 06:43
  • It doesn't address the problem. I've updated my post with more details. – Guy Sep 24 '14 at 08:51
  • 1
    You could build a GAE module that takes those CSV files and converts them to import SQL statements, and another module will upload them to the Cloud SQL instances. This all should be done asynchronously. Why do you think the other post is not related? I see the relationship. – Mario Sep 24 '14 at 09:16
  • We're both trying to import data to Cloud SQL, however, I need a solution that multiple end users are able to utilize within the app itself. I guess I'll just have them upload the CSV and parse it all with PHP some how. Thanks for the ideas. – Guy Sep 24 '14 at 09:51
  • Exactly, you'll have to take care of that but my suggestion is to to do it in a module or as tasks, not directly in the customer HTTP request to the GAE front-end. – Mario Sep 24 '14 at 09:54
  • 2
    Have you tried `LOAD DATA LOCAL INFILE ...` ([docs](http://dev.mysql.com/doc/refman/5.5/en/load-data.html))? – Razvan Musaloiu-E. Sep 25 '14 at 20:24
  • Great idea! It seems `LOAD DATA LOCAL INFILE` is supported by Cloud SQL. I'll give it a go! – Guy Sep 26 '14 at 05:49

2 Answers2

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().

Dan
  • 69
  • 1
  • 8
0

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.

Tex
  • 33
  • 6