title: Extract data from excel and create a custom CSV - ColdFusion
body: <p>I am receiving excel files from our client and I need to create a csv file from them. My issue is that the values in the excel files do not match our standards to create the csv file just by copying it. I need to get the values and put them in the correct order while I'm creating the file. I read through the livedocs for CF but I couldn't find anything the worked.</p>
  
  <p>I was thinking to create a struct and pull the data out in it and then create the csv file based on my struct but I haven't done anything like this before and I am not sure if it is even possible. I also thought of using listGetAt instead of the struct but still haven't tried it.</p>
  
  <p>Anyone did something like this before? I'm trying to hardcode as less values as possible because I see this becoming a future problem if we get a second client with the same issue.</p>
  
  <p>Update (I changed my code around a lot the past few days so this is what I have at the moment)</p>
  
  <pre><code>&lt;cfset DataDirectory = "E:\testfolder\test.xlsx"&gt; 
  
  &lt;cfspreadsheet action="read" src="#DataDirectory#" excludeHeaderRow="true" headerrow="1"  query="queryData"&gt; 
  
   &lt;cfquery name="contact" datasource="#ds#"&gt;
          select ClientBrandID 
          from ClientBrands 
          where surveyReferralID IN
                                  (select surveyReferralID from clientAdmin where username = '#res#')
       &lt;/cfquery&gt;
  
  
      &lt;cfscript&gt;
          dataFile = structNew();
          StructInsert(datafile,"ClientBrandID",contact.ClientBrandID);
          StructInsert(datafile,"surveyType", queryData.surveyType);  
      &lt;/cfscript&gt;
   &lt;cfscript&gt; 
  
      ///We need an absolute path, so get the current directory path. 
      theFile=  "E:\testfolder\NewTest.csv";
      //Create a new Excel spreadsheet object and add the query data. 
      theSheet = SpreadsheetNew("PatientData"); 
  
      SpreadsheetAddRow(theSheet, "ClientBrandID,SurveyType,Location,ClientContactID,FirstName,LastName,HomePhone,WorkPhone,CellPhone,Email"); &lt;!---This is the header of the new CSV ---&gt;
      SpreadsheetAddRows(theSheet, "datafile.clientbrandid, datafile.surveytype"); &lt;!--- This is my latest failed attempt. Tried to pass in data from my struct, probably the quotes are the issue but haven't tried it without them ---&gt;
  &lt;/cfscript&gt; 
  
  &lt;!--- Write the spreadsheet to a file, replacing any existing file. ---&gt; 
  &lt;cfspreadsheet action="write" filename="#theFile#" format="csv" name="theSheet" overwrite=true &gt;
  </code></pre>
  
  <p>All of these operations will need to be in a cfloop in order to go through all of the files in my test folder. Right now I am hardcoding a single file to fix the issue at hand before I do anything else. Also, I am missing another loop in between that will need to go through all the values of the file. It should be something like <code>&lt;cfloop query='queryData'&gt;</code> which is the query that Im getting from cfspreadsheet.</p>