By: Rajendra Gupta | Updated: 2017-11-15 | Comments (2) | Related: > SQL Server Management Studio
Problem
SQL Server Management Studio (SSMS) is one of the most important tools for SQL Server DBAs and Developers to complete their work on a daily basis. SSMS has improved a lot over the years along with SQL Server versions. Microsoft recently released SSMS 2017.3 with new and exciting features. We have explored some of the features in my previous tip and in this tip we will demonstrate how the Import Flat File Wizard works and see how useful it is over the Import Data Wizard that already exists in SQL Server.
Solution
SQL Server Management Studio is used to access, configure, manage, administer, and develop all components of SQL Server, Azure SQL Database and SQL Data Warehouse. SSMS provides a single comprehensive utility that combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server for Developers and Database Administrators of all skill levels.
In October 2017, the Microsoft SQL Server Team announced the 17.3 release of SQL Server Management Studio. This SSMS version provides lots of new features. One thing which I was interested to check out is the new Import Flat File Wizard which promises to simplify the process of importing flat files to SQL Server. To get started with SSMS 17.3 you can download it from this link.
SQL Server Management Studio 2017 New Flat File Import Option
The Import Flat File Wizard streamlines the import process of flat files (.csv, .txt, etc.) with an intelligent framework, requiring minimal user intervention or specialized knowledge.
The SQL Server Management Studio Flat File Import Wizard uses the Microsoft Program Synthesis using Examples (PROSE) SDK. This SDK allows us to take raw semi-structured data and identify patterns in it to perform predictive file splitting, helping the wizard “learn” the structure of a flat file to infer column names, types, delimiters, and more.
This simplifies the import flat file process by requiring the user to only provide an input file and unique file name, and the wizard takes care of the rest for you.
The image below shows the data flow for the Import Flat File Wizard.
In this tip, we will compare the process to import of the data into table with both the Import Data Wizard (original product) and Import Flat File Wizard (new product).
Example 1 - SQL Server Import Data Wizard
To launch the SQL Server Import Data Wizard in Management Studio, right click on the database name followed by Tasks and then click on the Import Data... option as shown below.
Select the Source as Flat File Source and select the source file location as shown below:
We can see here that the there are many configuration needs to be done such as Format, Text qualifier, header row delimiter, etc. The problem with this is that we need to know the correct options else data will not be correctly uploaded to the table. For example, if you go to the column section, we can see that rows data are still being shown with quotes ("), which we don't want in our table data.
To resolve this, we have to set the Text qualifier as " i.e. double quotes.
Once entered, the data now looks fine as shown below:
If we go to the Advanced section, the review the DataType option we can change the data type however the data types are not SQL Server data types which sometimes causes issues in the data import.
Example 2 - SQL Server Import Flat File Wizard
Now let's import the same file using the Import Flat File Wizard in SQL Server Management Studio.
The high level steps which the wizard performs to import the file contents into a new table in your database are:
- Specify Input File
- Preview Data
- Modify Schema
- Summary
- Results
To launch this wizard in SQL Server Management Studio, right click on the database name followed by Tasks and then select the Import Flat File Wizard option.
This opens the Import Flat File Wizard which provides a brief introduction of the Wizard.
Now click on the next button and we can see two options in the next screen:
- Location of the file to be imported
- Specify the table name
Once we specify the location of the Flat File, it automatically takes the table name as the Flat File name. On the next screen, Preview Data, you can see your data. We can see the data is automatically corrected by removing the quotes ("), so we don't have to make the configuration change to import it.
Click on Next and the Modify Columns interface opens up. This wizard is intelligent enough to identify patterns and perform predictive file splitting. After scanning through the data it also does a nice job in predicting the data type of the column values.
On this screen we can set the column properties like Name, Data Type, set Primary Key on the table, set option to Allow Nulls values in columns or not.
The wizard gives us a complete list of the column types as a drop down.
In my example, I made the following changes:
- Modelyear datatype as Int
- Set Primary key on ModelYear
- Allow NULL values on Mileage column
The Next Screen shows the summary of the tasks.
Click on the Next button to start the Import. Once the import is complete it returns the below message.
Let's verify the newly created table from the File Import operation and review the data types as well.
The table properties also show the changes we have made during the flat file import:
- Modelyear datatype as Int
- Set Primary key on ModelYear
- Allow NULL values on Mileage column
Example 3 - Import Data Wizard in SQL Server Management Studio
We have seen how the flat file import works using the recently introduced Flat File Wizard. In this example, we will see an import of a more complex .txt file and see the differences with the Import Data Wizard (original product) and Flat File Wizard (new product).
Below we have selected our sample file.
Now if we preview the columns, we can see it only detected 2 columns while our text file has many columns which are separated by "~".
Example 4 - Flat File Wizard in SQL Server Management Studio
If we try to import the same file using the new Flat file wizard, here is the difference:
We can see th Flat file Wizard is intelligent enough to detect the .txt or .csv format and import the data easily without any additional complexities. I have included the sample file used in the tips in the Next Steps section.
Common Errors in Flat File Wizard
Here are some common errors you might run into.
Table name should be unique, if table already exists, it will return this error:
Permissions to create a table need to be assigned to the user:
Next Steps
- SQL Server 2017 officially launched on October 2, 2017. Check out the SQL Server 2017 Release notes.
- Explore SQL Server 2017 What's new in SQL Server 2017
- Read more SQL Server 2017 tips
- Read more about Microsoft Program Synthesis
- Download the two import files MSSQLTIPS_Demo.txt and products.txt to conduct your own testing.
About the author
Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-11-15
