Save as CSV UFT-8 without BOM

[MaxDS1 (Max)]

Hello,

I’m using the “Write CSV” activity to save an Excel file as CSV, to be imported into another system.

The issue is the file gets saved as UTF-8-BOM, which the system cannot read properly.

I tried some different encodings, but none of them gives me the desired result. Right now I have to export it manually like this:

How do I solve this?

[Yoichi]

Hi,

The following post will help you.

UTF-8 without BOM

@vandame welcome to the forum give a try on following: [grafik] [grafik] Ensure following: [grafik]

Regards,

[MaxDS1 (Max)]

Hi,

Thanks for the reply.

I’m not sure I understand. How would I use this in my workflow?

[Yoichi]

Hi,

First, get csv string from your datatable using OutputDataTable activity.

Then write it to text file using the above post’s way.(System.IO.File.WriteAllText method using InvokeMethod actiivty.)

Regards,

[MaxDS1 (Max)]

Do you have any samples of this being sued? I’m still not quite sure I get it.

[MaxDS1 (Max)]

OK I got this output from above method:

19-04-2022,1482,F,2115,10040,Materiale,ALT,Flyttet til 21085,"-2752,8"
19-04-2022,1482,F,2115,21085,Materiale,ALT,Flyttet fra 10040,"2752,8"

Desired output is this:

19-04-2022;1482;F;2115;10040;Materiale;ALT;Flyttet til 21085;-2752,8
19-04-2022;1482;F;2115;21085;Materiale;ALT;Flyttet fra 10040;2752,8

Any ideas?

[ptrobot]

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.Columns
	csv.WriteField(col.ColumnName)
Next col
csv.NextRecord

' Write rows
For Each row As 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

The line csv.Configuration.Encoding = New System.Text.UTF8Encoding(False) is actually not needed since CSVHelper seems to write without BOM by default.

[MaxDS1 (Max)]

Hi,

Thank you for the reply.

It seems very complicated - I never imagined such a “small” issue would need solutions like these.

I managed to solve it two other ways though. Both simple enough so I could understand them myself.

Solution one was just to make use of string replace to replace all instances of

,

with

;

Solution two was to add headers and have the program I imported the files into, ignore the first line (the headers).

The intia issue and the reason I created this topic, was that if the CSV file was saved as UTF-8-BOM the program I imported the files into, would not recognize the date - but only on the first line (???).

[Yoichi]

Hi,

Another approach :

If the file always has BOM of UTF-8, the following workflow removes first 3 bytes and it will be without BOM file. Probably we can use WriteCSV activity with this.

Sample20220420-6.zip (2.8 KB)

Regards,

[ptrobot]

Beware if you go with Solution 1, that you will be replacing commas inside the cells also. E.g. the value “-2752,8” will become “-2752;8” which might not be what you want. You can get around the issue by using Regex.Replace to only replace commas outside quotes.