Changing datatype of a column in excel

Hi Guys,

I am working on a process that involved a data transfer from one excel to other based on some conditions. From one of the column in source excel has data like 8768/0001, 3423/0001, etc. I mean some data that ends with “/0001”.
So the issue that I am facing right no is when I write this specific col back to my new excel, it takes this data in numerical value and writes it back as 8768,3423 and so on.
There are some vLookup formulas linked to the new excel that gives back “#N/A” as a result.
What I am thinking is to change the data type of that column to Text but I don’t know how to achieve this.

Need some ideas on this.

Thanks and Regards
Devbrath

1 Like

Buddy @Devbrath_Rajkhua

Buddy i tried to reproduce your issue. but i am getting the correct one buddy… if possible can i have your xaml file, project.json file may be all together a zipped one
This can be resolved buddy
Cheers @Devbrath_Rajkhua

hi there…

I tested it just now and using the ‘preserve format’ check box while reading the file will definitely solve your problem.

It will paste the numbers/numbers as it is.

Regards :slight_smile:

1 Like

Hey @Palaniyappan,
Sorry I can’t share you the excel file or the xaml file as it is confidential data. One thing I can tell you is the excel file to which I am writing the data, the data type of that column is “General” and I am thinking to change the data type of that specific column.

Thanks and Regards
Devbrath

Buddy i tried the same



image

May be the format of the excel column from WHERE you are getting and TO where you are entering must match with each other buddy
Cheers @Devbrath_Rajkhua
Kindly try this and let know buddy

1 Like

Yup,

Already existing excel file might have it set to Date or text or general. Depending what preserved format might be - excel will convert it to the current cell format.

So either he has to create a new file dynamically or the existing file must carry the same format where it is going to be pasted.

Last resort : Run macro/VB Script on the excel file to change the cell format before pasting the data table.

Hi @Raghavendraprasad and @Palaniyappan,

Thanks for your responses. I have come to a solution for this issue.
What I did is open excel and change the datatype of the whole column to text using hot keys. Its working fine and I am getting the data as desired.

Thanks and Regards :slight_smile:
Devbrath

1 Like

Well,

Glad it’s working. But, at times hot keys are a tad bit unreliable, so a lasting solution would root is something that would either use the inbuilt excel features of the tool/macros/scripting.

Regards :slight_smile:

When I read a cell suing Preserve Format option enabled ,then values like : 201909008818 is read as “2.01912E+11” by robot.

Pls help to reolve this.