Skip to main content
  • EN
  • Home
  • Forums
  • FME Form
  • Data
  • Excel: Best Way to find the first blank row, then write new data to it / Using an attribute as start row

Hi,

I am looking for the best way in FME to find the first blank row in an Excel Spreadsheet. I have a way, but there are likely better ways. For now I am using the AttributeFilter to give me all of the rows that have data and then using a counter to give me the first blank row. If you know of a better way I would like to streamline this:

It spits out an attribute named RowToWriteTo and it gives me row 127 which is correct. However, I am not sure how to use this in the writer.

 

I am needing it to write one new row of data to the first blank row in that same spreadsheet and then save it. In this case, row 127. Next time the workspace would be run it would be row 128 etc. etc. So the row number is dynamic each time the workspace it run.

 

My problem has been keeping the existing data and then just adding that one row to it. Perhaps that is a writer issue. Not sure? Any ideas would be helpful!

I would think the correct way is to set the Writer Mode to Update? Or should it be Insert?

 

***SUGGESTION FOR EXCEL WRITER: Allow an Attribute to be used for Start Row in the Start/Position Parameters.

By default the Excel writer will append to an existing file if the writer parameter "Overwrite existing file" (in the navigator) is set to No (which is its default setting), so I think you should give that a try. Probably will want to set the writer mode parameter to INSERT, just in case.

By default the Excel writer will append to an existing file if the writer parameter "Overwrite existing file" (in the navigator) is set to No (which is its default setting), so I think you should give that a try. Probably will want to set the writer mode parameter to INSERT, just in case.

Thanks, that was done though writing the data to the first blank row now seems to be the big issue.

By default the Excel writer will append to an existing file if the writer parameter "Overwrite existing file" (in the navigator) is set to No (which is its default setting), so I think you should give that a try. Probably will want to set the writer mode parameter to INSERT, just in case.

Looks like it is now inserting to the first blank row 🙂 Question now is whether there is a better way to find the first blank row.

[[[BSW Excel First Blank Row Insert]]].fmwtIn case others have the same issue, I have found a solution, though I still do not think it is the cleanest way of finding the first blank row. If anyone comes up with a better mousetrap I'd like to try it. The workspace is attached. All you would need to do is edit the StatsCalcs to Group By what you need it to and set up the Writer to the columns you need. Hope that helps someone else!

[[[BSW Excel First Blank Row Insert]]].fmwtIn case others have the same issue, I have found a solution, though I still do not think it is the cleanest way of finding the first blank row. If anyone comes up with a better mousetrap I'd like to try it. The workspace is attached. All you would need to do is edit the StatsCalcs to Group By what you need it to and set up the Writer to the columns you need. Hope that helps someone else!

I'm not sure why you need to know the first blank row. As @redgeographics mentioned, you can just append the new row(s) under the existing data with the Excel writer.

If you would clarify the goal you would like to achieve, a better solution might be provided, other than getting the first blank row.

Looks like it is now inserting to the first blank row 🙂 Question now is whether there is a better way to find the first blank row.

Is there a chance there will be non-blank rows after that first blank one? Because if not I really think you're massively overthinking this.

[[[BSW Excel First Blank Row Insert]]].fmwtIn case others have the same issue, I have found a solution, though I still do not think it is the cleanest way of finding the first blank row. If anyone comes up with a better mousetrap I'd like to try it. The workspace is attached. All you would need to do is edit the StatsCalcs to Group By what you need it to and set up the Writer to the columns you need. Hope that helps someone else!

Ah, I didn't notice there are some data rows after blank rows in your sample Excel dataset. I've understood your situation now.

If you expose "xlsx_row_id" (row number) in the Excel reader feature type, you can get id of the first blank row with this workflow.

Is there a chance there will be non-blank rows after that first blank one? Because if not I really think you're massively overthinking this.

Hi @redgeographics, yes I found that their sample data contained in the fmwt (posted above) have non-blank rows after the first blank row. This one.

ALL CODES INFO.xlsx

Hi @redgeographics, yes I found that their sample data contained in the fmwt (posted above) have non-blank rows after the first blank row. This one.

ALL CODES INFO.xlsx

Yes, that does indeed complicate things.

Ah, I didn't notice there are some data rows after blank rows in your sample Excel dataset. I've understood your situation now.

If you expose "xlsx_row_id" (row number) in the Excel reader feature type, you can get id of the first blank row with this workflow.

Ultimately, this is likely the best solution IF the spreadsheet is clean. Thank you both!

Here's my idea.

 

 

Original Sheet

 

New Data

 

Final Sheet

Ultimately, this is likely the best solution IF the spreadsheet is clean. Thank you both!

Actually, even with a clean sheet it likes to overwrite row 127 over and over. Thought it was caching, but then it continued to do the same thing after saving, closing and rerunning. My solution seems to work so I will probably stick with it though I like @jlbaker2779 's idea for a not so clean spreadsheet.

Reply


Community Stats

30,114
Posts
114,307
Replies
37,747
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.