Write range date formatting

I have a datatable where column type is date, in some cells the date is not present to I have added “Nothing” in add data row , but when I write this datatable to excel using write range activity these nothing cells are auto changed to 1/1/1990 which is incorrect.
As I need to keep date format in excel file too so I am using a template excel file with the column type as a date type. Can you please help me how to make empty/nothing cells to be empty in the excel file too

Have you tried with Null instead of Nothing?

1 Like

yes but “Null” is not present in uipath? and “System.DBNull” is giving error when I assign it to a date column(row)

Hi @kl_kl,

Can you try this way by enabling the PreserveFormat in the read range?

image

Regards,
@90s_Developer

2 Likes

give a try on using String.Empty and do use for RnD purpose the Excel Application Scope along with corresponding write range activity

Edited: on RnD we used nothing as mentioned below String.Empty will be complaint

1 Like

I am not reading any excel file, I have created the datatable(build datatable) and populated the data in uipath(using add data row) so I don’t think there is the use of read range over here.
Thanks

thanks i will try this and inform you about the results

@ppr this is giving error “Add Data Row: String was not recognized as a valid DateTime.Couldn’t store <> in start date Column. Expected type is DateTime.”

@kl_kl,

Does that error occur when the value is the empty row?
Also, can you show the expression which you have implemented in the workflow?

Regards,
@90s_Developer

1 Like

Main.xaml (11.6 KB)
@90s_Developer Check this workflow , please let me know if i have done any error or if there is any work around , below is the ss of what I expect , Thanks




Yes I am getting runtime error when I try to add the empty row(its not necessary that both the columns need to be empty , there can me situation like empty “start date” and non empty “Year”)

ok reasonable, give us some time for replication rounds

1 Like

Hi @kl_kl,

Check the below information,

This is the input excel,
image

This is the output excel,
image

This is the workflow screenshot,


image

Follow the below steps in your workflow,

  1. Use a Read Range activity to read the input file [firstexcel]
  2. Use Build Data Table activity to write the output data into the excel.(I have used two columns in the build data table as it is required only for two columns)
  3. Use For Each Row in DataTable to iterate through each row of firstexcel
    3.1 Use Assign and create DataRow for the Build Data Table.
    3.2 Use If Condition to check whether the row is Null or Empty and this is the expression: String.IsNullOrEmpty(CurrentRow(0).ToString) OR String.IsNullOrWhiteSpace(CurrentRow(0).ToString)
    3.2.1 If true, assign the value “Empty” to the datarow.
    3.2.2 If false, assign the date value to the datarow.
  4. After If Condition, use Add Data Row and in the DataRow pass the Datarow variable and in the DataTable pass the Build Data Table variable.
  5. Use Write Range, to write into the excel.

For a better reference, I have attached the xaml with the excel file below.

KLEmptyDatarow.zip (9.9 KB)

I hope this would sort out your issue.

Regards,
@90s_Developer

@90s_Developer Thank you for this detailed explanation,

But :sweat_smile: , my complete use case is to generate the excel file that you have read using the Read Range activity.So the steps are:

  1. Use Build Data Table activity to initialise the output datatable
  2. in the loop(while/for) I add rows which are generated inside the code itself like today’s date or some other things like may be some transactionitem
  3. If the date or transaction item is empty I need to place empty( blank/nothing/null) in the add data row
    4.Finally when I write the datatable to a excel file the output should be same as this
    image

    I am really sorry as I couldn’t explain you the problem in my main question, Thanks again for this hard work :hugs:

@kl_kl
in the first rnd round issue was not replicable

  • nothing was used for the add datarow
  • tested with EAS write range And workbook write range

Ensure you are using the last excel activities package version
just run a rnd round against a new / fresh created excel

also do notice that with Balareva Change Cell Type Activity we can reset the cell format

@kl_kl,

Then you can use the condition which I have mentioned earlier to check whether the incoming values are Empty or Null.

Regards,
@90s_Developer

Thanks, everyone, I also tried to run the sequence in a different device, the outputs are coming correct over there(i.e. Nothing rows are really empty) , seems like there is some setting or something with excel due to which it is showing Nothing values as some default dates and some empty integers as 0. I will check for a solution of this issue in Excel .

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.