Quick CSV Import with dbaTools – #SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Recently I was searching around for some sample data for a project. I stumbled on a CSV of data from the Internet Movie Database (IMDB). I wanted to quickly import this into a database to play with and query. I decided to use dbaTools since my csv python skills were a little rusty and I thought this would be quick.

This post looks at how easy this was.

Disconnected from the Internet

I had downloaded the CSV before getting on a plane, but once I was ready to import this, I had no access to the Internet. This meant I had no access to looking up commands or documentation. As a result, I decided PowerShell and dbatools would be the easiest way to do this.

I ran a quick Get-Help command and got the name of the command. Using wildcards, I could see the dbatools result below.

2021-10-27 20_30_29-cmd - powershell

I then ran “Get-Help Import-DbaCsv” to get the syntax. Using the results below, I wanted to see how quickly this would run.

2021-10-27 20_31_16-cmd - powershell

A Quick Experiment

With the docs above, I created a database and then entered this command.

$movie = Import-DbaCsv -SqlInstance localhost -Database IMDB -AutoCreateTable -Path "IMDB-Movie-Data.csv"

This was my experiment. Just try some values and see what happens here. This ran in less than a second, and from ADS, I could see the data had been imported.

2021-10-27 20_33_42-● SQLQuery_1 - ._SQL2019.IMDB (Integrated) - Azure Data Studio

When I checked my variable, I saw that things had just worked with the 1000 rows imported in less than half a second.

2021-10-27 20_34_30-cmd - powershell

Success!

This wasn’t my main task, but using some tools and some past knowledge, I figured out how to accomplish this task quickly and get back to the process of writing SQL code to query the data.

SQLNewBlogger

My entire purpose here was to write some queries against this data, but I needed to import the data. Either ADS or SSMS have import wizards, but I’ve had various levels of success at times with them. I didn’t want to work through an ETL process. Once I saw how quick dbatools made this, I decided to write this post, based on the ease of getting something done.

You could easily duplicate this post, noting why you needed to do an import and how easy this was. You could compare this to SSMS or ADS, or even write about starting to use dbatools for this purpose.

This took me less than 10 minutes.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

8 Responses to Quick CSV Import with dbaTools – #SQLNewBlogger

  1. JeffModen says:

    Do you have a link to the database you speak of? I’d love to do some performance testing.

    Like

  2. way0utwest says:

    This was a single table I added to a database with some other info. I need to get some more data, but I was glad someone had pulled down a piece of the data. I want to normalize and break this up, and add more, but you can get this here: https://github.com/laxmimerit/All-CSV-ML-Data-Files-Download

    You can get the dataset (zipped) from imdb as well: https://www.imdb.com/interfaces/

    Like

  3. Salam Elias says:

    Thanks Steve, I tried different syntaxes but always getting “.\Jobs.csv cannot be found” or .”[Import-DbaCsv] C:\temp\Jobs.csv cannot be found”….. any idea? Thanks

    Like

  4. way0utwest says:

    Pathing in PoSh can be difficult. It really depends on where you are running the cmdlet from and where the file is located.

    Like

  5. way0utwest says:

    That’s a longer post. You can import tsv files: https://docs.dbatools.io/Import-DbaCsv
    You need to create your own db. If you mean how do you structure these, likely you want to create the tables you need with specific names and link them with PK/FKs

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.