CSV to datatable to Excel // Encoding Problem

[Geoffroy_Pantegnies]

Hi everyone !

I’m trying to read a CSV file and to write the data in an Excel file.

I’ve tried two solutions:

• Read Text File > Generate Data Table > Write Range

• Read CSV File > Write Range

For both I tried “utf-8 / 16 / 32” for the encoding and semicolon for separator.

And either it does (nohing)

or (just changing the column name)

Here is the CSV

If anyone as an idea, please share it

Have a nice day,

[indra]

@Geoffroy_Pantegnies Try this in read csv properties choose delimiter semicolon

[Geoffroy_Pantegnies]

Hi Indra,
Thanks for answering.
Yes I have the ‘Semicolon’ in the delimiter (I tried them all), but it’s not working.

[indra]

@Geoffroy_Pantegnies Is it possible to share csv file

[Geoffroy_Pantegnies]

It seems I cannot upload .csv, what kind of informations would you like ?

[supermanPunch (Arpan)]

Hi @Geoffroy_Pantegnies ,

You could try the below as an Alternate :

1. Read the CSV file using Read Text file Activity. Get the Output in a String variable.

2. Use Generate Datatable Activity.

• Here Pass the Output from Read Text file as Input
• Make following Configurations as shown below :
  
  

3. You should get the Output Datatable as you require. You can then Write the Datatable to an Excel using Write Range Activity

[Geoffroy_Pantegnies]

Hello Arpan,

Thanks for your answer

It looks like my problem came form this configuration

Unchecking it was way better but still the last two lines are not converted.

Any idea or that ?

[supermanPunch (Arpan)]

@Geoffroy_Pantegnies ,

Is it possible for you to Provide the CSV file ?

I am not sure, if there are some unusual characters in between the text

[ppr (Peter Preuss)]

@Geoffroy_Pantegnies
we assume that something with the character encoding is going wrong

In RnD rounds we never use EXCEL for checking CSV data and are doing this with a text editor e.g. notepad++. (Excel can change the CSV data, while opening and also will not show us the details)

So in a first round lets replicate and find out the encoding:

sample text encoding: ISO 8859-1 :

will result to:

when changing the encoding to:

we get:

Analysis Steps:

• open CSV file in notepad++
• check the used encoding
• model and adopt the encoding as shown above
• check during a debug within the immediate panel, if the text is correct read in

[Geoffroy_Pantegnies]

The best I can share is :

[Geoffroy_Pantegnies]

Hello Peter, thanks for answering !

The format is as you said:

But the result with your encoding is :

The only activity working is the ‘Read Text File’ followed by
‘Generate data table’ which give me

The format is good expect for the last two lines.

Ps: Nice touch with the French part in your notepad

[supermanPunch (Arpan)]

@Geoffroy_Pantegnies

image755×103 8.39 KB

@Geoffroy_Pantegnies , We Could have the file zipped and sent here.

[Geoffroy_Pantegnies]

Export_08042022.zip (591 Bytes)

Indeed

[supermanPunch (Arpan)]

Hi @Geoffroy_Pantegnies ,

Could you try adding one More Step to the Workflow :

After you have Read the Text file as a String, Using an Assign Activity Perform the Following :

textData = String.Join(Environment.NewLine,Split(textData,Environment.NewLine).Select(Function(x)x.Trim(";".ToCharArray)))

Here, textData is the Output of Read Text File Activity

The above expression is used to remove the Trailing Semicolons from the Text data.

Then do the next steps as is.

But also a Thing to note is that the initial Posted File Screenshot did not have Trailing Semicolons.

Let us know if this doesn’t work

[ppr (Peter Preuss)]

Hi @Geoffroy_Pantegnies

let seperate issues: Character Encoding and dirty characters (les characteres sales) and parsinf issues

for the character encoding maybe other iso / sets are to uses like ISO-8859-15, Windows-1252
here we just RnD it

For the seperation it looks that there are a lot of unneded cols (;;;;;; hint)
In such case we would recommend

• read text
• cleanse text - e.g. remove all ; when it comes in multi occurence, remove "
• parse it to a datatable - Generate Datatable or Custom LINQ

[ppr (Peter Preuss)]

@Geoffroy_Pantegnies
was working so far:

Find starter help here:
ppr_CSVParseBox.xaml (6.4 KB)

[Geoffroy_Pantegnies]

The result was the same with this new activity.
with the assign proposed by ppr juste below works.

Thanks for helping

[Geoffroy_Pantegnies]

Once again it worked fine !
Thanks for your help and your explanations Peter !

Have a nice day.