CODEWITHSUNDEEP
arrayslistcoldfusion
espresso_coffee
espresso_coffee

Reputation: 6110

ArrayToList() ColdFusion?

I have a query that I'm looping through and appending to array. After loop is done I use ArrayToList() with tab delimiter. Problem is that between each row I have an extra empty column. My guess is that command converts last comma to tab delimiter. I still could not find the way to prevent that. Here is my code:

<cfspreadsheet action="read" src="#urlPath#\Roster.xlsx" query="excelData" headerrow="1">

<cfset metadata = getMetaData(excelData)>
<cfset colList = "">

<cfloop index="col" array="#metadata#">
    <cfset colList = listAppend(colList, col.name)>
</cfloop>

<cfset buffer=ArrayNew(1)>
<cfset crlf=chr(13)&chr(10)/>

<cfoutput query="excelData">
    <cfloop index="i" list="#colList#">
        <cfset ArrayAppend(buffer,excelData[i][currentRow])>
    </cfloop>

    //prevents blank row to be created on the end
    <cfif excelData.currentrow eq excelData.recordcount>
        <cfset ArrayAppend(buffer,"")>
    <cfelse>
        <cfset ArrayAppend(buffer,crlf)>
    </cfif> 
</cfoutput>

//setting tab delimiter 
<cfset test = arrayToList(buffer,chr(9))/>

<cfscript> 
    FileWrite("#urlPath#\Test_Text.txt", "#test#"); 
</cfscript>

Here is the output after I run this code:

enter image description here

And here is what I need my output to look like:

enter image description here

I choose ArrayAppend() because it's much faster than the ListAppend(), if anyone can help whit this problem please let me know. Thank you.

If I do cfdump of buffer array this is the output:

array
1   LAST_NAME
2   FIRST_NAME
3   BIRTH_DATE 
4   
5   Milos
6   TestOne
7   11/16/97
8

Looks like between each row is the blank space row 4 and 8. This is how I fixed the date format issue:

<cfoutput query="excelData">
        <cfset thisRow = []>
        <cfset count = 1>
        <cfloop index="i" list="#colList#">
            <cfif count EQ 3 AND excelData[i][currentRow] NEQ "BIRTH_DATE">
                <cfset ArrayAppend(thisRow,dateFormat(excelData[i][currentRow],"mm/dd/yyyy"))>
            <cfelse>
                <cfset ArrayAppend(thisRow,excelData[i][currentRow])>
            </cfif>
            <cfset count++>
        </cfloop>
        <cfset ArrayAppend(buffer,arrayToList(thisRow, chr(9)))>
    </cfoutput>

Upvotes: 1

Views: 1586

Answers (1)

Kevin B
Kevin B

Reputation: 95065

Your array is, for example, 

['Doe', 'John', '1/1/1900',crlf,'Doe','Jane','1/1/1900']

If you put a tab between each of those values, there will be a tab both before and after the crlf.

I would suggest creating a single string for each line in the sheet to insert into buffer and then a arrayToList with crlf as the delimiter, also eliminating the need for the if statement.

<cfspreadsheet action="read" src="#urlPath#\Roster.xlsx" query="excelData" headerrow="1">

<cfset metadata = getMetaData(excelData)>
<cfset colList = "">

<cfloop index="col" array="#metadata#">
    <cfset colList = listAppend(colList, col.name)>
</cfloop>

<cfset buffer=ArrayNew(1)>
<cfset crlf=chr(13)&chr(10)/>

<cfoutput query="excelData">
    <cfset thisRow = []>
    <cfloop index="i" list="#colList#">
        <cfset ArrayAppend(thisRow,excelData[i][currentRow])>
    </cfloop>
    <cfset ArrayAppend(buffer, arrayToList(thisRow,chr(9)))>
</cfoutput>

// joining to list on crlf
<cfset test = arrayToList(buffer, crlf)/>

<cfscript> 
    FileWrite("#urlPath#\Test_Text.txt", "#test#"); 
</cfscript>

And just because: (untested)

<cfspreadsheet action="read" src="#urlPath#\Roster.xlsx" query="excelData" headerrow="1">

<cfscript>
    metadata = getMetaData(excelData);
    buffer = [];
    crlf = chr(13)&chr(10);

    for (data in excelData) {
        thisRow = ArrayMap(metadata, function (col,i) {
            return data[col];
        });
        ArrayAppend(buffer, arrayToList(thisRow,chr(9)));
    }    

    test = arrayToList(buffer, crlf);

    FileWrite("#urlPath#\Test_Text.txt", "#test#"); 
</cfscript>

Upvotes: 4

Related Questions