Filereader in KNIME 4.0.1 quotes semicolon error

Hi for a project I have to use an older KNIME version 4.0.1 and trouble to read a csv file correctly. The data is semicolon separated but with lots of missing values and has also semicolons within quotes.
Unfortunately the file is very large and highly confidential so I do not have a sample currenlty.
I tried this post

but the filereader keeps the quotes or does split the semicolons in quotes which messes up the columns

I know it’s difficult to help without data. Just want to know if someone had that issue and a solution for that?

I also tried out CSV reader but in this version it’s not a good option as well.
Thanks and best

@Daniel_Weikert you might have to experiment with the advanced settings of the CSV reader.

You also could try and use the R package readr which often helped me in the past.

1 Like

Hi @Daniel_Weikert, back then, I used to favour File Reader over CSV Reader, even for csv files, as I thought it gave me a bit more control.

When you say there are lots of missing values, do you mean you have lines like ;;;;;? That should be ok. When the delimiters themselves are missing, then you’re in trouble.

Semicolons within quotes should be considered as data, right?

What kind of trouble are you facing? What data is breaking structure?

@bruno29a
In that version the file reader is better then csv but is not working correctly. The missing values itself are not an issue (even though there seem to be "short lines included as well)
The issue with the file reader is that e.g one column has
x;y;z;;;“a;b”;;;e;f

then a and be is also split in 2 columns which leads to the mess up of columns as the structure does not fit to the lines above and below anymore
a;b when in quotes should be a;b in one column
The settings in the file reader which should help (according to the link as well) do not work.

I tried to replicate just one line like that in the new version and the csv reader has no issues with that.
KNIME is great for sure but I tell you when you work on a newer release you don’t want to go back to the older one :roll_eyes:

1 Like

Hi @Daniel_Weikert , as I said, I used to use File Reader over CSV Reader back then, but I don’t remember for what reason, but I know that File Reader was working better for me.

However, if the CSV Reader of Knime 4.0.1 can handle real csv files, that is comma separated, then you could try to replace all semicolons to commas.

You could try to open this via CSV Reader and see what happens:
x,y,z,,,"a,b",,,e,f

If CSV Reader opens this properly, then you can try to replace all semicolons to commas first, via Line Reader, and then open via CSV Reader. CSV Reader should know that “a,b” is 1 column, instead of 2.

The reason probably is that in the old version the file reader is the default when drag and drop text files as the csv reader is “not that good” in that version.
The line reader does not help because it does not support choosing the file format (utf8)
CSV reader runs into error (I tried all combinations).
I guess I have to use a way outside of KNIME for that
Anyway thanks for the ideas, appreciate it.
br

That’s unfortunate about the file format for the Line Reader @Daniel_Weikert .

Maybe you can do the replace outside of Knime (notepad, or any editor), and then use CSV Reader

You still could try and use readr with R and KNIME. It would also allow you to choose several configurations (locale) that might help you import the file.

knime.out <- as.data.frame(readr::read_delim(file_location, delim=";"
, locale = locale(encoding = "UTF-16LE") , col_names = TRUE))
2 Likes

Thanks,
just need to find out if I am allowed to use R or Python and IT allows installation.
br

2 Likes

Hello @Daniel_Weikert,

v4.0.1 was indeed some time ago but still think File Reader should be able to read file formatted as you say. Can you create dummy csv file (5-10 lines should be ok) so we can give it a try?

Br,
Ivan

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