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.
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.
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.
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.
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.
The next step is a summary. I usually quickly click the Next button to import data.
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.
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/
