Importing data from CSV File to SQL

Importing data from Notepad File to SQL Table

Below are the Steps of importing CSV (comma Separated file) i.e Text file into SQL Table

First we will create a notepad file with employee Data below

ID,Name,Salary
1,Amit,5000
2,Sumit,6000
3,Raj,8000
4,vijay,9000
5,suresh,10000
6,,5000
7,,5000
8,,5000
9,vinod,130

Save it as Notepad file in any Drive with name Employee.txt .

Now creating a Employee table to insert CSV file values with same no. of columns as in notepad file

CREATE TABLE [dbo].[Employee_import](
[Empid] int,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]

GO

So below is the Query to insert Data from CSV to SQL Tables

 BULK INSERT [Employee_import]
    FROM 'E:\Employee.txt'
    WITH 
    ( 
        FIRSTROW = 2, 
        FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n' 
    )

select * from Employee_import  --to verify result

I have placed my notepad file in E : drive so, change to the place where you have stored your notepad file.

Firstrow=2 means the first row in the notepad file is having column names so we dont want that   inserted insert will start from row 2.

FieldTeminator=, as field are separated by comma
RowsTerminator=\n as Rows are separated with a new lines.

Note: You can also use SQL Import and Export wizard and SSIS to import or Export Data from CSV file to SQL.