SQLServerCentral Article

Using the Import Extension in Azure Data Studio


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 I've got a few more coming. I covered a basic orientation and a more in-depth one on some of the code writing features. In this one, I wanted to cover a handy extension that makes it easy to import files, something that many of us do regularly. This article will look at installing and using the Import extension with csv files. A future article will look at other file types.

I've written a few articles on how ADS works, shown here:

You can download the tool and read those articles to learn about how to use this in writing code. I've gotten more comfortable with the tool, and while I still like SSMS, there are some features in ADS that I prefer.

The Import Extension

In Azure Data Studio, there is an Extensions item in the side bar. For me, that's on the right, but once you click it, you should see a list of installed extensions and the marketplace. Or at least those entries if you've collapsed them. In the image below, I have collapsed my installed extensions, but you can see a list of Marketplace extensions.

ADS Extension Panel

If I type "import" into the search at the top, I will see the extension I want. If I click it in the sidebar, the main description page will open up. As you can see, this isn't installed as there is an "install" button listed. Below this I have some information, such as this includes the Import File Wizard. Click "Install" to add this to ADS.

Finding the Import Extension in the marketplace

Once this is installed, this can be launched by clicking Ctrl+I.

Using the Extension

This extension works in a similar way as the Import Flat File Wizard works in SSMS. If I connect to an instance, I can press CTRL+I, or I can right click a database in the server explorer. You can see the import entry in the right click menu below and the shortcut listed.

Right click menu in Server Explorer with Import item

Once the wizard opens, I have to set some parameters. If I've opened this from a connected session (as I did), the instance and database are selected. If I right click and select "Import wizard", I get a connection dialog. Poor context awareness, at least as of Nov 2023.

Import wizard first screen

I don't get to pick an existing table, so for me, this is often a wizard I use to load data, and then I will move it with T-SQL to a destination and drop this staging table. If I have a problem in the import, I'll need to drop the table before I can re-run this and try another load.

I'll pick the file I have, which is a csv, and then give a new table name. You can see the details below. I've also attached my countrylist.csv file in the Resources section.

Import Wizard with fields filled in

When I click Next, I see a preview of my table. This looks good, as I just have two columns. If I had more, I could scroll right to see them. I'll cover the derived column later in this article.

data preview in the wizard

The next step is to structure my destination table. This defaults to the column names in the preview. In my case, you can see my file below and I had no header, so the columns are column1 and column2.

csv country data

The wizard shows me the two columns, and I can make changes here.

default wizard column structure

In my case, I want to change these. The first one is a code and the second one is a name, so I'll change those. I'll also set the first column to be the PK and not allow nulls. You can se these edits below.

table structure modified in the wizard

Now we can import the data. I click Import and the data is pulled in quickly. You can see I receive a summary as well as a message data was imported.

summary of data imported

If I close the wizard (click Done) and then run a query, I see data in dbo.countrylist. You can see in the image below I'd already created a table, so I could insert .. select from countrylist into countrycodes.

checking the data is loaded in ADS

Everything worked, now let's make a few changes and try different things.

Headers in a CSV File

What about headers in a CSV file? Let's add some. In countrylistheader.csv, I've added headers. First, I was wondering why the first row wasn't picked as a header before? There was no checkbox in the wizard to mark a row as a header. I'll add a line to my file like this:

upper case line 1 as header

As you can see in the preview, this isn't detected as a header.

wizard preview not detecting header

Let's cancel and change the file. Now I'll use proper case.

header line in csv as proper case

If I run the wizard and see the preview, I see this:

wizard preview detecting header

That's interesting. Let me try one more thing. I'll now change the file to not use upper case for the name. My data now looks like:

new csv formatted file

The preview works.

preview working

If I change my header row to be all upper case, this still works:

preview working with upper case header

I can only imagine that there is some light detection here of whether the first row looks like data or a header. I imagine that if there is some difference in rows 1 and 2, row 1 is detected as a header.

Derived Columns

I will add two new columns to my CSV. These are auditing columns, which I want to derive something from on import. My data looks like this:

csv with four columns

Now I'll choose this file in my Import extension. When I do, I see this preview.

csv preview with four columns

I'll click "Create derived column" at the bottom. This brings up some instructions, which you can see below.

derived column instructions

I'll click one column, the DataChange, and I see this. I get a list of data with a new column name at the top and a type of transform.

initial derived column chosen

I'll try something. Let me put in a value for update and insert. I'll use single characters.

initial transformation

If I click preview, this is expanded to the rest of my rows. Interesting.

preview data transformations

That's interesting. I'll stick with that and return to the preview. I see my new column.

preview with derived column

I can continue with the wizard. I'll change the table.

changing the table

Now let's click import and see the summary.

summary of import

And if I query the table, I can see my data.

checking the import

I can change some data and insert as a new column. If I didn't want the DataChange data, I still have to import it here, and then only move the data from LastChange to a new table. This is extra data, so another reason to drop this table after the import is complete.


I installed and used the Import Extension in Azure Data Studio to load a few CSV files into my database. This is pretty easy, and while I can specify a header, I don't get many options here. It's possible I'd need to modify the the header row, but it's not completely clear how to do this, or what the impact if different changes might be on different types of files.

In the next article, we'll look at these issues and experiment with different types of data, as well as file types.



You rated this post out of 5. Change rating




You rated this post out of 5. Change rating