Find the empty column in Excel file and write to it

How can i find the last Empty column in an excel file and write to it?

I have an excel file with data downloaded from SAP.
Some time the empty column in that Excel file start from column L or some time from column Q depend on the data.
So how to find the last empty column and write to it…

Can someone help…

Is it the last column that has ANY empty data in it? Or is at ALL data in the column is empty?

Either way, you’ll want to loop through the columns in reverse order. In normal programming this would be done by something like For dt1.columns.count-1 to 0 step -1 but Uipath doesn’t have a For loop activity (only for each) which allows you to step through backwards, so you have to approximate it by using a while loop.

You can use a loopcounter and index in the while loop and at the end of the loop just subtract 1 from the loopcounter and index. It would then be something like: While loopcounter > 0 [do code here] assign loopcounter = loopcounter - 1

So with the looping stuff out of the way, you have to figure out what code to include in the loop. This thread has a decent way of checking here: How to check if a particular column is empty in DataTable (Without using For Each Row)?

Once you find a column that is completely empty, you can use the datacolumn.Ordinal() property to determine the position of the empty column. This gives an integer. With that integer, you can then convert into a letter (if needed), or just write directly to that column instead

1 Like

There are a few options here.

One you could open excel file, use keystrokes to navigate to far right of first row (ctrl + g) and type “XFD1”, then ctrl + left arrow to go to last used column. Add one and you have first unused column.

I prefer a similar approach in VBA

Last_Column = Worksheets(“Sheet1”).cells(1,Worksheets(“Sheet1”).columns.count).end(xltoright).column

Lastly, you could use UiPath to do similar actions. Read range of sheet, add column to datatable, then write range back to same sheet and overlay the info.

thanks for reply but let me clear…
I am in a “for each loop” as im reading the ID from same excel Sheet and creating case on that ID in an other system… now when im done with creating case I need to write case ID in same excel file but I do not know from where empty column start so that i can give this column a name as “Created Case ID” and then write the case id in the row which I created a case on.

maybe that is a good explanation what i want.

You need to find the empty column BEFORE you loop through each row. I would recommend following the instructions I wrote above, although you can ignore the last paragraph as you can just use the column index within later for each row loop

@Dave I did not get it how to adjest as im always running a count in for each loop and do not know how to adject that…
:frowning:

when i do simple Write cell in for each loop
image
then i specify the column and row… its working but i did not want that… I want that instead of I hard code the column and row number it find it self and add the value.
is’t there a simple solution to it?

Step 1 is to find the last empty column in your datatable (use the while loop to iterate through all columns backwards and the SELECT statement in the forum post I linked above)

Step 2 is to iterate through each row, do your processing, and write the value into the empty column of your datatable found in step 1. Don’t use a write cell activity, just use an assign activity to update the datatable directly.

Step 3 is to use an excel write range activity to write the entire datatable back to your original excel document at one time.

so I need to create a new datatable and then merrage that in to the old datatable
is that what you says :thinking:

No, everything is done on your existing datatable

like this…
image
my whole code is within this for each loop…
but how this loopcounter will tell me which column is empty :thinking:

as i’ve stated, you need to find the empty column BEFORE you loop through each row.

@Latif - i was trying to give you the clues and all of the information needed to have you try it out on your own. I would still recommend trying to do that. However, if you’re struggling with it, I attached the .xaml showing the solution as well: latif.xaml (14.2 KB) latif_inputFile.xlsx (10.3 KB)

thanks Dave but the code is not giving write value to me.
In my sheet I have only value filled til column K. means from Ltil rest of excel have empty column …
and your code is saying I have 18 column filled which is not right and i converted whole your solution into my code and I have no error on running but value is not correct…

Hi @Latif
Please use
VarColCount = DTInput.Column.Count

it will give you column count
NextEmptyColumn = Convert.ToChar(065+VarColCount).ToString

Please mark it as solution if this helps.

Sounds like an input problem? Put this in a write line and verify it is showing the correct number of columns: YourDatatable.Columns.Count.ToString

If that number equals the number of columns in your datatable, then it should work fine. If not, then there is an issue with your data and it will be impossible to find the last empty column, because uipath is finding more empty columns then you think there are…

It is showing the correct number of colum… I have 11 colums with data and it shows… 10 starting with 0. which is correct…
image
Problem is i need to define starting cell when i do output and I cant not defind any as it has to be the empty one from the excel file…
If i do not specify the Starting Cell
image
I get this error below.
image

So what should I write in Starting cell that it write the value…
As i understood your code it said… this is putting my value into that row…
image

It is working but not on write columm…

@Dave kindly help and tell me where is wrong.

@Latif

Write range will overwrite the entire sheet. You should put the starting cell at A1.

Try using my input sheet and creating an outputworkbook using the .xaml I uploaded earlier to see how it is supposed to work. I also put fairly detailed comments & annotations in the .xaml but please let me know if the comments in the file aren’t helping

Hi @Dave

Try to just run that code.

MyFile.xlsx (9.8 KB)
latif.xaml (20.6 KB)
I want result like its in the result file check that.
WantResultLike that.xlsx (10.2 KB)

Your code is using column A to right the output value and its overwriting my ID’s. Its not that what i want… I want all data from input be same and increment with new value on new column in last of inputfile.

kindly help.