In KNIME, Is there a way to search for non-ascii characters in a csv file

sgilmour · May 8, 2020, 2:26pm

HI,
I have a 128505 row of data with columns A-K. When I add it to my python script the file is being rejected because of non-ascii characters. Is there a way to search for these non-ascii characters in my csv file using KNIME or if need be I can do it in my excel file.
Thanks for your help,
Scott

izaychik63 · May 8, 2020, 2:39pm

This could solve your issue in Python.
https://linuxconfig.org/syntaxerror-non-ascii-character-python-with-utf-8-encoding

sgilmour · May 9, 2020, 3:43am

Thank you for the info. I would prefer to remove the non ascii characters rather than excepting them in my code.

ipazin · May 11, 2020, 2:57pm

Hi there @sgilmour,

you can read data into KNIME, remove all non-ascii characters with regex and write it back.

Br,
Ivan

sgilmour · May 11, 2020, 3:29pm

That would work. I am up for a good challenge. How would I go about doing that? Which Nodes would I need to setup to configure. When you say read data into KNIME can I do this from my CSV file?
Thanks for all your help
Scott

ipazin · May 11, 2020, 3:54pm

Hi @sgilmour,

first read data into KNIME using File Reader or CSV Reader (this can not be done from within CSV file). Then you need to create loop to loop over every column and apply appropriate regex (this one could work: regexReplace($column_name$,"[^u0000-u007F]+" ,"" )) in String Manipulation node to remove all non-ascii characters. When done use CSV Writer to write data back. For column looping check this example:

Br,
Ivan

sgilmour · May 11, 2020, 4:49pm

Hi Ivan,
I took my file I created with CSV Writer and imported it into my CSV Reader and I am already getting an error in CSV Reader.
Execute Failed: New line in quoted string: (for closing quote missing) in line 11.

I know I have a lot of cells that have a need for a new line that would need to be changed since I have over 100,000 rows of data. Is there an easy way to fix this issue when creating my file in CSV Writer.
Thanks
Scott

mlauber71 · May 12, 2020, 5:10am

You could try and use R with the readr package

Or you could try and use the file reader

Or you could try the advanced settings and allow non complete rows (citing from memory since I am at my mobile device at the moment)

ipazin · May 12, 2020, 1:44pm

Hi there @sgilmour,

you created this file with KNIME? If so then you can try to remove non-ascii characters prior to writing file with CSV Writer node?

Br,
Ivan

sgilmour · May 12, 2020, 2:35pm

Thanks.
Is there a recommended node I should use?
I am going to try the file reader node rather than the csv reader and try the loop option that was mentioned before.

ipazin · May 12, 2020, 4:19pm

Hi @sgilmour,

try approach and nodes from my second reply.

Br,
Ivan

sgilmour · May 12, 2020, 4:23pm

Hi Ivan,
That is what I am trying now. File Reader ----- Column List Loop Start ------String Manipulation ---- Loop End
Just working on how to configure the nodes

sgilmour · May 13, 2020, 11:00pm

In String Manipulation it is listing all my columns as one big column. How do I get them to list as seperate columns.
Thanks
Scott

ipazin · May 14, 2020, 1:43pm

Hi @sgilmour,

so if you check your data after File Reader node you’ll see that all your data is in one column. You can either define column separator in File Reader node or use Cell Splitter node after you read your data. Instead of column_name in String Manipulation node you should then use real column name.

Br,
Ivan

sgilmour · May 14, 2020, 4:18pm

The Cell Splitter worked. and then I did the column list loop start node

Then I added the node string manipulation and it adding all my columns as 1 again I was going to add the regex expression to each column.

Here is what I have so far for the workflow.

ipazin · May 14, 2020, 4:37pm

Hi @sgilmour,

check Split input column name for output column names in Cell Splitter node to have proper column names (if haven’t already). Also you can filter columns you don’t need prior to loop. Then check example workflow I linked in one of my previous replies regarding how to loop over multiple columns and applying same manipulation over each one of them.

Br,
Ivan

sgilmour · May 16, 2020, 11:47pm

Hi Ivan,
Is this what you were thinking. I am still working on configuring but wanted to make sure I am on the right track.

sgilmour · May 18, 2020, 1:38pm

Hi Ivan,
I modeled my setup after the Looping over all columns and manipulation of each workflow. I am getting stuck when I reach the Math Formula Node. it is giving me an error no such column: Anonymous column when it is added in n the previous node.
Column List Loop Start

Then the Column Rename

Then the Math Formula Node

Then I get the error

Do I need to add something to my workflow to get it to work?

sgilmour · May 18, 2020, 1:50pm

sample output of file.
test_output.xls (26 KB)

ipazin · May 18, 2020, 2:02pm

Hi @sgilmour,

yes! That is the one I was thinking of. You need to use String Manipulation instead of Math Formula node. Also seems to me you should use Column Filter node to remove all these columns you have in Exclude in Column List Loop Start node.

Br,
Ivan