CSV/Excel Reader can't convert integer to string

I’ve got a workflow that imports a CSV file and a Excel file to Join them.
Column 21 (and some more columns) occur to have only numbers in them, but not all the time.
When this happens the CSV reader and Excel Reader node get an error

ERROR CSV Reader 0:117:105 Execute failed: The column ‘Column21’ can’t be converted to the configured data type ‘String’. Change the target type or uncheck the enforce types option to map it to a compatible type.

The column type in Excel is set to Standard so this shouldn’t bother the reader.

If I uncheck Enforce Type conversion in the reader nodes, the joiner node is in danger of failing as the types are no longer matching. And as the CSV Reader is inside a loop, the Loop End also fails
String type is fine for me if it would work.

How should I handle this instability in Import files?

Hi @dijkstran , are you able to use a File Reader node instead of a CSV Reader. This then allows you to specify the column data type if it doesn’t match what is “auto detected”.

Click on the column name where you want the data type to be changed:

You can change any problematic columns to String and hopefully this would resolve your problem.

One word of caution though, if your file is on a network share, and you are feeding the path name to File Reader using a flow variable, I think you will have to convert the flow variable to a URL using a String to URI node. See CSV reader - I need a string - #7 by takbb for an example, if required.

2 Likes

maybe you can provide a sample.
It’s rather unusual that a string conversion does not work (most often its convert to number which might cause issues)
br

1 Like

This is the solution for the CSV reader. thanks!
(the change to URI was easy, but the default column names we’re a bit of a struggle going from Column# to Col#)

Nice link to that topic too, can’t believe I missed that one.

The Excel Reader node has the same Transformation tab as the CSV Reader did. It seems to have the same limitations. Is there a replacement for this

1 Like

@Daniel_Weikert sorry the data contains addresses so I didn’t share it here (privacy related).
If this problem remains I might make some fake data containing the problem.

I do have a screenshot of the CSV reader loop

It becomes clear to me that it might not be a failing conversion problem.
It’s more like the new Excel and CSV readers are autodetecting an integer and the Transformation tab within the node does not contain an option to convert Integer to string.

Hello @dijkstran,

I suggest to take a look at Table Validator (Reference) node in order to make sure your table structure is as expected.

Br,
Ivan

1 Like

With 4.4 this problem will be resolved. Please excuse the inconveniences.

Best
Mark

1 Like