Formatting CSV / Excel File

Learning & Development Programs Automation Starter
1

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!

2

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

3

Microsoft Excel Comma Serpeated Values File:
image

xlsx-File:
image

4

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

5

I am not able to upload this csv.

image
image804Ă—225 16.6 KB

6

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.

7

@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

20220622144435
20220622144435641Ă—727 18.4 KB

8

Thanks a lot! Currently dealing with this issue:
image

9

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

10

image

image
image817Ă—379 13 KB

11

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

12

The format of the column is “General”

13

Opening a source file in notepad it looks like this:

image

14

What about trying to use an “Invoke Code”?

15

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

1 Like
16

@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.

17

Okay but I dont get the format I want.

image
image837Ă—427 21 KB

  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.

18

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

19

image
image1355Ă—797 89.6 KB

image
image1250Ă—796 94.8 KB

20

@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