How to quickly modify all columns in the dataset

1

Hi,

I have already got from you a brilliant formula that helps me to get rid-off double quotes when exporting dataset to CSV file. It works great. Thanks again.

if(not iserror(asnumber([Column])), asnumber([Column]), [Column])

Usually, I have to apply it to many columns in the transformation “modify column”. It has to be done for each column separately.

I am migrating a couple of databases so I am sure you can imagine how painful it would be to click this for every single column.

Do you have an idea how one can do it in a better way? Quicker or more elegant way rather than clicking the same thing for each column in the dataset.

Pop-up window were all columns can be modified at once would work well for me. Maybe there is a better way.

Hope it makes some sense. Don’t hesitate to ask me for more details.

Thanks,
Milan

2

Hi Milan,

What is the source of your initial data? How are they imported into EasyMorph?

3

The source is Microsoft Azure cloud database. The final source has to be csv file that is then loaded to Oracle database by somebody else in other country. It represents our single data warehouse.

4

And all the numeric columns from Azure cloud database are imported into EasyMorph as text? Can you please what data type those columns have in the database?

5

Hi, numeric columns are ok. I have problem when the column is defined as varchar in the source database. EasyMorph then imports it correctly as text. When exporting this file to CSV it exports text correctly, but if the text field contains for some cases just a number. Which happens quite often then it puts double quotes around it.
To get rid off it I apply the formula above. To be honest it is even better formula what I really use.But the issue is that I have to click it manually for all columns. If there were a pop-up window then I can create this formula in excel for all columns and just copy and paste it. The formula I apply is below.

if(not iserror(asnumber(sanitize(replace([eShopFirstName],’|’,’ ‘)))), asnumber(sanitize(replace([eShopFirstName],’|’,’ ‘))), sanitize(replace([eShopFirstName],’|’,’ ')))

6

Hi Milan,

Thanks for the details. I just wanted to make sure that import is working correctly.

Right now I can’t think of an easy way to modify several columns at once. But we a going to release “Text to numbers” mode for the Convert Data Type transformation in the next few weeks. I hope that it will make your task a little easier.

Also I think, that it’s possible to replace all the vertical bar characters at once with the Table-Wide Replace transformation.

7

Hi Andrew,

Thanks for your quick feedback,

Great tip with this table-wide replace. I will use it.

I have found the way how to replace these double-quotes. As I am running the EasyMorph on Windows 7 then I can use PowerShell to replace it for me once the CSV file is exported.

There is the very last thing I probably cannot do for all columns in a single operation and it is the function SANITIZE. I use this one to make sure I do not get any hidden “crazy” symbols or enters (new line) in the character variables. New line makes CSV look wrong.

Is it possible to apply this one using the table-wide replace too? I mean new line because I do not know how to specify new line when defining this replace.

Thanks,
Milan

8

Milan, unfortunately, it doesn’t seem to be possible to remove or replace newline characters with Table-wide replace transformation.

9

That’s ok. Thanks for your quick feedback. Thanks, Milan