MySQL Import – How to Insert All CSV Files into a Table

What you want is nearly impossible, no one can know if you want to import '1' as a characters string, n decimal integer, a base-2 integer, an hex integer, a float, a blob, a "boolean", ...

You can try heuristic programs, or import them all as strings and run an analysis program like SELECT... ANALYZE TABLE();. It would be easier if the csv had the datatypes as an information.

Regarding programs that create the table (with chars) based on the list of columns, that is easier to find (GUI) and program.

Here it is one in bash as an example.

Having researched and tested out a few ideas I have now found a solution.

1) Access or Excel and utilise VBA to Save the CSV files to the FTP of where your website is. 2) After the SaveAs VBA statement I then had another line of code that fires open a web browser which loads a PHP page and executes a INSERT with LOAD DATA LOCAL INFILE...

Even with a 50mb CSV file the saving process over BT Infinity only takes a minute or so and the PHP runs in seconds.

I'm no natural born coder, I find solutions to what I need to achieve and here's what I have working...

Here's the PHP (ensure MyTable.csv is in the same folder as the PHP file else modify below):

<?php

$sql1 = "TRUNCATE TABLE MyTable";

$sql2 = "LOAD DATA LOCAL INFILE 'MyTable.csv'
INTO TABLE MyTable
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r'
(@date_time_variable, field1, field2, field3, field4)
SET datestamp = STR_TO_DATE(@date_time_variable, '%m/%d/%Y %H:%i');";

$con=mysqli_connect("mysql_server_address","mysql_username","mysql_password","mysql_table");

if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
};

$result1 = mysqli_query($con, $sql1);
$result2 = mysqli_query($con, $sql2);

if (mysqli_affected_rows($con) == 1) {
  $message = "The data was successfully added!";
} else {
  $message = "The user update failed: ";
  $message .= mysqli_error($con); 
};

echo $message;

mysqli_close($con); 

?>

The VBA I used to save to FTP:

ThisWorkbook.SaveAs "ftp://ftp_username:ftp_password@ftp_site_address/MyTable.csv"

Thanks for the responses to the original question to those that tried to help but as you can see I needed a solution that required no extra input from the user, one button does all.

The only thing you will need to ensure is that the MySQL server you are scripting to allows the use of LOAD DATA LOCAL INFILE as some restrict this on shared servers. Also take note that you'll have to constantly have FTP access enabled or enabled from your IP Address as many hosts like this locked down these days.

Hope this helps someone given the fact it wasn't easily answered.