Hi,
I am trying to run a project that exports a table from Excel to a table in MSSQL. I am using the inbuilt table creation in the EasyMorph Export to Database Command transformation.
EasyMorph correctly identifies datatypes but, for text, the length of field is limited to 255 and there doesn’t seem to be a way around it within EasyMorph itself.
I have found a work around which is to create the table via EasyMorph, then go into SQL Server Management Studio, Switch to the tables design mode, and alter the length. I then go back into EasyMorph and set the behaviour to keep the table and delete the data.
We have many users now using EasyMorph - Not all of them have the ability to alter the tables in SSMS.
Is there a way of changing the text length from within EasyMorph?
Thanks,
Daniel
Hi Daniel,
in the EasyMorph installation directory there is a file named DataTypesMap.xml. This file contains default data types for different databases types. You can alter it to using, say, NVARCHAR(512) as the default type for text fields.
Thanks Dmitri,
Could I therefore raise as a feature request being able to do this within the GUI on a field-by-field basis? i.e. having field set as 255 by default (but showing the length next to the TEXT title), and if the TEXT tile is clicked, a dialog pops up with the facility of altering the length?
We have a number of users that are not tech savvy, plus field lengths above 255 are an exception to the rule so, it would not be good practice to increase this as a default…
Thanks,
Daniel
Advanced data types for database table creation is already on our product roadmap. It won’t appear in versions 3.6 and 3.7 though. Probably, closer to 3.8. We definitely will have it implemented – it’s an important feature.
1 Like
hello, in case it’s useful, one way I solved this was to count the amount of characters in the columns and divide by 255 creating several columns with the sequential name. Allow me to give an example, if the character count is 1200 we will have to create 5 columns “column name” + slip (example: column_split1, column_split2) and upload to the database as a temporary table, after that, I run a custom sql command that unifies these “slipt” columns into a single column in the database, ending this step, the temporary table is reset and I keep only the final table with the unified columns in the database.
if it’s useful, I can detail how my process was done.
I don’t remember if I managed to leave the creation of the columns “splits” dynamic, for example, if I need 5 columns it happens automatically or if it is a manual configuration.
Hi Matheus and welcome to the Community!
Thank you for sharing a workaround, but fortunately, there is no need for it. The advanced data types for table creation have long been available in EasyMorph. They allow creating database fields of any supported data type, so you can specify the necessary text length.
