Highlighted

Odd issue with cfspreadsheet and excel

Participant ,
Sep 20, 2017

Copy link to clipboard

Copied

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

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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>

Views

327

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Odd issue with cfspreadsheet and excel

Participant ,
Sep 20, 2017

Copy link to clipboard

Copied

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

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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>

Views

328

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Sep 20, 2017 0
Adobe Community Professional ,
Sep 22, 2017

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 22, 2017 0