Opening CSV file using UiPath changes the format


#1

Hey,

I am performing some operations in a CSV file using Invoke code. When I open the file manually, i get the date column in the correct format. But when I open it with Invoke code/Excel application scope it changes the default format to MM/dd/yyyy format. For eg: the date 30/06/2018 is changed to ‘text’ and the date 01/07/2018 is still ‘date’ but January 7th. While opening manually the format is dd/MM/yyyy and so all the dates are in the expected format. Any idea why does this happen?


#2

Hey, @midhunsug661. Could you share your workflow here for us to understand the problem better?


#3

HI @siddharth

you can use this macro,

Sub Macroxlsx()

’ Macroxlsx Macro

    MyPath = "file_path" ' change to suit
    'MyPath = "file_path"
    strFilename = Dir(MyPath & "\*.x*", vbNormal)
    strFilenameOnly = Split(strFilename, ".")(0)
    strRootFile = MyPath & "\" & strFilename

Set RootFile = Workbooks.Open(strRootFile)

ActiveWorkbook.SaveAs Filename:="save_file_path" & strFilenameOnly & ".xlsx" _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

ActiveWindow.Close

End Sub


#4

ABC.zip (5.3 KB)
There isn’t any workflow. My issue is that when I open the attached file manually, all the data in column K is ‘date’.

Manual

But when I open the same in the excel application scope the 30/06/2018 turns as string and 01/07/2018 turns as date

Scope

Please find attached the file as well.


#5

The first three values might be in a different format than the rest of the values already. Try this: go the excel sheet and give that column a particular format. Then run your code and see what happens.