This legacy importer uses ColdFusion scripts. It has been replaced by new functionality in the API Server. See Data Import from CSV for the new documentation.
importData(ctx, tablename, columns, batchsize, csvfile, label, rowfilter, mappings, appendTo, allowAppend, progresscallback, progressrowcount, beforepublishcallback)
Imports data from a CSV file into a data import table. If you are importing data into a table that already exists, it is added to the table as additional rows, with the
It is possible to append data to an existing import. The existing data must have been imported with
When data is imported, a corresponding row is added to the
Parameters
| Name | Type | Description |
|---|---|---|
| ctx | Struct, required | Database details, see above |
| tablename | String, required | The table to import the data into |
| columns | Array of objects, required | Each item in the array is an object representing a column. This follows the same format as |
| batchsize | Integer, optional | The number of rows to insert in each database query. Defaults to 50 |
| csvfile | String, required | The fully qualified path to the CSV file |
| label | String, optional | A label for this import. Defaults to the csvfile value if not set |
| rowfilter | Function, optional | The name of a callback function which decides whether rows should be imported into the table. The function is called before each row is processed. It must return true for the row to be imported. See below |
| mappings | Array of objects, required | An array of mappings. These set which data is imported into which field |
| mappings[n].csvCol | Integer, optional | The index number for the column in the source CSV file. Either csvCol or a formatter must be supplied |
| mappings[n].tableCol | String, required | The name of the column to insert this data into |
| mappings[n].nullvalue | [any], optional | If the formatted data value matches this value, then a null will be inserted into the database table |
| mappings[n].trim | Boolean, optional | Trim leading and trailing whitespace from the value. Defaults to true |
| mappings[n].formatters | Array of functions, optional | An array of callback functions that format a value before it is inserted into the database table. The functions are called in order. Each function must return a formatted version of the data value. See below. Either csvCol or a formatter must be supplied |
| appendTo | Integer, optional | The version number of an open import to append this data to. Open imports must have previously been created with allowAppend set as true |
| allowAppend | Boolean, optional | If true the imported data will not be published and may have data appended to it in the future (ie future imports will have the same version number as this import). Defaults to false |
| progresscallback | Function, optional | A callback function which is told how many rows have been processed. The function must not return a value. It takes two augments: nRead (Integer) the number of rows that have been read from the CSV file; nWritten (Integer) the number of rows that have been written to the database |
| progressrowcount | Integer, optional | The number of rows after which the progresscallback function is called. Defaults to 250 |
| beforepublishcallback | Function, optional | A callback function which is called after the data import, but before this version of the import is marked as complete. The function must not return a value. It takes two augments: tablename (String) the name of the table that the data has been imported into; version (Integer) the version number of the import which has just occurred |
Row Filters
Row filters give you control over the CSV rows that are imported.
This first example skips the first row in the file (useful if the first row is a header).
<cffunction name="skipheader" returntype="Boolean">
<cfargument name="row" type="Array" required="yes">
<cfargument name="rowcount" type="Numeric" required="yes">
<!--- skip the first row --->
<cfif ARGUMENTS.rowcount EQ 1>
<cfreturn false>
</cfif>
<cfreturn true>
</cffunction>
This filter skips the first row, then checks the values of NAME and MAGNITUDE, which are variables that correspond to columns in the csv file.
<cffunction name="brightstar" returntype="Boolean">
<cfargument name="row" type="Array" required="yes">
<cfargument name="rowcount" type="Numeric" required="yes">
<!--- skip the first row --->
<cfif ARGUMENTS.rowcount EQ 1>
<cfreturn false>
</cfif>
<!--- do not import a row if the star is nameless and dim (the higher the number the dimmer the star) --->
<cfif len(trim(ARGUMENTS.row[NAME])) EQ 0 AND ARGUMENTS.row[MAGNITUDE] GT 8.0>
<cfreturn false>
</cfif>
<cfreturn true>
</cffunction>
Mapping Value Formatters
The formatters included in your mappings manipulate values in a column as they are imported.
This example checks for the value "Tim". If it's found it will be changed to "Tom". If not, the original value is returned.
<cffunction name="myformatter" returntype="Any">
<cfargument name="row" type="Array" required="yes">
<cfargument name="val" type="Any" required="yes">
<cfif ARGUMENTS.val EQ "Tim">
<cfreturn "Tom">
</cfif>
<cfreturn ARGUMENTS.val>
</cffunction>
This example is used on a CSV column called FIRST_NAME. It's value is mapped to a table column called FirstName. The formatter maps the first letter of the FIRST_NAME column to a new table column called Initial.
<cfset csv2table=[
{"csvCol":FIRST_NAME, "tableCol":"FirstName"},
{"csvCol":FIRST_NAME, "tableCol":"Initial", "formatters":[getinitial]},
{"csvCol":LAST_NAME, "tableCol":"LastName"},
{"csvCol":ADDRESS, "tableCol":"Address"},
{"csvCol":CITY, "tableCol":"City"},
{"csvCol":COUNTY, "tableCol":"County"},
{"csvCol":POSTCODE, "tableCol":"PostCode"}
]>
<cffunction name="getinitial" returntype="Any">
<cfargument name="row" type="Array" required="yes">
<cfargument name="val" type="Any" required="yes">
<cfif len(ARGUMENTS.val) GT 1>
<cfreturn Left(ARGUMENTS.val,1)>
</cfif>
<cfreturn "">
</cffunction>
Returns
| Name | Type | Description |
|---|---|---|
| version | String | The version number of the import |
| label | String | The label of the import |
| nRead | Integer | The number of lines read from the CSV |
| nWritten | integer | The number of rows written to the database table |
Example
This simplified example assumes the database table already exists.
<cfset ds=APPLICATION.datasource>
<cfset dt=APPLICATION.databasetype>
<cfset tablename="timstable">
<cfset csvfile=getDirectoryFromPath(getCurrentTemplatePath()) & "EXAMPLES/dataimport_names.csv">
<!--- get a data importer --->
<cfmodule template="/icm/admin/dataimport/importer_v1.cfm" name="di" datasource=#ds# databasetype=#dt#>
<!--- set some readable names for the csv columns to use later --->
<cfset FIRST_NAME = 1>
<cfset LAST_NAME = 2>
<cfset ADDRESS = 4>
<cfset CITY = 5>
<cfset COUNTY = 6>
<cfset POSTCODE = 7>
<!--- the database table columns --->
<cfset tableColumns=[
{"name":"FirstName", "type":"VARCHAR", "size":60},
{"name":"LastName", "type":"VARCHAR", "size":60},
{"name":"Address", "type":"VARCHAR", "size":240},
{"name":"City", "type":"VARCHAR", "size":60},
{"name":"County", "type":"VARCHAR", "size":60},
{"name":"PostCode", "type":"VARCHAR", "size":12}
]>
<!--- csv to database table mappings --->
<cfset csv2table=[
{"csvCol":FIRST_NAME, "tableCol":"FirstName"},
{"csvCol":LAST_NAME, "tableCol":"LastName"},
{"csvCol":ADDRESS, "tableCol":"Address"},
{"csvCol":CITY, "tableCol":"City"},
{"csvCol":COUNTY, "tableCol":"County"},
{"csvCol":POSTCODE, "tableCol":"PostCode"}
]>
<!--- import some data --->
<cfset imported = di.importData(ctx=#di#,
tablename=#tablename#,
columns=#tableColumns#,
csvfile=#csvfile#,
mappings=#csv2table#
)>
<cfoutput>...Done. Version:[#imported.version#]</cfoutput><cfflush>
Share this page
