Extract data from excel and create a custom CSV - ColdFusion

Question

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.

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.

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.

Update (I changed my code around a lot the past few days so this is what I have at the moment)

<cfset DataDirectory = "E:\testfolder\test.xlsx"> 

<cfspreadsheet action="read" src="#DataDirectory#" excludeHeaderRow="true" headerrow="1"  query="queryData"> 

 <cfquery name="contact" datasource="#ds#">
        select ClientBrandID 
        from ClientBrands 
        where surveyReferralID IN
                                (select surveyReferralID from clientAdmin where username = '#res#')
     </cfquery>


    <cfscript>
        dataFile = structNew();
        StructInsert(datafile,"ClientBrandID",contact.ClientBrandID);
        StructInsert(datafile,"surveyType", queryData.surveyType);  
    </cfscript>
 <cfscript> 

    ///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"); <!---This is the header of the new CSV --->
    SpreadsheetAddRows(theSheet, "datafile.clientbrandid, datafile.surveytype"); <!--- 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 --->
</cfscript> 

<!--- Write the spreadsheet to a file, replacing any existing file. ---> 
<cfspreadsheet action="write" filename="#theFile#" format="csv" name="theSheet" overwrite=true >

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 <cfloop query='queryData'> which is the query that Im getting from cfspreadsheet.

Answer 1

You could make it accept any order but you'll still need to hard code the expected column headers into an array or something and considering it is just for your internal use I would just add the data back to the CSV in the order it's supposed to be in. Last time I made a spreadsheet from a query I did it like this. Perhaps it will help.

<cfscript>
    sMySpreadSheet = spreadsheetNew();
    column = 1;
    <!--- header row --->
    <!--- Remember what is expected from SpreadsheetSetCellValue --->
    <!--- SpreadsheetSetCellValue(spreadsheetObj, value, row, column) --->

    spreadsheetSetCellValue(sMySpreadSheet ,"First Name",1,column); column++;
    spreadsheetSetCellValue(sMySpreadSheet ,"Last Name",1,column); column++;
    spreadsheetSetCellValue(sMySpreadSheet ,"DOB",1,column); column++;
    spreadsheetSetCellValue(sMySpreadSheet ,"Phone Number",1,column); column++;
    spreadsheetSetCellValue(sMySpreadSheet ,"Number of Kids",1,column); column++;

    <!--- data rows --->
    <!--- if you're not using a header row with real titles you can just use the 
          default col_x 
          example: spreadsheetSetCellValue(sMySpreadSheet , queryData.col_1[q], q+1, column); column++; --->
    for(q=1; q LTE queryData.recordCount; q++){
        column = 1;
        spreadsheetSetCellValue(sMySpreadSheet , queryData.first[q], q+1, column); column++;
        spreadsheetSetCellValue(sMySpreadSheet , queryData.last[q], q+1, column); column++;
        spreadsheetSetCellValue(sMySpreadSheet , queryData.dob[q], q+1, column); column++;
        spreadsheetSetCellValue(sMySpreadSheet , queryData.phoneNumber[q], q+1, column); column++;
        spreadsheetSetCellValue(sMySpreadSheet , queryData.kidCount[q], q+1, column); 

    }
    <!--- make it purdy (optional) --->
    spreadsheetFormatRow(queryData, {fgcolor="light_cornflower_blue"},1);   
</cfscript>

If you want to simply add to a CSV you can do something like this (wrap your stuff in " if you have them qualified):

<cfsetting enableCFoutputOnly = "Yes">
<cfsaveContent variable = "myCSV">
<cfset newline = #chr(13)#&#chr(10)#>
<cfoutput>First Name,Last Name,DOB,Phone Number,Number of Kids#newline#</cfoutput>
  <cfoutput query="queryData">#queryData.first#,#queryData.last#,#queryData.dob#,#queryData.phoneNumber#,#kqueryData.idCount##newline#/cfoutput>
</cfsaveContent>
</cfsetting>
<cffile action = "append" file = "[your file]" output = "#myCSV#">

If cfsavecontent causes white spaces issues and cfsetting doesn't help, here is another alternative.

<cfset myCSV = "First Name,Last Name,DOB,Phone Number,Number of Kids#newline#">
<cfloop query="queryData">
  <cfset myCSV &= "#queryData.first#,#queryData.last#,#queryData.dob#,#queryData.phoneNumber#,#queryData.kidCount##newline#">
</cfloop>

Answer 2

Here are some suggestions based on the edited question. I'm making them even though the edited question does not make it clear what you are trying to achieve.

Regarding, "All of these operations will need to be in a cfloop in order to go through all of the files in my test folder", you can use cfdirectory on that folder. It will return a ColdFusion query through which you can loop.

The rest of your code looks more complicated than it needs to be. Reading the spreadsheet gives you a query object - so far so good.

Your database query, named contact looks supicious. You refer to a variable named res but it's not clear where that comes from. If it comes from the spreadsheet, then a value list might be more appropriate. By the way, use query parameters.

It seems you want your csv file to combine data from the spreadsheet and the database query. If that's the case, a query of queries might be useful.

If it were me, I'd use cffile action="write" to create my csv file. I would also surround each value with double quotes in case any of them contained commas.