Basic Logics · Data Management · Real Scenario Logic's

Import CSV and TXT Files as Tables in SQL Server

Introduction

In this blog post, we are going to look how can we import .csv and .txt files as tables in SQL Server and some of the possible errors to avoid in this process.

Import .csv and .txt files as Tables in SQL Server

.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, 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

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

Preview Data

STEP 4 -Modify Columns

In Import Flat File Wizard, 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

In Import Flat File Wizard, 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

In Import Flat File Wizard, 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.

Thanks for reading and Follow Blog and Show your Support for many more interesting upcoming Posts!

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s