I do not yet fully understand the problem. If you could upload a workflow with a sample or even a few CSV and/or Excel files that would help us understand the problem it could go some way. A few hints for the start:
-
KNIME seems to be a little bit sensitive when it comes to ‘dirty’ CSV files with imbalanced quotes or separators. I like to use the R pack readr to get around that:
Error with File Reader node while running in a loop -
Some of the data looks like XML files or the cells seem to contain XML values. KNIME can also deal with that, but it takes some preparation. You would have to define one or more XPath schemes. It also could be necessary to ungroup the results (it depends on the structure of the XML data)
Problems with parsing a XML-like file
Json to Table -
the you have some non-latin characters and/or special characters there. You would have to make sure you choose the right encoding (UTF-8) also in the CSV (Text) files and you have to do some experimentation until it looks the way you want it to.
It looks like no easy task but the good news is KNIME has a lot of nodes and options and can incorporate the help of powerful tools from R and Python to help you on the way.