SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Importing Flat Files in SSMS 17.x Works

One of the more annoying things I’ve found in SSIS/SSMS is trying to import CSV files for some quick analysis. For some reason I constantly seem to get files that the import wizard keeps having issues. On top of that, the defaults are annoying. When I need to get a CSV in, I select a Flat File source, but the filter for files always defaults to text files. Then there are more drivers and choices that make this complex.

Recently I was looking to load a file and noticed a new option: Import from Flat File. I decided to try it and was pleasantly surprised.

2018-08-27 09_52_03-

Updating a Database

I have a database of some baseball statistics, but wanted a few updates. I started using this wizard to quickly update about 10 tables. Here’s how this works. When you select the option, a wizard starts with this screen.

2018-08-27 09_54_42-Microsoft Edge

There’s no reason to look at this more than once, so check the “Do not show” box, as I did. Click Next.

Once you do that, you need to pick the source of your data. There’s a Browse button, and when you pick the file, you get a filter for Data files, which are .csv and .txt files. The file you pick will be used for the table name in the dbo schema, but those can be changed in the text boxes.

2018-08-27 09_55_39-Microsoft Edge

Note that this wizard only works with new tables, so if your plan is to update another table, you need to stage this data in its own space first.

The next step in the wizard is a preview of the data. This is handy, and for me I glance to get an idea of what the data is. This is useful and you can use the “Previous” button at the bottom to get back here from the next screen.

2018-08-27 09_56_46-Microsoft Edge

This next screen is the reason I love this wizard. I can get the data types and the PK for the table. This designer makes it easy to import the data. I can fix the table, which is nice. In a few of these files, I want to change from nvarchar to smallint for data, and allow nulls for some fields. Plus, being able to set the PK here is great.

2018-08-27 09_58_14-Microsoft Edge

The next step is a summary. I usually quickly click the Next button to import data.

2018-08-27 09_58_21-Microsoft Edge

If things work well, you get a green checkmark here. If not, you can go back and change settings, like adjust the PK or allow nulls. I even go back twice to preview data sometimes.

2018-08-27 09_58_28-Microsoft Edge

One note here. If you go back, the table exists, and you’ll need to drop it.

If you care, you can get the baseball statistics from here: http://www.seanlahman.com/baseball-archive/statistics/

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...