I have a column in excel which has 14 digit number with 0 prefix. So, I changed the whole column data format to customer and enter 14 zeros to populate the correct no. I want this column to be converted as text. Pl see the screenshot:
I want to convert like this:
Moreover, when I copy it in another sheet so it shows the General Data Type and look like this:
if your organization allows to use third party package means
after that in the activities panel search for change cell type activity
if you want to convert whole column like A means you should declare like this “A:A” in cell Tab
copy the destination cell format in excel
follow these steps to find cell format Right click the destination formatted column → Format cells → custom ->copy the value from the type tab
paste the copied format into cell format tab in change cell type activity
finally choose excel file path in change cell type activity
hope its solves your issue
Thanks @Robinnavinraj_S, Our organization does not allow us to use third party package. Could you please share another solution instead.
Try this expression
@Sudharsan_Ka : My data not always start with 0, it can also start with other than zero (i.e., 1,2,3,4, and 5). So, your code will be good where we want prefix as zero, and I think through IF condition using Length function we can check. Pl. guide.
we can use power shell script also
$ExcelObject = new-Object -comobject Excel.Application
$strPath1=“D:\Powershell\New Microsoft Excel Worksheet.xlsx”
$ActiveWorkbook = $ExcelObject.WorkBooks.Open($strPath1)
$ActiveWorksheet = $ActiveWorkbook.Worksheets.Item(1)
$ActiveWorksheet.columns.item(‘A’).NumberFormat = “0”
Save the above script into text file
Replace the highlighted text below as per your document in the script
provide your excel file path in $strPath1 ="D:\Powershell\New Microsoft Excel Worksheet.xlsx"
provide your destination sheet index as 1,2… $ActiveWorkbook.Worksheets.Item(1)
Provide your column index want to change like A,B,C… $ActiveWorksheet.columns.item(‘A’).NumberFormat = “0”
read that saved text file using read text file activity
pass the output variable of read text file activity into invoke power shell activity and enable isscript checkbox
Hope its solves your issue
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.