Import Flat File in SSMS

Written by

Introduction

How can we import .csv and .txt files as tables in SQL Server? Yup, The Answer is the Title of this Post. We also going to see some of the Common errors to avoid in this process.

.csv file ( Comma Delimited )

.csv file

.txt file ( TAB Delimited )

.txt file

We can use Import Flat File Option in SSMS (SQL Server Management Studio) for loading the .csv and .txt files as Tables in SQL Server. If Object Explorer is not opened, then simply Click keyboard shortcut ‘F8’ or Go to View–>Object Explorer like below. Then Click Connect or Connection Icon and give credentials like Server Name, Login and Password etc.,


Object Explorer

STEP 1 – Import Flat File

In Object Explorer, Choose Particular Server Name –> Choose Particular Database Name –> Right Click and Choose Tasks –> Choose Import Flat File.


Import Flat File

STEP 2 – Specify Input File in Import Flat File Wizard

In Import Flat File Wizard, Skip the Introduction and Go to Specify Input File. Browse and Give the Location of the file to be imported.Give the New table name. By Default, Table Schema will be ‘dbo’. Click Next.


Specify Input File

STEP 3 -Preview Data

Go to Preview Data. Here, we can cross-check the structure of our given file. Click Next.


Preview Data

STEP 4 -Modify Columns

Go to Modify Columns. Here, we can change the Data Type,Enable/Disable the Primary Key and Allow Nulls options. Click Next.


Modify Columns

STEP 5 – Summary

Go to Summary. Here, we can cross-check Database Name, Table Name with Table Schema, Location of the File to be imported.Click Finish.


Summary

STEP 6 – Results

Go to Results. Here, we can cross-check the Status of the Operation. Click Close.


Results

Table Execution in SQL Server


Table Execution

Common Errors

1. Provided Table Name Already Exists

This error would come if we use Existing Table Name in New table name Text box.

Provided Table Name Already Exists

2. An error occurred while running learn on the input file. Cannot find patterns in the input file.

This error would come if we select .xlsx file format instead of either .csv or .txt file formats for importing.

Excel Format Import Error
An error occurred while running learn on the input file.

3. A Column named ‘Column_Name‘ already belongs to this DataTable.( System.Data)

This error would come if we use same column name twice in the given file.

A Column named ‘key’ already belongs to this DataTable.( System.Data)

Conclusion

Thus, we saw how we can import .csv and .txt files as tables in SQL Server through Import Flat File Option in SSMS and some of the common errors to avoid. This is applicable for both Azure SQL DB and On-Premises. Unit Tested as well.

Follow for interesting upcoming Posts!

Article Categories:
SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

Shares