Examples

Stars

There's an example importer and CSV file in the iCM custom folder, generally accessible using iCM's file manager.

EXAMPLES\dataimport_stars.csv has over 87,000 rows describing the names, positions, distance from Earth and magnitude of stars in the sky.

EXAMPLES\dataimport_stars.cfm imports the data from the CSV using a range of filters and formatters. It indexes the import in SOLR as well as deleting old table and index data if the import runs again. If you are using any of the more advanced importer functions we advise taking a look at this example first.

Contacts

This example is simpler than stars, but still demonstrates creating a table, importing data, setting up mappings and applying a filter. It imagines a CSV file that holds people's names and addresses and uses a filter to skip the first row.

<!--- set up the data importer with database information --->
<cfset ds=APPLICATION.datasource>
<cfset dt=APPLICATION.databasetype>
<cfmodule template="/icm/admin/dataimport/importer_v1.cfm" name="di" datasource=#ds# databasetype=#dt#>
<!--- the table we want to create --->
<cfset tablename="contacts">
<!--- the location of the CSV file (it's in the same directory as this importer) --->
<cfset csvfile=getDirectoryFromPath(getCurrentTemplatePath()) & "dataimport_contacts.csv">
<!--- create some variables which are easier to use than the csv column index --->
<cfset FIRST_NAME = 1>
<cfset LAST_NAME = 2>
<cfset ADDRESS = 4>
<cfset CITY = 5>
<cfset COUNTY = 6>
<cfset POSTCODE = 7>
<!--- 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"}
]>
<!--- database columns to create --->
<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}
]>
<!--- create the destination table if necessary --->
<cfif NOT di.tableExists(ctx=#di#, tablename=#tablename#)>
    <cfset di.makeTable(ctx=#di#, tablename=#tablename#, columns=#tableColumns#)>
</cfif>
<cfoutput>Importing Data<br></cfoutput><cfflush>
<!--- import csv data into the table --->
<cfset imported = di.importData(ctx=#di#,
        tablename=#tablename#,
        columns=#tableColumns#,
        csvfile=#csvfile#,
        label="seminar",
        rowfilter=skipheader,    <!--- do not import the header row --->
        mappings=#csv2table#
)>
<cfoutput>...Done. Version:[#imported.version#] Label:[#imported.label#] Read:[#imported.nRead#] Written:[#imported.nWritten#]<br></cfoutput><cfflush>
<!--- custom row filter --->
<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>