Converting a csv file to text to columns

Help Activities
, , , , , ,
1

Hi I want convert a csv file delimited by character “|” to new excel file. Is there any option to convert other than text to column from modern activity

because through this activity i will have read csv , write range workbook content of csv to excel and then use text to column, can you please let me know any other option like vba or c# for converting from text to column

2

Read CSV Activity (ensure header and delimiter char settings
→ Result will be a datatable - dtData
Write Range Activity
→ use dtData

3

Hi @YANG_zing

→ Use Read CSV activity. In the properties panel set the Delimiter to Pipe and store the data in data table sat dt_data.
→ Use Write Range workbook to write the data back to excel. make sure to enable Add Headers option

Refer the below image for better understanding:

image
image1458×767 43.5 KB

Regards

4

Client specifically wants converting from text to column

5

@YANG_zing

Since your text input is of extension.txt right. You can pass the same text file path in Read CSV acitvity and store it in data table. After that use Write Range Workbook to write it back to exce.

Regards

6

Is there any difference with this approach and the modern activity - text to column

7

@YANG_zing

You will have to use Excel activities which is not required in the given case. Try the process once check whether it’s working fine.

Regards

8

Yes, it is working fine

9

Any vba option for the same.

10

Hi @YANG_zing

You can try the below code in Invoke VBA activity.

Sub ImportTextFile()
    Dim filePath As String
    Dim delimiter As String
    Dim ws As Worksheet

    ' Set the file path and delimiter
    filePath = "C:\Users\spputhucode\Documents\UiPath\BlankProcess21\Input.txt" ' Replace with your file path
    delimiter = "|"

    ' Create a new worksheet
    Set ws = Worksheets.Add

    ' Import data from text file
    With ws.QueryTables.Add(Connection:="TEXT;" & filePath, Destination:=ws.Range("A1"))
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFilePlatform = xlWindows
        .TextFileParseType = xlDelimited
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileOtherDelimiter = delimiter
        .Refresh
    End With
End Sub

Code text file path:
ExportToPDF.txt (975 Bytes)

Paste the above code in text file and Entry Method name is ImportTextFile. You can follow the below workflow and create your workflow.

Workflow:

image
image834×1031 45.4 KB

Regards