CODEWITHSUNDEEP
excelcsvcoldfusion
espresso_coffee
espresso_coffee

Reputation: 6110

How to replace comma in .xlsx file in ColdFusion?

I have excel file that can contain commas in some fields. This can cause problem if I want to use cfspreadsheet to convert my file to csv. I was wondering if there is the way to replace or convert all commas with the \. After I replace all commas then I will be able to use cfspreadsheet to create csv. Here is my code how I read my file:

<cfspreadsheet action = "read" format="csv" src="filePath\myFile.xlsx" name="csvvar">

If anyone can help with this problem please let me know. Thank you.

Upvotes: 1

Views: 528

Answers (1)

BKBK
BKBK

Reputation: 484

Convert from Excel to query. Then, in the cell data of each row, replace "," by "\". Something like this

<cfspreadsheet 
action = "read" 
src="filePath\myFile.xlsx" 
query="excelquery" 
sheet="1">

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

<cfset columns = arraynew(1)>
  <!--- Store the list of column names as an array --->
<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]")>

    <!--- Replace , by \ in each cell --->
    <cfset cellData = replace(cellData, ",", "\", "all")>

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

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

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

Upvotes: 1

Related Questions