Output data table with pipe delimiter

[FMCgclifford]

I got stuck with having to create a CSV file in UTF-8 format without BOM. Luckily this link made that easy

Write CSV with LF endline (Unix) and UTF8 witout BOM

I’m tring to make CSV with UTF-8 encoding (without BOM) and UNIX new line. Delimiter is Pipe. I read: Small Bug - Read & Write Text UTF-8 encoding - #6 I installed newest package: 21.4.0 for System.Activities and 2.10.4 for Excel Activities. When I use Write CSV activity with properties: Delimiter Pipe AddHeaders True Encoding: “utf-8” I am still getting UTF-8 BOM. I tried also with empty value. It didn’t worked. How to get csv with UTF-8? How to change newline separator from /r/n to /n…

The problem I am having now is that I need the output to be pipe delimited and the “Output Data Table” function seems to default to csv without any options. Any ideas?

[Nithinkrishna]

Hey @FMCgclifford

Kindly try replacing the comma with pipe symbol in the output data table string.

Thanks
#nK

[FMCgclifford]

that won’t work as some of my value columns might also contain commas. They would be quoted in the csv

a,b,c,d,e
value1,value2,value3,value4,value5
“value 1,a”,value2,value3,value4,value5

[ptrobot]

You can use Regex.Replace to get around the issue:

newCSV = System.Text.RegularExpressions.Regex.Replace(outputFromDT, ",(?=(?:[^""]*""[^""]*"")*[^""]*$)", "|")

[FMCgclifford]

ptrobot,

This will solve the problem of commas inside quoted strings?

Graham

[ptrobot]

Yes, it will only replace commas outside quotes.

[FMCgclifford]

I’m still testing this. My data table is large (the CSV is about 15MB). I’ve been waiting 2 hours for this additional command to finish and it is still running. Any idea why it would take so long? I cannot wait hours for this conversion unfortunately.

Thoughts?

[ptrobot]

Unfortunately it will take awhile to replace a big string, especially with regex.

If you don’t mind real code, here’s an alternative way with Invoke Code:

Save as CSV UFT-8 without BOM

The Output Data Table activity only supports comma as delimiter. If you want to use a custom delimiter you need to generate the CSV text yourself. You could e.g. use CSVHelper to write the CSV file. Use the following code in an Invoke Code activity: Dim sw As StreamWriter = File.CreateText(in_FileName) Dim csv As CsvWriter = New CsvWriter(sw) csv.Configuration.Delimiter = ";" csv.Configuration.Encoding = New System.Text.UTF8Encoding(False) ' Write headers For Each col As DataColumn In in_DT.C…

Just change the Delimiter to “|” before using the code.

[FMCgclifford]

Interesting that I added one block of the invoke code and it seems ok.

I added a second one further down in the workflow and that gives an error. Any ideas why?

[ptrobot]

Have you imported CSVHelper?

[ppr (Peter Preuss)]

we would recommend not to create artifacts which are not in a format as needed and the adoapt by regex/replace.

We can generate the CSV as needed directly:

Find starter Help here:
WriteMassCSV_UTFWithoutBOM_CustomDelimiter.xaml (8.4 KB)

[FMCgclifford]

i added that to the import. New error now.

No compiled code to run
error BC30512: Option Strict On disallows implicit conversions from ‘System.IO.StreamWriter’ to ‘CsvHelper.ISerializer’. At line 2
error BC30456: ‘Encoding’ is not a member of ‘CsvHelper.Configuration.IWriterConfiguration’. At line 5

my arguments are a DT (with the data) and a string for the filename

[ptrobot]

Test with the code below instead. I have hard coded the namespace so UiPath shouldn’t complain anymore.

Dim sw As System.IO.StreamWriter = System.IO.File.CreateText(in_FileName)
Dim csv As CsvHelper.CsvWriter = New CsvHelper.CsvWriter(sw)

csv.Configuration.Delimiter = "|"
csv.Configuration.Encoding = New System.Text.UTF8Encoding(False)

' Write headers
For Each col As System.Data.DataColumn In in_DT.Columns
	csv.WriteField(col.ColumnName)
Next col
csv.NextRecord

' Write rows
For Each row As System.Data.DataRow In in_DT.Rows
	For i As Integer = 0 To row.ItemArray.Count-1
		csv.WriteField(row(i))
	Next
	csv.NextRecord
Next row

sw.Close

' Clean up
sw.Dispose
csv.Dispose

Also remove the CsvHelper import you added. It seems like it has imported an older version of CsvHelper that are missing a lot of features.

[FMCgclifford]

Unfortunately i’m seeing the same error still. I am using your new code and I removed the csvhelpder from the import tab.

Error ERROR Validation Error No compiled code to run
error BC30512: Option Strict On disallows implicit conversions from ‘System.IO.StreamWriter’ to ‘CsvHelper.ISerializer’. At line 2
error BC30456: ‘Encoding’ is not a member of ‘CsvHelper.Configuration.IWriterConfiguration’. At line 5 Main.xaml

[ptrobot]

Between the first Invoke Code and the second Invoke Code, do you have any other CSV activities? It seems like your UiPath Studio is trying to access different versions of CsvHelper.

If you search of CsvHelper in your project, do you see different versions?

I have no issue invoking the code several times on my computer:

[FMCgclifford]

Here is mine

[ptrobot]

Here’s my last try. The code has been updated for CsvHelper 16.0.0. I have also removed the encoding line since CsvHelper doesn’t add BOM either way.

Dim sw As System.IO.StreamWriter = System.IO.File.CreateText(in_FileName)
Dim csv As CsvHelper.CsvWriter = New CsvHelper.CsvWriter(sw, System.Globalization.CultureInfo.InvariantCulture)

csv.Configuration.Delimiter = "|"

' Write headers
For Each col As System.Data.DataColumn In in_DT.Columns
	csv.WriteField(col.ColumnName)
Next col
csv.NextRecord

' Write rows
For Each row As System.Data.DataRow In in_DT.Rows
	For i As Integer = 0 To row.ItemArray.Count-1
		csv.WriteField(row(i))
	Next
	csv.NextRecord
Next row

sw.Close

' Clean up
sw.Dispose
csv.Dispose

[FMCgclifford]

Thank you!!! that did it.

Thank you again.