About the maximum length defect at the time of import

[Ro5]

Hello.

The other day, when I tried to update by importing an import set into a table with a maximum length of 200 data, some data was cut off at 40 characters.
I once again checked the maximum length of the import set table and the maximum length of the table to update, both of which had the same maximum length of 200.
Just in case, I set the maximum length to 200 again and imported it again, but this time it was imported normally.

This phenomenon has occurred multiple times so far.
Do you have any idea what the cause is?

Thank you.

[Community Alums]

Hi Ro,

Try to change the max length to 256 first and then try to put it to a bigger value after.

It seems that if you don't put it to 256 first, the change will not occur at the database end (which you cannot change yourself).

As changing from 40 chars to say 400 requires a change to the field data type.

Also, manually change the field length for source table (u_imported_widgets) .

Mark my answer correct & Helpful, if Applicable.

Thanks,

Sandeep

[Tony Chatfield1]

Hi, this is most likely because fields in the temp import table were created with a default size of 40,
and this is a not uncommon issue when importing into a new temp table.
You can resolve (as you did)  this simply by updating the field size of any impacted fields in the temp\import table so that your source data is not truncated and importing again.

To prevent this from occurring, the first time I load data that generates a new transform table I normally

• Import a copy of my data to generate the import table - but do not transform the data.
• Validate the field sizes of the new table are correct and update the import tables fields if incorrect.
• Drop the initial imported data if fields are truncated
• Reload the data and this time run the transform.

[Ro5]

Hello, Tony Chatfield.

Thank you for your answer.
As a side note to the question, I didn't create a new import set table this time, I did an import using an existing import set table. Then this phenomenon happened.
Based on that, do you have any idea what caused this problem?

Thank you.

[Tony Chatfield1 · Accepted solution]

Hi, I would not expect any pre-existing import\data table to have field sizes changed by system.

Were the impacted columns new\additions to the import?

Assuming your issue is the import table field size has changed.
Have you checked\validated previously imported data to see if the impacted fields were truncated and it went unnoticed?
Or checked to see if anyone has made accidental changes directly or via an update-set?

Otherwise
Is the data source xls or csv?
If csv you could check to confirm that the file encoding is correct and valid.

Was it the first column that was impacted?
Microsoft product saved (or exported) CSV's are often formatted as UTF-8 BOM (byte order mark) and the first column name may have been interpreted as a new field if you recent import was encoded differently to the first import.
- BOM can normally be seen as an upside down '?' at beginning of the first column name
 -You can easily change CSV encoding with a text editor like PSPad