MySQL default value to integer fields with LOAD DATA from CSV file import with dynamic fields

I was confronted with a problem while loading data from file to the MySQL table. The table have some Integer and Varchar fields and the data is loaded from CSV. Normally the data loading process goes well, but in case the data does not exist for a Integer field, the import breaks and show error like : 

Incorrect integer value: '' for column 'ID' at row 1

The fields are dynamic that I can determine the fields in run-time only. 

So, I devised a solution for this.

I have created the query such that for all the fields with Integer type, I add a '0' with it. 

Here are the steps.

<!--- STEP 1 - GET THE FIELDS IN THIS TABLE --->

<cfquery name="qryTblDefinition" datasource="<datasource>">

DESC <table_name>

</cfquery>

 

<!--- STEP 2 - GET THE FIELDS WHICH ARE INT --->

<cfquery name="qryIntKeyList" dbtype="query">

SELECT

FIELD

FROM

qryTblDefinition

WHERE

TYPE like 'int%'

</cfquery>

<!--- STEP 3 - CONVERT THE FIELDS TO A LIST --->

<cfset intHeaders = valueList(qryIntKeyList.Field) />

Now, you have a list of fields which are of type INT.

<!--- STEP 4 - PREFIX AND @ FOR ALL NUMERIC FIELDS --->

<cfloop list="#intHeaders#" index="i">

<cfif listFindNoCase(colHeaders,i)>

<cfset colHeaders = Replace(colHeaders,'#i#','@#i#') />

</cfif>

</cfloop>

<!--- STEP 5 --->

<CFQUERY NAME="add_records" DATASOURCE="<datasource>" result="qryResult">

        LOAD DATA INFILE <cfqueryparam value="<file path>" cfsqltype="cf_sql_varchar">

INTO TABLE <table>

FIELDS TERMINATED BY '\t'

OPTIONALLY ENCLOSED BY '"'

lines terminated by '\r\n'

        IGNORE 1 LINES  

(<colHeaders>)

<!--- IF FIELDS ARE AVAILABLE IN THE INTEGER LIST, THEN CREATE A 'SET' STATEMENT FOR EACH --->

<cfif listLen(intFields)>

        <cfloop list="#intFields#" index="i">

<cfset intFields = Replace(intFields,i,"#i#=@#i#+0") />

</cfloop>

  SET #intFields#

</cfif>;

</cfquery>

So, what I have done in STEP 4 is that we have prefixed an '@' for all field names of type INT so that they are now variables.

In Step 5, we are creating the query with the new field names and additionally a SET statement for these variables. In the SET statement, I am adding a 0 to the value so that if the value is a valid integer, the value does not change, but if it is not, then it will be 0.

An example of the output for STEP 5 will be as follows :

LOAD DATA INFILE 'F:\\data.txt' INTO TABLE users

  FIELDS TERMINATED BY ',' ENCLOSED BY '"'

  LINES TERMINATED BY '\r\n'

  IGNORE 1 LINES

(@ID,Name,Address)

SET  ID = @ID+0;

Here, the field ID is of type Integer. I have added a 0 to the ID in the SET statement. Similarly, any number of fields can be used.

If this code help you, please comment.