CSV to MySQL – Advanced settings
When you upload a CSV file, it is parsed and displays the default options as shown above.
Seq ID : This is only for visual purpose and not used anywhere else.
Source Column: This field is derived from the first row of the CSV file. The uploaded CSV file is expected to have column names in the first row
Target Column : By default this field is cleaned up copy of the Source Column field. If you need, you can mouse click on the field and edit the target column name
Type : After scanning the first row, it assigns a default column type. Some times if the number data contains comma or other currency symbols it may not be able to decide correctly so it assigns them as ‘Char’ type. You can click and change the type. During upload to the database it will strip out all non-numeric characters.
Length: This field decides what length to assign to the target column
decimal: This is applicable to numeric columns where you need to assign precision for the decimals
Date Format: This is for future use. Ignore this field for now. The default date format is ‘YYYY-MM-DD’ so if today is 31st Jan 2017 then it expects the value in the date column to be as 2017-01-31.
Index: Currently it has two values to select from ‘Primary’ and ‘None’. By default all columns are assigned ‘None’ index. You can assign a column to be a primary index. You can assign more than one column to be part of the primary index. Primary index is useful for updates. When you try to load a new data that might contain updates to existing data in the table then this primary key definition helps identifying the correct rows and updates them.
Update Flag: This flag is useful in conjunction with the primary key. When you have new data to be loaded, you can selectively update only the columns you flag them. All columns with Update Flag checked (Y) will be updated when there are changes detected. By default the parser assigns the update flag checked for all numerical columns.
Transpose Flag: This is for future use. Ignore this setting for now.
Apply Function : This flag determines if you want to apply any function on the source column.
Function String: This is a free flow text field where you can provide either PHP function or MySQL function. The function is applied only if ‘Apply Function’ is checked. It is seeded with some popular date formats for MySQL Once you type the letter ‘s’ it shows the list of available formulas
Consider a CSV file containing a date column with the following format
Check date is mm/dd/YYYY format. So to match this, we use the MySQL equivalent formatter as ‘%m/%d/%Y’ After selecting, the function string gets populated as shown below
Since this is a MySQL function, we need to wrap it with a double quotes. Alternatively you could use a PHP function to do the same job but then we don’t need to wrap it in double quotes.
The advantage of using MySQL function is that all the transformation happens during the insert operation into the Target Table. If you use PHP function then the function is applied as each row is scanned from the CSV file. So by the time the insert statement is prepared the cell value is already converted into the required format.
The function string provides lot of flexibility but you need to use it with caution. If there is any syntax issue with function string then the mapping won’t work.