Azure Data Studio (ADS) is a lightweight IDE built on Visual Studio Code. I've written a few articles on how the tool works, and this one continues the series, taking a deeper look at the Import extension.

The other articles in this series on ADS works are compiled into this article: The Mastering Azure Data Studio Series

Complex Imports

I want to look at Derived columns and how I can control my imports. In the previous article, we looked at importing basic files as is to a new table. Let's try to do more. I'll pick a file to import, and at the preview screen, I'll select the Create Derived Column button.

I get a list of columns to choose. I'll select the last two: DataChange and ChangeDate.

I need to give this a name for the column and then create the transformation. I'll use SQL and pick the column names.

This doesn't work. When I click "Preview Transformation at the bottom, I see this. The transformation is using my exact typing.

That's not what I want. Instead, let's use the actual values of the first row. I'll enter this:

Update at 2023-04-09 00:00:00.000

I get something closer. For some reason, the insert rows aren't correct.

Let's add this code on the second line, for the insert operation.

Insert at 2023-04-01 00:00:00.000

This seems to work great.

Let's make one more change. I'll remove the time portion, which might not be important for me. If I change my two lines to this:

Update at 2023-04-09
Insert at 2023-04-01

Now I can get a different preview.

I'll add back the time, since time usually is needed in auditing and click "Done".

The preview looks right:

I'll add my table column changes.

And click Import, which summarizes things and shows this worked.

If I check the table, I see my derived column. What I was after here was loading the code, the name, and then the action with a date in one column. As you can see above and below, this wound up in master. As I was experimenting, the connection somewhere defaulted back to master, which is annoying.

Experiments with Numbers

Let's try experimenting with some numbers. I've got a flat file that looks like this, with order data:

Let's pick this as a source for the Import wizard and look at our preview. We see this.

Now, let's click the Create Derived column. I'll pick the 3 end columns and then enter a total formula in line 1. That doesn't work.

I tried a number of ideas, but ultimately I can only seem to return string data, not a calculation. Sad face. Even if I do the calculation, it gives me something I can't decode.

Masking Data

Let's try another one. I want to try to import this file:

CompanyName,CustomerID,ContactName,City,Country
Alfreds Futterkiste,ALFKI,Maria Anders,Berlin,Germany
Ana Trujillo Emparedados y helados,ANATR,Ana Trujillo,M?xico D.F.,Mexico
Antonio Moreno Taquer?a,ANTON,Antonio Moreno,M?xico D.F.,Mexico
Around the Horn,AROUT,Thomas Hardy,London,UK
Berglunds snabbk?p,BERGS,Christina Berglund,Lule?,Sweden
Blauer See Delikatessen,BLAUS,Hanna Moos,Mannheim,Germany
Blondesddsl p?re et fils,BLONP,Fr?d?rique Citeaux,Strasbourg,France
B?lido Comidas preparadas,BOLID,Mart?n Sommer,Madrid,Spain
Bon app',BONAP,Laurence Lebihan,Marseille,France
Bottom-Dollar Markets,BOTTM,Elizabeth Lincoln,Tsawassen,Canada

I'll pick this and choose the create derived column. Here is what I'll do. I'll give this a name and then put the first name in the specification and x's for the last name. I see this.

'

I like that, so I'll pick Done. I return to the preview and I'll click "Create derived column" again. This time, I do this:

I'll click done again. Now I have this as a preview, with two derived columns.

When I finish the import process, I see this in my table:

I have my extra columns in here, and they contain the derived column data I wanted. I can then move these to a final table using T-SQL.

Summary

The Import wizard is fairly basic, but the ability to add derived columns gives it some usefulness. You have to know that you are just getting text data in those, and if you need calculations for other columns (numeric or date), you'll need to do those once you've imported the data, perhaps as a part of your move to a final location.

The import wizard derived columns is based on the PROSE project, which is part of Excel and other products from Microsoft. It's not perfect, but it's helpful.