SSMS 17.3 FLAT FILE

So while at GPUG Summit this week in Nashville, TN Microsoft released to the public SSMS 17.3 and one of the new features is called Import Flat File which you may find full documentation on here.  This new wizard feels a little easier to use compared to the Import / Export wizard that has shipped with SSMS for a long time.  In this brief post I’ll walk you through a simple use case.

 

Let’s say you need to import the average weekly price of diesel across the US since 1997. You can visit EIA.gov scroll down to se the most current three week average. In the top right though is an option for full history, click on that and save the document to your desktop.

image

 

From here I opened the Excel document and copied the contents of the worksheet and pasted them into a new Excel document. Then from our new Excel document I chose to export the data out as a .CSV file as shown in the 2nd image.

image

image

 

So with our new source data I open up SSMS and get logged into the target database I want to load this data into. In my demo instance I’m going to simply load this into a test database called SQLSHOOTOUT.

image

When you click on Import Flat File you are greeted with a nice splash screen outlining what the wizard does.

image

 

My first step is to point the wizard to the source data which it’s important to note since this is done via SSMS the data is local to the device you are connecting from. I select browse and choose my file. When I click OK the wizard assumes you want to name the table the same as your file name. You can change this if you wish. We then click on next to continue.

image

Now we get to see a preview of our data.  Now in my import file some of the column headings had some crazy characters like () in the name, the wizard has removed those for me. I take a peak at the data and so far everything looks great, so again click next to continue.

image

 

 

Next up is the modify columns option.  Here you will need to stop and think about if the data types are correct, will any of these columns need to act as a PK? Will you allow NULL values to be imported?  In my demo file the wizard is pretty spot on about the data I have and the type of data it is. The only thing I’m going to change is my Date column to be an actual date data type. Since this is a simple demo I’ll click on next to continue.

image

 

Validate your information on the summary window.

 

image

 

When the import is done you will see a window like this.

image

 

When the data has imported refresh your SSMS objects and expand tables under your database name.

image

So now lets see what our data looks like and there it is folks.

image

 

While I could have run through some more complex scenarios I really feel like this is a feature best suited for user discovery.

Add a Comment

Your email address will not be published. Required fields are marked *