CODEWITHSUNDEEP
excelcoldfusiontext-filesxlsxcfspreadsheet
espresso_coffee
espresso_coffee

Reputation: 6110

How to create tab delimited text file from cfquery?

I have created query using cfspreadsheet. Now I'm wondering if it's possible to convert query to tab delimited text file. This is my code to get the query:

<cfspreadsheet action="read" src="C:\myFiles\Records.xlsx" query="myQuery" headerrow="1">

Here is the list of my records from excel represented in cfquery:

    FIRST_NAME  LAST_NAME   DOB     GENDER  
1   FIRST_NAME  LAST_NAME   DOB     GENDER 
2   Mike    Johns   01/12/98    M   
3   Helen   Johns   2/2/01  F

I would like my text file to look like this if possible:

FIRST_NAME  LAST_NAME   DOB     GENDER 
Mike    Johns   01/12/98    M   
Helen   Johns   2/2/01  F

Tab delimiter between the values and \n to create newline. I have tried .csv but I could not get file organized as I showed above. Also if there is any other way to convert .xlsx file to .txt please let me know. I was looking xp_cmdshell commands but there is nothing that would be helpful in my case.

Here si the code that I used to get .csv file:

<cfspreadsheet action="read" format="csv" src="C:\myFiles\Records.xlsx" name="myCsv">

Then I used FileWrite() to get .txt file:

<cfscript> 
    FileWrite("C:\myFiles\Records.txt", "#myCsv#"); 
</cfscript>

Code above gave me tab delimited text file but one problem occured, if value in the field was empty those columns disappeared. For example if I did not have value in my GENDER column, that column was not created.

Mike    Johns   01/12/98

Upvotes: 1

Views: 1262

Answers (1)

BKBK
BKBK

Reputation: 484

You may see this literally as a question of converting a query result-set into a tab-delimited CSV file. That is, without the involvement of cfspreadsheet. You will get an answer by slightly modifying the answer I gave to a similar question from you:

<cfspreadsheet 
action = "read" 
src="C:\myFiles\Records.xlsx" 
query="excelquery" 
sheet="1">

<!--- Create CSV file in current directory--->
<cffile action="write" file="#expandpath('result.csv')#" output="">

<cfset columns = arraynew(1)>
<cfset columns = listToArray(excelquery.ColumnList)>

<cfoutput query="excelquery">
<cfset rowList = "">

<cfloop from="1" to="#arraylen(columns)#" index="n">
    <cfset colName = columns[n]>
    <cfset cellData = evaluate("#colName#[currentrow]")>

    <!--- Tab-separated row data --->
    <cfset rowList = listAppend(rowList,cellData,chr(9))>
</cfloop>

<!--- Place carriage-return at end of row --->
<cfset rowList = rowList & '<br>'>

<!--- Append row to CSV file --->
<cffile action="append" file="#expandpath('result.csv')#" output="#rowList#" >
</cfoutput>

Upvotes: 1

Related Questions