Skip to main content
Y
yui8979
Inspiring
August 15, 2011
Question

CFSPREADSHEET Read truncating empty columns with excludeHeaderRow = true

  • August 15, 2011
  • 1 reply
  • 1314 views

Hi,

Is anyone having an issue with ExcludeHeaderRow for CFSPREADSHEET where it is truncating an entire column if the column is empty, even though it has a header column?

Below is my code to read an excel file named Fruits.XLS:

<CFSPREADSHEET ACTION="READ" SRC="c:\Fruits.XLS" QUERY="q_xls" SHEETNAME="Sheet1" headerrow="1" excludeheaderrow="true"></cfspreadsheet>

<cfdump var="#q_xls#">

Fruits.XLS consists of :

NoFruitDescription
1Apple
2Orange

Coldfusion's returned query 'q_xls' is as below, with the Description column missing:

No    Fruit

1      Apple

2      Orange

This is an issue because the returned query is expected to contain column "Description" with empty values, but it does not exist.I know there are workarounds like, not setting ExcludeHeaderRow to true, but I'd rather not go there.

Please help.

Version : CF 9.0.1

Thanks

Lisa

    This topic has been closed for replies.

    1 reply

    -
    -__cfSearching__-
    Inspiring
    August 15, 2011

    Specify the column numbers you want to retrieve explicitly

    [ CFSPREADSHEET columns="1-2" .... ]

    Y
    yui8979Author
    Inspiring
    August 15, 2011

    Forgot to mention that I have the columnnames specified  -  <CFSPREADSHEET ... columnnames="No,Fruit,Description"> but that did not work - "Description" column in returned query is still missing.

    Specifying columns="1-3" works so I'd expect columnnames to work but it doesn't.

    Is this a bug?

    Thank you.

    -
    -__cfSearching__-
    Inspiring
    August 15, 2011

    No, it is not a bug.  "columnames" refers to the query not the headers in your spreadsheet. The purpose of that attribute is to let you define/change the column names in the resulting query ie "q_xls".

    Message was edited by: -==cfSearching==-

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