Initially, I thought I would have to use sqlcmd because I’m on a Mac and don’t have SSMS. It turns out Azure Data Studio has a nifty way to import data from flat files – yay!
SQL Server Import Extension
You will need the SQL Server Import extension. This extension works with CSV and TXT files.
Once you have that installed, you can right-click on the server or db to get the wizard option. I discovered this after I started down the sqlcmd path. Yes, you could also use sqlcmd on a Mac to do this, but I decided this was easier in the end, especially when I had to sort out data types for columns.
Also, note I couldn’t import a file when I was connected via Azure Data Studio with an AD account and had to use a SQL account.
Import (Flat File) Wizard
To start, right-click the db you want to import the file into, then choose Import wizard:
This takes you step-by-step to import your file.
Step 1: Specify Input File
This step lets you choose the server and db, which should be pre-populated based on the server/db you right-clicked on to start this process. It will default to the master db, so make sure to choose the db in which you want the table created. Then, you select the file’s location, along with the new table name and schema. You can’t import to an existing table.
Step 2: Preview Data
This step allows you to preview your data.
Step 3: Modify Columns
This step allows you to specify your column types. It doesn’t select the optimal data types and whether or not to allow nulls. I had to iterate through this step, having it fail repeatedly until I got the types right. I also had to do some cleanup on my CSV to tidy it enough to import to the chosen types.
Step 4: Summary
If there are any errors, it will list them on the next page. Make sure to click the previous button to go back to correct errors so you don’t lose your progress.
When it’s finished importing, the summary screen will show success. The wizard imported 2069 rows, matching the row count in my CSV file.
If you are curious about the data used for this exercise, it’s from the School Shooting Safety Compendium. I wanted to do some of my own simple visualizations, which will be provided in a future post.