Type conversion with Transformation Flow

VisionBI | Jurgen shared this question ago

Answered

When we import data from Excel (export to CSV) with itemnumbers inside we have a problem converting them. If we have a leading 0 we have to put in a line with text and ignore that line, otherwise it will be numeric. When the metadata says its text and we remove that line, it produces an error because the metadata is not correct.

Why can't we just specify each column type during the import step? Or is there a way to force this?

The same question

Replies (1)

Nathan Schroeder ●

Hi Jurgen,

The CSV importer will always have a bit of guesswork as to what the data types are (it will base it on a preview of the data (depending on precision) so if there is a text row in there, it will default the column to text).

After import, I would recommend using in-line transformations to ensure that all of your data-types are as expected.

4b11a1ff622f33cd696569289c093f0b

Let me know if this works for you.

Regards,

Nathan

Reply URL

VisionBI | Jurgen ●

Hi Nathan,

is it possible to do a Numeric to Text? The problem is that the leading 0 is removed. Also it's not possible to store the Numeric data as Text, so if you want to use the output with a join to a table where the field to join is actually a text field, it doesn't work.

Regards,

Jurgen

URL

Nathan Schroeder ●

Hi Jurgen,

Yes it is possible, but In cases such as this, I would recommend erring on the side of using numeric values. When it comes to report building it is far easier to go from numeric->text than the reverse, and leaving everything as numeric (if possible) will keep your options open.

Nathan

URL

VisionBI | Jurgen ●

Hi Nathan,

We can close this call. If we can't determine type during the read, but have to do it afterwards, it won't help.

We decided to recode the itemnumbers so we never start with a 0.

Regards,

Jurgen

URL

Nathan Schroeder ●

Hi Jurgen,

That sounds like a good solution. Closing this.

URL