Import Flat File wizard is a new feature implemented in SQL Server Management Studio (SSMS) v17.3 for importing a .csv or a .txt file into a SQL Server database.
The latest SSMS version can be found on this link
To invoke the Import Flat File wizard, go to Object Explorer, select a database you want to use to import data from a .csv or .txt file, right click and from the context menu, choose the Import Flat File option:
This will open the Import Flat File Introduction page providing an overview of the features and showing what needs to be specified in order to successfully import data from a flat file to a SQL Server database.
If you don’t wish to see this page next time the Import Flat File feature is invoked, just check the Do not show this page again. After that press the Next button to proceed with importing a flat file:
The next page of the Import Flat File wizard is the Specify Input File page. On this page, we need to set up the location of a flat file which will be used for importing into a SQL Server database. Also, we need to add the name of the table which will be created into a SQL Server database to hold the imported data from a flat file:
To pick a location of a flat file, click the Browse button. By default, the Import Flat File wizard is set to search for the .csv and .txt files. The Import Flat File wizard allows to pick another file format then .csv and .txt. For example, a .xlsx file format can be selected:
But on the next page of the Import Flat File wizard, the following error message will appear when another file format then .csv or .txt is used:
“An error occurred while running learn on the input file.
Additional information:
Cannot find patterns in the input file. (Microsoft.SqlServer.Prose.Import)”
Let’s go back to the Specify Input File page and, this time, choose the correct file format (e.g. ImportData.csv):
By default, the name of the table in the New table name box will be the name of the imported .csv/.txt file (ImportData).
The name of the table in which will be imported data can be changed and set whatever is liked as long as the SQL Server table and column naming conventions are followed.
The name of a new table should be unique and must not exist in the destination database, otherwise the following message will appear when the Next button is pressed:
“Provided table name already exists.”
After the unique name for the table is set, press the Next button to continue. The next page of the Import Flat File wizard is Preview Data:
This page generates a preview of data that are located in the ImportData.csv file and how they will look like when are generated into a table. The Preview Data page generates max up to first 50 rows.
If you are satisfied with the preview, click the Next button to proceed:
On the Modify Columns page wizard analyze the data located in the .csv or .txt file and suggest what it believes that is correct for the column name and data type. The names of the columns, data types, primary key for the table, etc. can be modified/changed:
Once the settings on the Modify Column page are done, click the Next button. The Summary page will appear:
This page shows the names of SQL Server, database and the name of the table where will data from the imported .csv file be stored.
Click the Finish button to import data formImpotData.csv file to the ImportData SQL Server table.
The last page of the Import Flat File wizard is the Results page. This page indicates whether the importing data from the flat file to a SQL Server table was successful or with errors:
Now, when check the AdwnetureWorks2014 database, under the Tables folder, the ImportData table will appear:
When importing data from the flat file to SQL Server database, make sure that you have adequate permissions, otherwise one of the following error message will appear:
“Error creating table. (Microsoft.SqlServer.Import.Wizard)
Error creating table. (Microsoft.SqlServer.Prose.Import)
CREATE TABLE permission denied in database ‘AdventureWorks2014’. (Microsoft SQL Server, Error: 262)”
“An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The server principal “Zivko” is not able to access the database “AdventureWorks2014” under the current security context. (Microsoft SQL Server, Error: 916)”
“Error creating table. (Microsoft.SqlServer.Import.Wizard)
Error creating table. (Microsoft.SqlServer.Prose.Import)
The specified schema name “dbo” either does not exist or you do not have permission to use it. (Microsoft SQL Server, Error: 2760)”
More about necessary permissions in order to use the Import Flat File wizard successfully can be found on this page
He is a prolific author of authoritative content related to SQL Server including a number of “platinum” articles (top 1% in terms of popularity and engagement). His writing covers a range of topics on MySQL and SQL Server including remote/linked servers, import/export, LocalDB, SSMS, and more.
In his part-time, Zivko likes basketball, foosball (table-soccer), and rock music.
See more about Marko at LinkedIn
View all posts by Marko Zivkovic
- How to connect to a remote MySQL server using SSL on Ubuntu - April 28, 2020
- How to install MySQL on Ubuntu - March 10, 2020
- Using SSH keys to connect to a remote MySQL Server - November 28, 2019