Skip to main content
W
weezerboy
Inspiring
September 20, 2017
Answered

Odd issue with cfspreadsheet and excel

  • September 20, 2017
  • 1 reply
  • 556 views

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

This topic has been closed for replies.
Correct answer BKBK

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>

1 reply

BKBK
Community Expert
BKBKCommunity ExpertCorrect answer
Community Expert
September 22, 2017

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>

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices