Anyone that’s worked with data has had the need for a one-time import of a data file, in order to clean and reshape the data. SSMS has included an Import Data wizard for quite a while, but added an Import Flat File wizard in 2017. This is meant to quickly import data from text or CSV file. I tried using the wizard back when it was released, but had a number of issues with it, so I stuck with the old Import Data process.
Recently I went back and gave Import Flat File another shot (I’m currently using SSMS v18.8), and it seems to work a lot better now. This process will create a new table for the imported data, so you won’t be able to load data into an existing table. This process also seems meant for TXT or CSV files, so no Excel spreadsheets or other sources. This wizard will pick up if the first line in a file contains the attribute names. When I removed the attribute names from my test file, it seemed to recognize that, and just used column1, column2, etc.
To start the wizard, right-click on the target database, and select Tasks => Import Flat File. You’ll select the file to import, and you can supply the new table’s schema and name.
Next, you’ll get a preview of the data to import (up to 50 rows). There is a checkbox here for ‘Use Rich Data Type Detection'(More on this option later).
According to the help page, the process will use the first 200 rows to determine the data type for each column. The next ‘Modify Columns’ screen will display these data types, and give the user a chance to change them, as well as the ability to define a primary key and column nullability. Empty length strings will be imported as NULL.
You’ll also want to change the default of ‘No Range’ on the Error Reporting option. I select a range of 1 here, so I can get a better idea of which row has caused any error (With 1, you end up getting a row range of 2 rows, although if the range given is 0-1, you know it’s the first data row that’s the issue). It seems like these values give you a trade-off between better performance and a more accurate row count, although I didn’t find anything in Help about this feature. ‘No Range’ will give an error message, but no row location or range.
The first file I used included NULL in the values, so I ended up with a string “NULL” for these values, which makes sense. So I needed to convert these to empty length strings in my source data file.
For a test, I also tried processing a fixed-width data file. The file had no column names. The wizard makes its best guess as to where the columns begin and end in the fixed width data. If it makes the wrong assumption (as it did for me), you don’t get a change to correct that. Luckily, I don’t often work with fixed-width files.
Back to the ‘Use Rich Data Type Detection’ on the preview page. I didn’t find anything in help about this option, but I tried a few variations with it. My test data file had an ID column, which was a unique number for each record. I added a new record, but used a letter in the ID column. When I checked the ‘Rich Data Type’ option and imported, that new record was imported, but with a NULL in the ID column. The wizard determined ID should be an Int column, since all but one of the values was an integer. When I didn’t check the column, the wizard used nvarchar(50) as the datatype (This appears to be the default data type it chooses for strings). I doubt this is an option I would use, I would rather have all values imported, even if the column has to be a string, I wouldn’t want to lose data without a warning.
So this Wizard gives you a lot less options than the Import File wizard, but it seems to be a quicker way to import a comma delimited data file.