November 24, 2020, 4:47am
How do I get the index of the first blank row in an excel file that I’m reading into a data table?
I am able to return the index of a column “Status” that contains the value “New” with this formula:
io_TransactionData.Rows.IndexOf(io_TransactionData.AsEnumerable().Where(Function(row) row("Status").ToString.Trim = "New").ToList().First)
But I am having a hard time getting it to work when I try to convert it so that:
It references the column by index rather than by name
It returns only values that are blanks (would ideally like to use the string.isNullOrWhitespace function)
Can anyone please tell me what I’m doing wrong?
io_TransactionData.Rows.IndexOf(io_TransactionData.AsEnumerable().Where(Function(row) row(0).ToString.Trim = "").ToList().First)
Try this one
io_TransactionData.AsEnumerable.First(Function(x) x(0).ToString.Trim = “”)
Returns index of First blank row
November 24, 2020, 5:04am
Thank you! Just tried and it gives me this error message that sequence contained no matching element, which is pretty odd since the Excel file I am reading definitely contains blank rows.
Column index is 0, isn’t it?
Can you show your datatable?
November 24, 2020, 9:30am
find some starter help here (demo on find first, last, all index for a blank column)
FindIndex_FirstLastAllMatch_1Col_1Val.xaml (8.7 KB)
November 24, 2020, 3:16pm
TempOutput.xlsx (9.4 KB)
Here’s a modified version of the file I am working with. In reality, it has over 26 columns, but I doubt that would make a difference in how the rows are read.
Since I’ll be adding data in batches to the file, I want to be able to locate the index of the first blank or empty cell in the first column and then use that index to write data to various columns.
Thank you for your help!
November 24, 2020, 3:22pm
These formulas are awesome. The only problem I ran into is that while they work great to return the index of a column cell that contains a particular phrase, they do not work for blanks, which is my goal.
I tried modifying the first formula as follows:
DTTemp.AsEnumerable.toList.FindIndex(Function (r) r(0).ToString.Equals("")) + 2
But the result is always “1”, although that is not the correct index to return since I set up the test file to have its first blank cell in column A7.
I suppose I could use the formula to find the index of the last cell that contains values (adding 1 to bring me to the next, blank cell), but the problem there is that I can’t control what text will be present in the previous cells and I have not had any better luck using wildcards
Does Row 7 contain any data for any column?
If not, it automatically reads A1:J6 for your sample File.
So A7(row 7) is not contained in your datatable, upper solutions return that your Datatable doesn’t contain blank rows
November 25, 2020, 9:32am
if find index doesnt find the match it will return -1. So with adding +2 on this we got 1.
Does mean: you got 1 as it was not found.
For Blanks give a try on following:
dtData.AsEnumerable.ToList.FindIndex(Function (r) (isNothing(r(0)) OrElse String.IsNullOrWhiteSpace(r(0).toString.Trim)))
November 25, 2020, 4:01pm
Duh. I should have thought of this. This is exactly what is happening with my data set, and makes perfect sense now.
As a workaround, I will use row count to return the number of rows in the data table + 1 and use that for the next row index to fill with data.
Thank you so much for your help, functions are something I want to grow stronger in and this thread taught me a lot!
November 25, 2020, 4:02pm
@ppr THANK YOU! This is exactly the sort of function I was struggling to create. It works like a charm and I’ll be using it quite a bit in the future. Many thanks!
November 28, 2020, 4:02pm
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.