Why my CSV Writer is written in gibberish?

[Banksy]

Hello everyone,

Why my CSV Writer is written in gibberish?
Thanks in advance.

[mlauber71]

@Banksy by any chance could you provide us with a sample of that data without spelling any secrets. I assume you have tested other encodings as well?

What does the CSV file look like when you open it again in KNIME and what kind of editor is it you are using to check the file? Would this editor support utf-8.

[Banksy]

Hi @mlauber71

I have tried other encoders and my data is read from a SharePoint list, not a CSV file. I want to write the SharePoint List data as CSV files.

[mlauber71]

@Banksy could you provide us the data in the form when it is still in the KNIME workflow where the encoding seems to work. You might want to store it in a KNIME table. Maybe nit store the Sharepoint connector in an executed state since it might contain sensitive informations.

Also the question how the CSV file does look imported back into KNIME. And what version and (operating) system are you using?

[Banksy]

Hi @mlauber71

I am so sorry. I can’t upload files due to my company restrictions.

I rewound the CSV back to KNIME and it worked.

My KNIME is 4.5V, my system is Win10.

[laaaarsi]

Hey @Banksy,

if I got it right, your are writing a csv file (source is a Sharepoint List) encoded with UTF-8 and if you open it in Excel it is messed up? Reading the file back into knime (encoding UTF-8) and the file is read correct?

Sounds more like an issue with excel, what you can try is to import your csv (https://answers.microsoft.com/en-us/msoffice/forum/all/how-to-open-utf-8-csv-file-in-excel-without-mis/1eb15700-d235-441e-8b99-db10fafff3c2) and change the encoding during to UTF-8 that process. Iam assuming excel is using some default encoding which does probably not correspond to UTF-8, but this is just a guess.

Best regards
Lars

[bruno29a]

Hi @Banksy , it definitely sounds like it’s Excel that’s not reading the file properly. In addition to what @laaaarsi said, can you also try to open the CSV with another editor, such as notepad? Does it display well there?

[Daniel_Weikert]

@laaaarsi is correct. I experienced the same issue. Excel has it’s own encoding which is not utf-8 by default.

[Banksy]

Hi @laaaarsi ,
Thank you for your help, you are right. But I according to the method you provided, the text can be displayed normally, but the position is confused.

use notepad open

use your way open

[mlauber71]

@Banksy exporting complex data to an actual CSV file, that is with comma separation and then importing it into excel is possibly the worst way to transfer data that I can think of.

Have you tried just using the (new) Excel writer?

I would suggest you create a sample file with dummy data that would represent your challenge and post it here so we would be able to narrow down the technical problem and might be able to offer a solution. Also it might be interesting to know what the final outcome or format should be (CSV, Excel) and where is the data headed next. Other formats like Parquet or SQLite or ARFF might be better suited to store column types and all sorts of encodings.

[Banksy]

Hi @mlauber71
I got it, thank you so much for your help!

[bruno29a]

Hi @Banksy , as per your screenshot, the CSV file was written properly.

If your end goal is to open the CSV file from Excel, then I would use an Excel Writer instead, as per @mlauber71 's suggestion. It does not make sense to write as a CSV file in this case. But if you are doing an export to a file that will be imported into a database, then a CSV file would make sense. Just don’t use Excel to view it.

So, it all depends on what you will do with the exported data.

[Banksy]

Hi @bruno29a
I got it, thank you so much for your help!