Automatic Importing from text files to a MySQL database using Automate by Network Automation
I am a raving fan of Network Automation's products (www.networkautomation.com). Automate is great for somebody such as myself who lacks a background in programming but is efficient at script writing. With Automate I am able to automate (as the product's namesake) many tasks such as FTP, cleaning up file directories, downloading from a website, run reports, analyze reports, trigger email alerts, etc. all without the need to write lengthy scripts.
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 "\\\\19f220.127.116.11\\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)|
|Step 2||Establish SQL Connection using...|
|NOTE: 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|
|Step 3||Create a variable named "var_FullPath"|
|Step 4||Execute the SQL statement: "TRUNCATE TABLE tablename"|
|NOTE: 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.|
|Step 5||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).|
|Step 6||Set variable var_Fullpath to value "%var_Filepath & ds_Files.Name%"|
|NOTE: 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|
|Step 6||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);".|
|Step 8||End Loop|
|Step 9||Close SQL Connection|
And there you have it. In 9 easy steps I'm able to load 20 reports in to a table in under a minute!