Update a MySQL table from CSV files
How to update a MySQL table from CSV files
The scenario for this particular automate task was that we had text, CSV reports that we needed to import in to a MySQL database and needed to clear the particular table of all records before we started a new import. In order for this task to work we had to share the directory where the reports were at and need the task to loop through all of the files in the directory.
The task steps are as follows:
Create a variable named "var_FilePath" with an initial value of "\\\\192.168.1.1\\sharename\\" (Notice the double backslash. It's a small nuance of MySQL that a backslash is a command character so you need a double backslash to represent a single backslash)
Create a variable named "var_FullPath"
Establish SQL Connection using...
Here you use the built-in ODBC connection browser to connect to the MySQL database. IMPORTANT: You need to install the MySQL for Windows driver to be able to make this connection. You can get the driver at: http://dev.mysql.com/downloads/connector/odbc
Execute the SQL statement: "TRUNCATE TABLE tablename"
Why use the TRUNCATE TABLE statement rather than UPDATE TABLE? There's a great debate in online forums about the merits of both but in this instance we wanted a clean table so the TRUNCATE statement was more efficient.
Loop through files in directory "\\192.168.1.1\sharename" Do not include the path in the variable. Create and populate dataset "ds_files" with details of the file(s).
Set variable var_Fullpath to value "%var_Filepath & ds_Files.Name%"
So what is this about? We need the MySQL statement to grab the files from the shared directory but we needed the value to be in the right format. So this step concatenates the variable var_FilePath and the file name of the report so that we get the final value of \\\\192.168.1.1.\\share\\report.csv
Execute SQL statement: "LOAD DATA LOCAL INFILE '%var_FullPath%' INTO TABLE tablename COLUMNS TERMINATED BY '|' LINES TERMINATED BY '\n' IGNORE 1 LINES (COLUMN1,COLUMN2,COLUMN3);".
Close SQL Connection