To import CSV file into database table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

To import CSV file into database table

Author

Topic

chandru@zsl
Starting Member

8 Posts

Posted - 2008-06-03 : 12:13:31

Hai all.,
Im trying to import the CSV file values using bulk insert but im getting an error in my code .so can anyone help me on this.
the following is the coding i have created
--create table
CREATE TABLE CSVTest
( ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME
)
--import from CSV using bulk insert

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

im gettin an error like
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "c:\csvtest.txt" does not exist

The thing is i have created a CSV file in C drive with some values.

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 12:23:31

Are you having a valid file in c:\csvtest.txt of your server?

chandru@zsl
Starting Member

8 Posts

Posted - 2008-06-03 : 12:28:53

quote:
Originally posted by visakh16

Are you having a valid file in c:\csvtest.txt of your server?

Ofcourse,im having the valid file.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 13:35:04

quote:
Originally posted by chandru@zsl

quote:
Originally posted by visakh16

Are you having a valid file in c:\csvtest.txt of your server?

Ofcourse,im having the valid file.

Its should be inside your server itself. Or if its remote file you have to specify UNC name.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-03 : 13:44:59

To add more information to visakh16's post...

BULK INSERT runs from the database server's perspective and not your client machine. So the path and file must exist on the database server. It will not look on your client machine unless you use a UNC path.

To see if the file exists on the database server, run this (assuming you have sysadmin):

EXEC master..xp_cmdshell 'type c:\csvtest.txt'

Do you get an error or the file contents?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

chandru@zsl
Starting Member

8 Posts

Posted - 2008-06-04 : 10:42:18

quote:
Originally posted by visakh16

quote:
Originally posted by chandru@zsl

quote:
Originally posted by visakh16

Are you having a valid file in c:\csvtest.txt of your server?

Ofcourse,im having the valid file.

Its should be inside your server itself. Or if its remote file you have to specify UNC name.

yes vikash you are correct im using a remote file thats the problem.
now how to specify the UNC name..

Thanks..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-04 : 12:43:50

UNC:

\\RemoteMachine\ShareName\...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources