Importing Data With Azure Data Studio

By now, you’ve probably heard of Azure Data Studio, the lightweight, cross-platform tool for data professionals to connect to, query, and otherwise explore data sources for all sorts of databases, and not just Microsoft SQL Server (see below). I love it, and I’m happy to see people using it either where I work or out in the wild.

Still, I get feedback from people all the time that they don’t think it’s a good replacement for SQL Server Management Studio (SSMS). Without starting an argument, I don’t think of it as a replacement; I view it as a side-by-side tool with a stronger focus on development than something like SSMS where I can do more administrative work. But that doesn’t stop people from pooh-poohing it, complaining about things it can’t do compared to what they’re familiar with.

One thing I’ve overhead is that if you want to load data to a database, you’re still locked into using a tool like SSMS to import or export data. It turns out that you can, but you just need to think fourth-dimensionally, Marty.

Extensibility

If you’ve only downloaded the binary for Azure Data Studio, loaded it up, connected to a server and ran a query, you may have missed out on one of the best things it offers: extensions.

Over on the left-hand side of the UI, there’s an icon that lists extensions. If you open that up, there’s a decent amount (and growing almost every day) of extensions you can add to your client. Just like Visual Studio Code, you can add extensions to add new functionality and features to your installed editor. For instance, did you know you can now connect and work with PostgreSQL databases in Azure Data Studio?

Likewise, there’s also an extension for importing flat files in your databases, too. Let’s give it a whirl to see how it all works.

Demo Time

So let’s say we’ve got ourselves a good ol’ comma separated file. Maybe it even looks like this:

First things first, we’re going to go and grab the latest “SQL Server Import’ extension from the extension marketplace. We’ll go ahead and click on the “extensions” tab on the left hand side, and we’ll just search the marketplace for “import.” Here’s what I’m seeing:

(Side note: as of the time of this writing, there aren’t many extensions in the marketplace for Azure Data Studio, but it’s growing rapidly. Your search might be a little trickier, or, the name of this extension might change. I’d recommend sticking with the Microsoft-supplied one, but hey, there might be something better, and if you found one, tell me!)

If your extension installed successfully, you should now have a new context menu when you right-click on your database name in Azure Data Studio:

So let’s follow-through. After we click this option, we’re going to get a wizard to walk us through the import. Most of this is pretty self-explanatory, but here we go. One thing to note: you *have* to import your data into a new table. I have no clue as to why, but that’s just how it works (for now?):

If you did it right, you’ll get a preview of your data file on the next step. Hi, Erik!

With that done, you now move on to the tedium: specifying your import schema. This is by far the hardest part of this process. Granted, our file is small and we don’t have a lot of fields, but imagine how this would look if you had more. On top of that, the wizard sometimes doesn’t make the best decisions about your data types, either. For instance, in my source data I had the word “Yes” in a column and this wizard wants to translate this to a bit field. Maybe you want that, maybe you don’t. So take your time and know what you’re picking.

Once you’re satisfied, click import data! If everything worked, you’ll get a happy checkmark. If not, you just get a generic error. Why did it fail? Who knows! Maybe your table already exists, maybe you have a data type conflict somewhere. The extension isn’t really fully functional here, but I’m hoping it improves.

Once it’s done, you’re off to the races. You can start querying your new table just like you would anything else in Azure Data Studio. Since we had to drop this data into a new table, we could easily do some work to get this data where you really wanted it.

How do you want it?

Even though this blog post set out to talk about how it’s entirely possible to import your data with Azure Data Studio, there’s a bigger theme at play here: thanks to the solid foundations Microsoft built upon, there’s an ever evolving landscape of capabilities that you can add on to the core concept of Azure Data Studio. If you dismissed this product early on, you owe it to yourself to take another look and continue to experiment with it. Also sharp-eyed readers might have noticed that these screen shots were taken on my Mac; I love the cross-platform portability this product gives me, and for every day SQL development, it’s great.

I look forward to the continued evolution of the tool, and with some dedicated help of the community we’ll continue to see really awesome support for all kinds of features. Heck, if you’ve got a good idea for one, why not make it yourself?

And finally, I’ll be the first to say it could be better in some places. But guess what? Microsoft listens, so go open some issues on their Github project page. After all, it’s through feedback that this tool continues to get better. All of this wouldn’t be possible with the constant effort of the SQL Tools Team. They rock, and they deserve a ton of thanks!