How To Import Flat File Data Using Import Export In SQL Server

Posted November 15, 2020 by Vishwanath Dalvi in Database, SQL Server

Import Flat File Data SQL Server_Header

Importing and exporting data from flat files is a common task among developers & DBA. Different methods are available to import-export flat file data in SQL Server. Well-known methods include – BCP, SSIS Packages, OPENROWSET, BULK INSERT queries, and tools. However, the simplest one is doing it within SSMS (SQL Server Management Studio). Using Import Flat File data wizard in SQL Server.

Import Flat File feature is included with SQL Server Management Studio (v 17.3) and later.

This Import File File data wizard simplifies the process compared to former ways. Moreover, with simple UI and fewer configurations plus limited domain knowledge, any user can do this. This wizard is smart and detects the delimiter, column data types using an intelligent framework. With minimal configurations and inputs from the user, – it can import data from text and flat file (CSV) into a new table.

To demonstrate with an example, we are using publicly available sample CSV file data with 13933 rows. We will import Annual enterprise 2019 survey details into dbo.Annual2019SurveyDetails table.

Sample CSV File Data

Import Flat File Data SQL Server_CSV_Data

Import Flat File Data Using Import Export In SQL Server


1.Within SQL Server Management Studio (SSMS), right-click on your target database where flat-file data will be imported.

Import Flat File Data SQL Server_1

2.Click on Tasks > Import Flat File.

Import Flat File Data SQL Server_2

3.Furthermore, Specify Input File window, browse the CSV file location, and specify the target schema & table name.

Import Flat File Data SQL Server_3

4.Preview Data window will show the first 50 sample rows.

Import Flat File Data SQL Server_4

5.Modify Column page, here we can make changes to column names which are generated based on file structure, data types, and table constraints, keys.

Import Flat File Data SQL Server_5

6.Summary and Result window, you will see the details about the imported file, table, and success & failure results.

Import Flat File Data SQL Server_6

Import Flat File Data SQL Server_7

Summary

In a nutshell, we have learned a basic and simple way to Import Flat file (CSV) data into SQL Server table. This approach is faster and easy without getting into much configurations. This can work with other delimited files too. Soon Micorosft will be adding more options to this feature. If you like this post you may reach through Tech-Recipes database archive to learn more stuff.

Read more and browse through more posts related to SQL Server on Tech-Recipes.

1. hHow To Use BCP Utility In SQL Server
2. How To Import CSV File Using Bulk Insert In SQL Server
3. SSIS- How To Export & Import ISPAC File SSISDB – Visual Studio
4. How To Create Database Diagram In SQL Server SSMS
5. How To Index Computed Column In SQL Server
6. How To Use Computed Column In SQL Server
7. Execute SQL Files Using SQLCMD

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

Leave a Reply