Importing data from a spreadsheet to a database table using ColdFusion

Friday 23 December 2011

I'm currently working on a project where I need to import a list of products from a spreadsheet into a database table. Here is the ColdFusion script I have writted to complete the task.

<!--- read the spreadsheet data into a query object --->
<cfspreadsheet action="read" src="#ExpandPath( './data.xlsx' )#" query="importdata" headerrow="1" />

<!--- create a variable to store the codes of products that could not be imported --->
<cfset failedimports = "" />

<!--- loop through the query starting with the first row containing data (row 2) --->
<cfloop query="importdata" startrow="2">
  <!--- check row contains valid data (all fields must contain a value and price must be numeric) --->
  <cfif !Len( product_title ) || !Len( product_code ) || !IsNumeric( product_price )>
    <!--- the row data is not valid so add it to our list of failed imports --->
    <cfset failedimports = ListAppend( failedimports, product_code ) />
  <cfelse>
    <cftry>
      <!--- insert the product into the database using REPLACE INTO to ensure existing products with the same code (which must be a unique key) are not duplicated --->
      <cfquery datasource="mydatasource" result="foobar">
        REPLACE INTO Products
        (
          product_title
          , product_code
          , product_price
        )
        VALUES
        (
          <cfqueryparam cfsqltype="cf_sql_varchar" value="#product_title#" />
          , <cfqueryparam cfsqltype="cf_sql_varchar" value="#product_code#" />
          , <cfqueryparam cfsqltype="cf_sql_double" value="#Val( product_price )#" />
        )
      </cfquery>

      <cfcatch type="any">
        <!--- catch any products that could not be imported and add them to our list of failed imports --->
        <cfset failedimports = ListAppend( failedimports, product_code) />
      </cfcatch>
    </cftry>
  </cfif>
</cfloop>

<cfoutput>
  <!--- display the products that could not be imported --->
  <h1>Failed Imports</h1>

  <cfif ListLen( failedimports )>
    <p>Oops! #ListLen( failedimports )# products could not be imported.</p>

    <cfloop list="#failedimports#" index="index">
      #index#<br />
    </cfloop>
  <cfelse>
    <p>No products failed to be imported.</p>
  </cfif>
</cfoutput>

Download Sample Code

Comments

When I import data from Excel, I intentionally duplicate the header row so that all data is imported as a "VarChar" datatype. I've found that if this is not done and the first couple rows have some empty cells, new queries datatypes aren't always correctly identified. (Coldfusion is typeless and can automatically identify dates & numbers anyhow.) I then start the loop at first row containing the actual data. I've had to also add isDefined() checks before the loop because sometimes the client forgets to add certain columns to the spreadsheet. This enables the script to determine if optional columns exist and set an optional user-defined default value if they don't.

Regarding performance, I'd recommend converting the "failedimports" variable into an array. Working with an array is both faster and safer. You never know if/when a product code will intentionally or accidentally have a comma. (I've had some admin-only scripts that took over 2 minutes to execute and converting lists to arrays reduced the time considerably.)