This is a very common request recently – How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.
CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.
Create TestTable
USE TestData
GO
CREATEÂ TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO
Create CSV file in drive C: with name sweetest. text with the following content. The location of the file is C:\csvtest.txt
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202
Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.
BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
DROP TABLEÂ CSVTest
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
839 Comments. Leave new
What is the Max size that SQL server can read and insert into database table? Please help me for this because i am inserting up to 4MB file into a table using .NET code, it is not inserting records in the table. Up to 3 MB files are inserting into the table.
Thanks
Hi,
i have to upload data from a fixed width text file to sql server 2000.
data in file is like rahulankilprogrammer
we have two create a table of three column fname,lname and job, first 5 letters will go in first column , next 5 letters will go in second and than next 10 letters will go in third column .
please help me to do this ,as it is not a CSV file.
this file come to us on daily basis, daily we hav to upload it on sql server.
pls pls help me out ASAP
ThanX in advance
Hello,
I have a proplem with BULK in stored procedures.
I want to do a BULK from a file (.CSV) wich name changes every day. So I have an attribute “@FileName” in order to change the name every single day.
BULK INSERT dbo.TableName
FROM @FileName
WITH ( FIELDTERMINATOR=’;’, ROWTERMINATOR=’\\n’)
But it gives me an error telling that there’s a sintax error after FROM. I have tried with all kind of punctuation (‘,”,`,´,+)
It would be gratefull if some one could answer to my question.
Thanks in advance
declare @sql varchar(1000)
set @sq=’BULK INSERT dbo.TableName
FROM ”’+@FileName+”’
WITH ( FIELDTERMINATOR=’’;”, ROWTERMINATOR=’’\n’’)’
EXEC(@sql)
Hi,
I try that code. But my saved location is D drive.
But i shows one error. That is Incorrect syntax near ‘ ‘ ‘.
Give the solution.
Thanks,
Vijay Ananth
Post the full code you used
hi
it solved my problem
can u please tell how can i schedule stored procedure in sql server2005
thanks
rajeh
Read about JOBs in SQL Server help file
Hello,
I’m using bulk insert and everything works fine but I cannot get latin characters to import properly. For example, my flat data file contains Sertãozinho but the imported data displays Sert+úozinho. The “a” character changes to something with plus sign. I tried using nvarchar datatype but it did not help.
Here is my code:
DROP TABLE [DumpData]
GO
CREATE TABLE [dbo].[DumpData](
[DataField1] [varchar](255) NULL
ON [PRIMARY]
–import data using bulk insert
DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = ‘BULK INSERT [NalcoSAPDump]
FROM ”c:\DataImport\import.dat”
WITH (FIELDTERMINATOR = ”;”,ROWTERMINATOR = ”’+CHAR(10)+”’)’
EXEC(@bulk_cmd)
Thanks!!
You should have used nvarchar datatype instead of varchar
USE @bulk_cmd use nvarchar
The table you are creating use the appropriate Collation for that column(s)
Hello,
thanks for your answer, Vijay Ananth. :D
Does anybody know something about incremental bulk? It’s just the “normal” bulk insert or there is another thing that I have to add to the bulk sentence?
I want to do a BULK from a csv file. Firstly I do a Bulk of the whole file.
But that csv file changes every time, and in order to have those new rows in my DB I would like to know if there is another way to bulk just the new rows. With that incremental bulk or something else.
At the moment I’m doing it with bulk every second. So I don’t know if somebody that is writting the file can get an error while the bulk is working.
Any suggestions will be wellcome.
Thanks
insert Excel file record in a table + SQL SERVER + C#.net
Thanks, helpful post.
Pinal one more time you saved my life, althought it is the first time that I submit you a commend! Mate you are the best! I hope to reach your level one day!
Panagiotis Lepeniotis
MCDBA,
MSc Database Professional Student!
As for the dataMate who asked about the quotations, I will suggest to edit the csv file, at least thats what I did and worked!
Cheers
Hi Pinal,
i want to batch upload from excel to sqlserver where i want to insert into multiple table as dependency is there between tables. How to do it? Is it possible to do in c#?
Thank you in advance. I would be very thankful if I get answer quickly as I’m badly in need of it?
Dave B
Regarding your question:
Does BULK INSERTing into an existing table that has an index on it take longer than inserting into an existing table without an index?
The answer is no. We tried it out on a large Table (10 mil rows) into which we are loading even more rows. WITHOUT the index, the application timed out. With the index, it loaded in fine time.
thanks a lot!
exactly what i was looking for.
BULK INSERT CSVTest
FROM ‘c:\CSVTest.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
I got the following error
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘‘’.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Can you please let me know what needs to be done in this case?
Can we do conversions while doing Bulk inserts? (like string to decimal conversions, string to date, string to time etc..,)
Any suggestions is highly appreciated! :)
Import data as such
If you want ot convert them to different data types, use update statement
I also get the error: Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file “c:csvtest.txt” does not exist.
How do I grant SQL access to the file?
I am running on SQL2005 on an instant I created for myself.
I tried to change the file to have access approved for ‘everyone’, but still this didn’t make any difference. I still get the file doesn’t exist error.
Thank you,
Anke
How were you able to resolve this?
I was able to resolve.
Thank you!
I have the same problem as Kumar. Runs without errors but only every second (alternative) record gets inserted. I have tried different rowterminators, but the problem remains.
bulk insert Gen_ExcelImports from ‘C:\Software\invoices\April2008\csvfile.txt’
with ( FIELDTERMINATOR = ‘,’,FIRSTROW=2,ROWTERMINATOR = ‘\n’)
Any help would be greatly apprciated. Thanks in advance!
I have imported a flat (csv) into SQL Server 2005 using the wizard. I have field called Product Code which has 3 numbers e.g. 003. I imported it as text (default). When I open the ipmorted table, i have a plus sign next to the Product Codes e.g. +003. Why is it there and how do i get rid of that plus sign? I just want it to show 003.
It is particularly annoying and I want to concatenate the Product Codes with other codes to create an ID.
Please Help
You should have formatted the cell to be of character type
or use convert function to convert number to varchar
Hola your example i good
How conncted remote computer for read file in this remote computer.
for example
BULK
INSERT dbo.tblPsoArchivosCalificacion
FROM ‘\\10.63.200.28\Paso\LibroDatos.csv’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
the question is?
i need net use userid and password to access remote computer.
sorry but my english is bad.