• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Remove extra empty rows from excel - cfspreadsheet

Participant ,
Feb 07, 2011 Feb 07, 2011

Copy link to clipboard

Copied

Hi all,

I am testing an excel file reader, and the spreasheet tag brings the empty rows as well. How would I prevent empty rows?

I have 10 rows in excel once I run the this tag it brings 60 more rows(empty rows), and the rowcound becomes 70..

<cfspreadsheet action="read"

src="#form.UPLOADFILE#"

query="myQuery">

<cfdump var="#myQuery#">

TOPICS
Advanced techniques

Views

2.8K

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
community guidelines
LEGEND ,
Feb 07, 2011 Feb 07, 2011

Copy link to clipboard

Copied

It's impossible to tell, based on what you've given us.  I imagine there is SOME data in the rows you are saying are empty, even if it's a space in the 70th row, or some formatting or something.  This is more likely than CFSPREADSHEET just magicking them out of thin air.

It might pay to export the XLS as CSV data or HTML or something and have a look at why there are more rows than you think.

--

Adam

Votes

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
community guidelines
Participant ,
Feb 07, 2011 Feb 07, 2011

Copy link to clipboard

Copied

I dumped that spreadsheet var it shows the empty rows as well. Is there a way to eliminate those empty rows in fly while reading it from?

Votes

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
community guidelines
LEGEND ,
Feb 07, 2011 Feb 07, 2011

Copy link to clipboard

Copied

It might not be quite what you want, but you can do a Q of Q to get rid of them after the fact.

Votes

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
community guidelines
LEGEND ,
Feb 08, 2011 Feb 08, 2011

Copy link to clipboard

Copied

If you have control over the contents of the XLS file, then I'd approach the issue from the POV of trying to get the data correct in the first place, rather than importing bung data and then dealing with it there.

But one should still take the belt & braces approach of making sure only the data you want makes its way into your system.

Dan's already given you an answer, but you should be asking yourself the question: if I have a query with rows in it I don't want... how do I get rid of those rows?

--

Adam

Votes

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
community guidelines
Participant ,
Feb 08, 2011 Feb 08, 2011

Copy link to clipboard

Copied

LATEST

yes i know that i should use Q of Q after reading... but was wondering during reading xls how to remove the empty rows. Apparently, there is no way unless the upgraded cfspreadsheet will have. than you guyz.

Votes

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
community guidelines
Resources
Documentation