Generate Imports Scripts

Use the Import Code Generator dialog to create either a simple LOAD DATA INFILE statement or a PHP console script to load delimited files into the database.

  1. LOAD DATA INFILE: Generates a LOAD DATA INFILE MySQL statement based on a field/column mapping. Remember that the LOAD DATA INFILE statement can only load a file from a directory that the database service can access.
  2. PHP Console Script: The generated PHP script is based on three common PHP functions: file_get_contents(), explode(), and str_getcsv().

Read about the features of the LOAD DATA INFILE on the MySQL site. Read about file_get_contents(), explode(), str_getcsv() on the php.net site. Learning the capabilities of these commands and functions will allow you to tweak the generated code to suit your requirements.

  • Select 'Import Code Generator' from the 'Tools' menu to open the Import Code Generator dialog. Generate Import Source
  • Set the 'Source File' path and click 'Analyze Source File' to attempt to get the column names of the delimited file. These column names will be listed in the 'Mapping' tab.

    Analyze Source File will use the Delimiter and Enclosure fields, but this command may not divide up the file in exactly the same way the generated PHP source will. Take note of any discrepancies in the mapping and fix the generated code with a program editor after you have saved the PHP files to disk.

  • The 'Iterate Rows' option enables the interval controls beneath. This option will loop the process so that it can load multiple files that have a date embedded in the filename. The loop iterates from the 'Intervals Back' integer to the 'Intervals Forward' integer. This is applied to the current date. So, for example, -2 would be two days ago, and 1 would be tomorrow. After the date math is applied to the current day, the date is transformed into a string based on the 'Date Format' field. The result is then replaced into the 'Source File' placeholder '{$dt}' before loading with the file_get_contents() function call. The following is an example URL with the date placeholder: 
    http://mis.nyiso.com/public/csv/damlbmp/{$dt}damlbmp_zone.csv
    Generate Import Target
  • The target is simply the table to which you are loading data. The columns of the selected target will appear in the right listbox in the 'Mapping' tab. Generate Import Mapping
  • Select a row in the Source file (left) listbox. Select a row in the Target table (right) listbox. Then click 'Map' to assign the source field to the database column. You can also double-click the target listbox row to map. Once you have mapped the source to the target, you can click 'Generate' to create the script files. Generate Import Output
  • Notice that two files are created. The 'generated.php' file is an include that contains the database credentials. You may want to change the user and password with a text editor at a later time.
Back to Table of Contents.