Excel related

Help Studio
1

Hi all,
I have a doubt, I have an excel with 1000’s of data in one of the column the number starts with 0 example 067854 when I am reading that excel it is reading as 67854. For this i need to format the cell.

Can you please help me how to do this. Thanks in advance

2

Hi @hanviprebday

Try this

Single quote add in first

'067854

3

I have added but still it’s not reading 0

4

Hi @hanviprebday

In excel it will not take the 0 number if it at first in any value. Add the single quote (') for every in that column .
=> Use read range workbook activity to read the excel and store in a Datatable.
=> After that use for each row in datatable activity to iterate the rows.
=> Inside use assign activity

- Assign -> CurrentRow("Column name") = "'"+CurrentRow("Column name")

After for each Insert the Write range workbook activity to write the same datatable to same excel.

Hope it helps!!

5

@hanviprebday

  • Read Range (Excel Application Scope)
    • For Each Row (DataTable)
      • Assign: formattedValue = row(“ColumnName”).ToString.PadLeft(6, "0"c)
      • Assign: row(“ColumnName”) = formattedValue
    • Write Range (Excel Application Scope)
6

But in my file of 1000data few start with 0 and few start with alphabet will this work for both