June 5, 2012 at 9:27 am
Hello All,
I am using the following link, to import the data from csv files to database.
So, from the link, I have modified my query and tried to run on my machine,
but apparently I received the following error:
bcp Testdb2.dbo.Activity in C:\Documents and Settings\..\..\\Activity.xls -c -t, -S Servername -T
Error: Incorrect syntax near '.'.
Tried using different way of quotes, but it didn't worked out. Can someone help to resolve this issue. Thank you.
June 5, 2012 at 9:33 am
How about:
bcp Testdb2.dbo.Activity in "C:\Documents and Settings\..\..\\Activity.xls" -c -t, -S Servername -T
June 5, 2012 at 10:23 am
Still the issue exists
June 5, 2012 at 10:32 am
You do realize that you have to execute this from command prompt or using xp_cmdshell? This is not an SQL command and cannot be executed as such.
To execute from SQL you must do the following:
DECLARE @cmd varchar(500)
SELECT @cmd = 'bcp Testdb2.dbo.Activity IN "C:\Documents and Settings\..\..\\Activity.xls" -c -t, -S Servername -T'
EXEC master..xp_cmdshell @cmd;
Jared
CE - Microsoft
June 5, 2012 at 11:29 am
Thanks much...What if we don't have permissions to run xp_cmdshell. Do we have any other options to import the data..
June 5, 2012 at 11:32 am
Look up BULK INSERT
Jared
CE - Microsoft
June 5, 2012 at 11:45 am
Ya I just did that...but does the bcp won't take the files from network location..
June 5, 2012 at 11:48 am
DBA_SQL (6/5/2012)
Ya I just did that...but does the bcp won't take the files from network location..
I'm sorry, I don't understand the question.
Jared
CE - Microsoft
June 5, 2012 at 12:13 pm
My file is in network location...I am trying to pull the file...onto server..but receiving error: file could not be opened....is bulk insert takes only local drive files.
June 5, 2012 at 12:45 pm
From BOL:
BULK INSERT can import data from a disk (including network, floppy disk, hard disk, and so on).
http://msdn.microsoft.com/en-us/library/ms188365.aspx
Jared
CE - Microsoft
June 5, 2012 at 12:46 pm
It's more likely to be a permissions issue. The service account that SQL Server is running under has to have permissions to read the file. Find out from the DBA if that service account can access the file's location.
June 6, 2012 at 11:32 am
I am receiving the following error..can some one please rectify..Thank you.
NULL
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unexpected EOF encountered in BCP data-file
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
NULL
June 6, 2012 at 11:35 am
You're going to need to paste the code you are using and a sample file. Something is probably wrong with the file. Is it uniformly formatted?
Jared
CE - Microsoft
June 6, 2012 at 11:41 am
First thing you might look for is if the delimiter you've picked is showing up in the data itself and not just between columns. That's the most common cause I've seen for this error.
As Jared said, showing us the command you used and a sample file that recreates the problem will get a quicker response.
June 6, 2012 at 11:58 am
My CSV file is of "|" limiter. I am trying using the following BCP command but receives error.
SET IDENTITY_INSERT table1 On
DECLARE @cmd nvarchar(500)
SELECT @cmd = 'bcp slcdb.dbo.table1 IN "D:\table.csv" -c -t, -S @@servername -T'
EXEC master..xp_cmdshell @cmd;
SET IDENTITY_INSERT SLCAgenciestbl OFF
Error:
NULL
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unexpected EOF encountered in BCP data-file
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
NULL
So, I tried using openrowset command
SELECT * FROM
OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\..\..\Desktop;',
'SELECT * from table.csv');
Here I can able to see the data, but I am not receiving the data in right format. It is showing just 2 columns instead of 10 columns. I do not know how to insert format option in this query. Can some one help me in getting the output.
No Idea, for small issues, making complex..
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply