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 )
.txt file ( TAB Delimited )
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.,
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.
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.
STEP 3 -Preview Data
Go to Preview Data. Here, we can cross-check the structure of our given file. Click Next.
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.
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.
STEP 6 – Results
Go to Results. Here, we can cross-check the Status of the Operation. Click Close.
Table Execution in SQL Server
1. Provided Table Name Already Exists
This error would come if we use Existing Table Name in New table name Text box.
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.
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.
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.