title: Importing Excel Spreadsheet Into SQL Database (Coldfusion 9)
body: <p>I'm writing a script to import a large excel spreadhseet into an SQL Server 2008 database. Everything is working fine except for one minor detail:</p>
  <p>If a cell in the sheet has multiple options (like a <code>&lt;select&gt;</code> dropdown menu), only the selected option gets grabbed. I want to grab every possible option the cell has, not just the one being used (See my SQL query for why)</p>
  <p>I have searched google and S/O for answers but I have not encountered a solution for this particular situation. <a href="https://web.archive.org/web/20161122041233/http://help.adobe.com:80/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec1a60c-7ffc.html#WSaad0660b0585370c-3b13488c12d7e9aabe0-8000" rel="nofollow noreferrer">Here is a link</a> to the Spreadsheet Functions I am using.</p>
  <p>I cannot show you the excel sheet, but it is safe to assume the traversing of the sheet is correct (I have tested it).</p>
  <p>Here is my code:</p>
  <pre><code>&lt;cfspreadsheet action=&quot;read&quot; src=&quot;spreadsheet.xlsx&quot; name=&quot;sheet&quot;&gt;
  &lt;cfoutput&gt;
      #sheet.rowcount-3#
  &lt;cfloop from=&quot;2&quot; to=&quot;#sheet.rowcount-3#&quot; index=&quot;row&quot;&gt;
      &lt;cfquery datasource=&quot;Questions&quot; result=&quot;rState&quot;&gt;
          INSERT INTO States
          (
              State,
              StateAbbr
          )
          VALUES 
          (
              &lt;cfqueryparam cfsqltype=&quot;cf_sql_varchar&quot; value=&quot;#SpreadsheetGetCellValue(sheet,row,1)#&quot;&gt;,
              &lt;cfqueryparam cfsqltype=&quot;cf_sql_varchar&quot; value=&quot;#SpreadsheetGetCellValue(sheet,row,2)#&quot;&gt;
          )
      &lt;/cfquery&gt;
      #SpreadsheetGetCellValue(sheet,row,1)#&lt;br /&gt;
      #SpreadsheetGetCellValue(sheet,row,2)#&lt;br /&gt;
      &lt;cfloop from=&quot;3&quot; to=&quot;15&quot; index=&quot;col&quot;&gt; &lt;!--- multi row selection (edit based on excel sheet col relationship) ---&gt;
          &lt;cfif SpreadsheetGetCellValue(sheet,row,col) EQ &quot;&quot;&gt;
              &lt;cfset SpreadsheetSetCellValue(sheet,&quot;N/A&quot;,row,col) /&gt;
          &lt;/cfif&gt;
          &lt;cfquery datasource=&quot;Questions&quot; result=&quot;rResponse&quot;&gt;
              IF NOT EXISTS 
              (
                  SELECT Response
                  FROM Responses
                  WHERE Response=&lt;cfqueryparam cfsqltype=&quot;cf_sql_nvarchar&quot; value=&quot;#SpreadsheetGetCellValue(sheet,row,col)#&quot;&gt;
              )
              INSERT INTO Responses
              (
                  Response
              )
              VALUES
              (
                  &lt;cfqueryparam cfsqltype=&quot;cf_sql_nvarchar&quot; value=&quot;#SpreadsheetGetCellValue(sheet,row,col)#&quot;&gt;
              )
          &lt;/cfquery&gt;
          #row#X#col#&lt;br /&gt;
          #SpreadsheetGetCellValue(sheet,row,col)#&lt;br /&gt;
      &lt;/cfloop&gt;
  &lt;/cfloop&gt;
  &lt;/cfoutput&gt;
  </code></pre>
  <p>Edit: I cannot show the excel sheet I'm using, but I recreated the list so you know what sort of drop down cell I am talking about. NOTE: The sheet I'm using, the options list was NOT built with cells, the creator used static values in the validation clause!!!</p>
  <p><a href="https://i.stack.imgur.com/wKwBG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wKwBG.png" alt="" /></a></p>