Formatting CSV / Excel File

Hi there,

I need to read a csv, output it into a datatable and write range into an excel file.
Problem: the format wont stay. Numbers become text.

Can someone help?

Thanks a lot!

@Katarina ,Can you show your csv?And the excel after you wirte.

Microsoft Excel Comma Serpeated Values File:
image

xlsx-File:
image

@Katarina ,Could you give me a source file?I wirte your data by myself and then i check the format remains unchanged.

I am not able to upload this csv.

Or is there a way to change the column data type in excel?

Numer formatted as text in cell - Help / Studio - UiPath Community Forum

Found this but doesnt help.

@Katarina ,You can use Vba to change them.

Attribute VB_Name = "ChangeFormat"
Sub ChangeFormat()
Attribute ChangeFormat.VB_ProcData.VB_Invoke_Func = " \n14"
'
' ChangeFormat Marco
'

'
    Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
End Sub

Thanks a lot! Currently dealing with this issue:
image

@Katarina ,If your Excel Trust Centre enable all macros or not?And check the box:Trusted access to the VBA project object model.

image

@Katarina ,Ok,now let we comment out the vba.And run your project.And check your xlsx format.

The format of the column is “General”

Opening a source file in notepad it looks like this:

image

What about trying to use an “Invoke Code”?

@Katarina ,Oh,your separator is “;”, my file use “,” to separate.Ok,let me think Invoke code.

1 Like

@Katarina
20220622155344
Look This.I use vba and success.Although it still reports an error,i comment out the vba it still show the format you want.
ps:The columns name i made up.

Okay but I dont get the format I want.

image

  1. I have a csv-file
  2. I read csv-File into a datatable
  3. I write range into a new xlsx-file

In none of these steps I have the column formatted as numbers. But thats what I need.

@Katarina ,Can you give me your xaml file?Or screenshot your process and setting.

@Katarina ,you use studioX?Or you use modren design?It look different as mine.Okay,now you write range to excel and the format as you show before right?
Look like we can change the excel setting.Check the column which you want to change the format,click the column,and then click “Data” and then click “Text to Columns”,and then click finish.
When I change once time by VBA, the setting was saved,then next time i run,it still show the format which i show you before.

1 Like