Write Range converting string values to integers

Help
1

Hi All,
I’m trying to read a excel file containing 3 lakh records in it and filtering the data table with some required columns. Then i’m trying to write the filtered table into new sheet. The issue is that , in the actual sheet, Column Name is product ID, my row value is 005300608 which is a string and when i copied the data into new sheet with write range activity, it is converted to 5300608 which is a integer. when i tried to make pivot on that, it is summing up the data in the product ID column as well.

I find a solution mentioned that, for each row , if we append a (') apostrophe, then the row data will be string. if i want to do that for all 3 lakh records, then the robot will run for 4 hours :slight_smile: and there will be no use of automation here.

Can anyone provide me a solution for my issue?

Many thanks in advance.

Thanks,
MR

2 Likes
2

Can you try the below solution ?

Format the target column first ie change the data type to “Text” and then write.

Refer the below screen shots.

image

image

3

@skini76 Thanks for ur reply :slight_smile:
I tried formatting the cells as text before running the robot process. Still getting the value as 5300608 in the new sheet.
Am i missing anything in the steps you mentioned?

Regards,
MR

4

Hi @HareeshMR,

Can you create the new file from a template that contains the formatting. So before you write it to the excel you copy over the new file from the template and then do a write range.

Hope this helps you!

Regards,
PD

5

Hi @PD2,

Tried the way you mentioned above. Still getting the values as string i.e, preceeding zeroes are trimmed. And i tried to write the columns to a .CSV and then to write in the excel. Still the values in the CSV are not as required :slight_smile: .

Is there any other way to try?

Thanks,
MR

6

Same issue, and before the write range, the value has the zeros. But when i copy the dataTable, to a excel, the values lose the zeros.

Ex: Excel Value Text :0032654 → Data Table: 0032654 → msg box: 0032654 → Excel Again (sheet output): 32654.

I am using Write-Range in a Excel Scope

7

Heyy @pedro_Henrique_Assumpcao

When you try to write it from datatable to excel, it will write it in the number format instead of string. So, if you have huge number of rows, try to copy and paste special with a piece of VB code which paste special will always include the format .

Do let me know if you need some code for this

8

Hi,

I am also facing the same issue.
Any solution from UIPath point of view or how you resolved it ?

9

I used Invoke code activity and wrote a piece of code to copy and paste the data so it will preserve the actual format @ronak83garg

10

Can you share that piece of code hareesh for reference ?
As when i am trying to use CopyPaste Range activity, it takes lot of time for me.

Thanks for your help in advance.

11

Here is the code @ronak83garg

	Dim excel As Microsoft.Office.Interop.Excel.Application
    Dim excelDiscount As Microsoft.Office.Interop.Excel.Application
    Dim wb As Microsoft.Office.Interop.Excel.Workbook
    Dim ws As Microsoft.Office.Interop.Excel.Worksheet
	excel = New Microsoft.Office.Interop.Excel.Application
	wb = excel.Workbooks.Open(Path of excel, [ReadOnly]:=False) 
    excel.Visible = True
    ws = CType(wb.Sheets("MT"), Microsoft.Office.Interop.Excel.Worksheet) 'select a sheet and activiates'
    ws.Activate()
	Dim MTUsedColumnsRange As Integer = ws.UsedRange.Columns.Count
    Dim newSheet As Microsoft.Office.Interop.Excel.Worksheet = Nothing

    newSheet = CType(wb.Sheets.Add, Microsoft.Office.Interop.Excel.Worksheet)
    newSheet.Name = "MTSheet"
    ws.Activate()

    For c As Integer = 1 To MTUsedColumnsRange
        Dim str As String = Convert.ToChar(64 + c)

        If CType(ws.Cells(4, c), Microsoft.Office.Interop.Excel.Range).Value.ToString = "Project" Then
            ws.Range(str + "4:" + str + (MTUsedRange - 1).ToString).Copy()
            newSheet.Activate()
            newSheet.Range("B1").PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues)
            ws.Activate()
		End If
    Next
	wb.Save()
    wb.Close()
    excel.Quit()
1 Like
12

This is a good question you got there. This is not a solution to the activity itself but it might help you. Instead of using the write range from the Excel Scope, use the system write range, that one will not convert string to values. If you type write range in the activity panel you would see the one under App Integration for Excel and one under System for Workbook. Try using the one under system.

1 Like