An option added to database tasks (right-click on a database and select Tasks)in SSMS 17 was ‘Import Flat File’. There’s always been the option to import data into a database from a file as part of the Import/Export wizard, but this is a more streamlined tool to import data. The utility accepts text(TXT) and CSV files, and imports data into a new table that is created by the import.
After the splash screen, the first option (Specify Import File) is to select a file and to specify the name of the table to be created. I was only given the option of dbo for the table schema, even though other schemas exist in the target database.
The next screen (Preview Data) will display the data to be imported (up to 50 records).
The next screen (Modify Columns) will allow the user to change some options for the table to be created, like specifying a primary key, as well as column names, nullability and data type. The wizard seems to do a good job of selecting data types for columns, identifying numbers and dates. String values default to nvarchar(50), but that can be changed to a more appropriate type.
There is an option at the bottom of the screen for ‘Error Reporting’, but I couldn’t find any documentation on this feature. I had set one of the columns to a length of 2, so each row would raise an error on insert. Setting the option to 100 told me that there was an error in rows 0-100, even though I only had 4 data rows in my file. This option includes the note ‘Selecting a smaller range may have a performance impact’. I’m guessing you want to set a higher number for performance reasons, but a lower number to give you a better idea which row caused a error. I just used the default option of ‘No Range’, which didn’t return any information on what rows failed, but I did get a message on which column the insert failed.
The last screens are a ‘Summary’ and then the ‘Results’.
This feature has definitely been improved in SSMS 18. I had used the feature in SSMS 17, but ran into a few issues. When I tried the feature out, I used a test file, comma delimited with quotes around the strings. So when the file had a column name of “ID”, the wizard set the column name as _ID_. Also, all of the data was defaulted to a string data type, no matter what kind of data was included. Also, a space after a comma in a data line threw the import off, so a line with , “Name” would appear as “Name in the data preview. Even without a quote, the space would be a part of the imported data.
SSMS 18 does a better job in these instances, quoted data gets a more appropriate default data type and column names are handled better. The data appears to be trimmed when imported, as well. Now that SSMS 18 is in GA release, I’ll be using this feature when importing data from text and CSV files.
SQL Rob 