SQL Server Management Studio Import Wizard Improvements

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 Import Data Wizard

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.

SQL Server Management Studio Import Data Wizard Data Flow

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.

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

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.

Preview Data in the Import Data Wizard in SQL Server Management Studio

To resolve this, we have to set the Text qualifier as ” i.e. double quotes.

set the Text qualifier as " i.e. double quotes in the Import Data Wizard

Once entered, the data now looks fine as shown below:

Data looks accurate in the Import Data Wizard

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.

Data type selection for the Import Data Wizard

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.

Import Flat File Wizard in SQL Server Management Studio

Import Flat File Wizard Introduction Screen

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

Specify Input File for the Import Flat File Wizard in SQL Server Management Studio

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.

Preview Data in the Import Flat File Wizard in SQL Server Management Studio

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.

Modify Columns in the Import Flat File Wizard in SQL Server Management Studio

The wizard gives us a complete list of the column types as a drop down.

Data Types in the Import Flat File Wizard in SQL Server Management Studio

In my example, I made the following changes:

Modelyear datatype as Int
Set Primary key on ModelYear
Allow NULL values on Mileage column

Final Column Changes in the Import Flat File Wizard in SQL Server Management Studio

The Next Screen shows the summary of the tasks.

Summary of Tasks for the Import Flat File Wizard in SQL Server Management Studio

Click on the Next button to start the Import. Once the import is complete it returns the below message.

Data insert is complete in the Import Flat File Wizard in SQL Server Management Studio

Let’s verify the newly created table from the File Import operation and review the data types as well.

Review the data from the Import Flat File Wizard in SQL Server Management Studio

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

Column changes from the Import Flat File Wizard in SQL Server Management Studio

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 “~”.

SQL Server Import and Export Wizard Previews Data with the ~ symbol as a separator

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:

Import Flat File Wizard Accurately Parses the Data

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.