Importing data with bcp - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Administration

Importing data with bcp

Author

Topic

Rach2009
Starting Member

37 Posts

Posted - 2009-09-30 : 08:28:58

Hi,

I am new to using bcp imports and keep getting an error message that i don't understand. When I execute the following;-

bcp DispatcherArchive..ORDERS in Orders.csv -T

I keep getting the error message - Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

The database is DeispatcherArchive, the table to import into is ORDERS and the flat file is Orders.csv. I have tried using DispatcherArchive.dbo.ORDERS but get the same error message.

Any suggestions why this could be happening?

Many Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-30 : 08:32:14

You need to execute this via command prompt

Madhivanan

Failing to plan is Planning to fail

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2009-09-30 : 08:43:21

if you want to do this in sql, see the syntax for BULK INSERT in books online.

elsasoft.org

Rach2009
Starting Member

37 Posts

Posted - 2009-09-30 : 08:56:04

Hi, I am planning on using the bulk insert but thought I still need to create a format file via bcp as I am using a char datafiletype. Is this the right way of doing this?

Thanks

Rach2009
Starting Member

37 Posts

Posted - 2009-09-30 : 09:23:10

Also, how do I access Command prompt?

Thanks again

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-30 : 17:46:36

To access the command prompt:
Start --> Run --> cmd <ENTER>

Then run the bcp command like:
BCP DispatcherArchive.dbo.ORDERS IN "C:\Temp\Orders.csv" -S <ServerName> -T -c -t","

If you want to use BULK INSERT with a standard CSV file, you may not need a format file. Try this in the Query Analyser:
BULK INSERT DispatcherArchive.dbo.ORDERS
FROM 'S:\Data\Orders.csv'
WITH
(
FIELDTERMINATOR =',',
ROWTERMINATOR ='\n'
)

Ensure that the file is accessible by the SQL Server service account!

Rach2009
Starting Member

37 Posts

Posted - 2009-10-01 : 04:50:08

Thanks YellowBug, I'll try that!

Rach2009
Starting Member

37 Posts

Posted - 2009-10-01 : 05:03:35

Hi I just tried executing -->Start --> Run --> cmd <ENTER> and although the Query Executed Successfully, nothing else happened. Where do I need to go from here? Should a box pop up for me to put the bcp statement in?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 05:10:46

yup... a black window should pop up. you can also click start-> all programs->accessories-> command prompt to launch this

Rach2009
Starting Member

37 Posts

Posted - 2009-10-06 : 09:36:43

Hi, I am now getting error messages - I was hoping someone might be able to explain!

Firstly, in the command prompt, when I enter

bcp Dispatcher_Archive.dbo.ORDERS In "C:\Temp\Orders.csv" -S <ServerName> -T -c -t

I get the error message
"The system cannot find the file specified"

And, similarly, when I enter the following command in SQL

BULK INSERT Dispatcher_Archive.dbo.ORDERS
FROM 'C:\Temp\Orders.csv'
WITH (FIELDTERMINATOR=',',ROWTERMINATOR='\n')

I get the error message
"Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "C:\Temp\Orders.csv" does not exist."

The file in question definetely DOES exist. Is there any reasons why this file cannot be seen or accessed? Is C:\ the right place to be saving a file of this kind? Are single or double quotes correct?

Many Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-06 : 09:42:30

The file should be in Server's directory

Madhivanan

Failing to plan is Planning to fail

Rach2009
Starting Member

37 Posts

Posted - 2009-10-06 : 10:10:51

Thanks, this has solved the problem for the BULK INSERT command in SQL, but I am still getting the same eror message for the bcp part. Any ideas why this is continuing to happen?

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-06 : 10:17:16

what version of sql server?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Rach2009
Starting Member

37 Posts

Posted - 2009-10-06 : 10:21:34

SQL Server 2005

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-07 : 05:15:31

bcp Dispatcher_Archive.dbo.ORDERS In "C:\Temp\Orders.csv" -S <ServerName> -T -c -t
With BCP the file should be in the C:\ drive of the local machine (so your machine if running from your PC)

Or if the file is on a share, try "\\Server\ShareNmae\filename.csv"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources