How NOT to change separator inside quotes

I’m trying to build a datatable with “;” as separators, but when I generate datatable, it creates 4 columns, when I need only 3. Inside the message box is the right datatable, with 3 columns. I built a datatable adding data rows with 3 columns.

Captura de tela 2023-01-25 221807

I don’t know where I’m wrong. Generate data table activity is below:

Right after is the result in .csv file
image

The objective is converting the .csv file in .ofx file on website csvconverter.biz, but the converter is detecting 4 columns, splitting the decimal value in two pieces:

Hi,

Can you try to turn on CSVParsing property as the following?

Regards,

@Matheus_Dallasta

Try wit csv parsing checked…then double quotes might be considered as one string

Cheers

Hi @Matheus_Dallasta

The reason why it creates 4 columns is that you’re second value contains “,” (i.e. 851,0000)

I suggest you try to replace that first before generating a datatable.

Hope this helps.

Kind regards,
Kenneth

Yes, I tried

The result inside .csv is ok, with 3 columns…

However, the converter for ofx is still dividing the decimal value into 2 parts…

The input file is a .txt with “tab” as separators…

And when I generate data table, this activity change “tab” to “comma”, and I cannot choose the final separators.

If I could choose, “semicolon” (“;”) would be the right choice.

Hi Matheus Dallasta,

If we cannot find a way to not parse the detected “,” comma delimiter in the second column, why don’t you try replacing the comma in the second column prior to passing to ofx? This would be after the Generate Data Table activity.

You could do this with LINQ statement or a For Each Data Row Activity, replacing that comma in the 2nd Data Column with an empty string.

In the end you can pass the modified DT, that will not contain this delimiter to ofx.

Otherwise, please show us your activities after Write CSV which passes the columns to ofx so we can understand the format required.

Would it be possible if you can share a sample text file?

Ok, the text file
PagSeguro 21-01-23 10-48.txt (1.4 KB)

Then I generate datatable
Then filter datatable chosing column 1 (description), 7 (value) , 9 (value), 13 (date) - with output (dt_CSV)
For each row of dt_CSV
Then I Build datatable (dt_Xlsx), then Add data row for first value (column index 1)

{row.Item(0).ToString,row.Item(1).ToString,row.Item(3).ToString.Substring(0,10)}

Another Add data row for second value (column index 2)
{“Tarifa de cartao”,row.Item(2).ToString,row.Item(3).ToString.Substring(0,10)}

Then write csv file, and the result is here:
image

Ok, found solution:
The activity build data table add comma as column separators.
I add all header and next Add data rows in only 1 column with the separator I wanted (“;”)

image

Changed for this
image

Right after in Add data row - ArrayRow
{row.Item(0).ToString,row.Item(1).ToString,row.Item(3).ToString.Substring(0,10)}

Changed for this
{row.Item(0).ToString+“;”+row.Item(1).ToString+“;”+row.Item(3).ToString.Substring(0,10)}

Result in .csv file…
image

And on csvconverter

Thank you all !

Glad you’ve found the solution.

Kind regards,
Kenneth

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.