Odd issue with cfspreadsheet and excel

[Patrick GH]

I have code that reads an excel from my server and then converts to a query object and the loops through and inserts into an MSSQL table.

<cfspreadsheet action="read" query="myExcelData" src="#path#" headerrow="1" rows="2-50000" />

It works great.

But only after I get the excel file from the server, open and save it, and then FTP back to the server.

It works great.

The problem is my client FTP's the excel file to the server using WINSCP.
When I try and run the script on those files the client FTPs I don't get any records.
It's like it reads the file but returns 0 records.
I don't get an error message.

Then I get the excel file from the server, open and save it, and then FTP back to the server.

It works great.

Any ideas?

Very odd situation

[BKBK · Accepted solution]

Could you share the code that you use to get the file from the server?

Some suggestions.

1) Is the value of Path in that code the same as in the cfspreadsheet tag?

You could improve your code's design by testing for the existence of the file. For example,

test

<cfif fileExists(path)>

    <cfspreadsheet action="read" query="myExcelData" src="#path#" headerrow="1" rows="2-50000" />

</cfif>

2) Your code might be reading a different sheet. So test with

<cfspreadsheet action="read" sheet ="1" />

<cfspreadsheet action="read" sheet ="2" />

3) Intervening manually with FTP feels clunky. What about doing that test automatically, with code instead. Something like,

<cfspreadsheet action="read" query="myExcelData" src="#path#">

<cflock name="XLSFileProcess" type="exclusive" timeout="20">

<cfspreadsheet action="write" query="myExcelData" src="#anotherPath#" overwrite="true">

</cflock>

<cflock name="XLSFileProcess" type="exclusive" timeout="20">

<cfspreadsheet action="read" query="myExcelData" src="#anotherPath#">

</cflock>