October 11, 2009 at 8:21 am
Hi
I am trying to create a bcp command to import data from a text file to an empty sql server table
I have created the table and the bcp command I have is:
bcp kroll..cdg9525_bri01208Training_Export in d:\_krollBat\AllData.txt -S meathead-pc\sqlexpress -U sa -P password -w -r -t¬ -k -q
I had "EOF" problem but solved that by adding in "-t¬" to my command
but now the command prompt displays the following text:
Starting copy...
0 rows copied.
Network pack size (bytes) 4096
Clock time (ms.) Total : 1
So I get no error but no data is copied.
The file names, directories and sql server instance is all correct, I have triple checked.
Can anyone see what I need to do?
Many thanks
October 11, 2009 at 11:56 am
don't see why you have specified the -q argument.
what are you trying to define for your row delimiter (-r)?
---------------------------------------------------------------------
October 11, 2009 at 4:37 pm
Try a native BCP with an error output log. If it fails it will write to the log and you will get an understanding why...
declare @STR varchar(500)
set @STR='bcp kroll..cdg9525_bri01208Training_Export in d:\_krollBat\AllData.txt -n -T -S meathead-pc\sqlexpress-e d:\_krollBat\err.txt'
print @STR
exec master..xp_cmdshell @STR
😉
-tjm
October 12, 2009 at 9:54 am
How about adding:
-e errorfile.txt
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 12, 2009 at 4:29 pm
Hi
thanks for all the replies
unfortunately xp_cmdshell is an advanced option and I cant use it. I think its because I am using sql server express
I added -e errorfile.txt and after I ran the whole command the file was empty 🙁
This is example data I am trying to insert:
¬756¬,¬RJ TestDocs¬,¬300000¬,¬4¬,¬K Ontrack¬,¬2009-09-25 09:25:53.910000000¬,¬EDIT - Changed ¬,¬WWSH Relevant Yes, xxx Group Organisation & Conduct, Privileged Yes ¬,¬NP¬,¬¬,¬Group document reviewed by Intg as part of the BC production exercise and found not to be privileged¬,¬¬
My table structure is as follows:
CREATE TABLE [dbo].[CDG9525_BRI01280Training_Export](
[iID] [int] NOT NULL,
[vchFolderName] [nvarchar](3000) NOT NULL,
[iControlNumber] [bigint] NOT NULL,
[iPageCount] [int] NOT NULL,
[DisplayName] [nvarchar](50) NOT NULL,
[dtAuditDate] [datetime] NOT NULL,
[AuditDescription] [nvarchar](253) NULL,
[Categories] [varchar](1024) NULL,
[PrivType] [varchar](max) NULL,
[PrivLogDocType] [varchar](max) NULL,
[LogDescription] [varchar](max) NULL,
[QAComments] [varchar](max) NULL
) ON [PRIMARY]
I notice that on the top of the data (text file) I have there is the following line:
¬iID¬,¬vchFolderName¬,¬iControlNumber¬,¬iPageCount¬,¬DisplayName¬,¬dtAuditDate¬,¬AuditDescription¬,¬Categories¬,¬PrivType¬,¬PrivLogDocType¬,¬LogDescription¬,¬QAComments¬
If I take it out I get the following error:
[Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file
As suggested I took out -q and there was no change
I'm not sure about -r, each new row starts on a new line in the file so I guess it's carriage return, is that \r?
Is there something I am missing?
thanks again
October 12, 2009 at 4:39 pm
Just got xp_cmdshell working !
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
October 12, 2009 at 6:59 pm
October 13, 2009 at 3:12 am
new line is the default for the row delimiter, so just remove the -r argument.
As you appear to have a header in the file, tell BCP to start loading from the second line using -F 2
I think you have a problem with the field terminator, as it starts and ends each column entry, plus you also have a comma delimiting the fields. Try setting -t to ¬,
Or get the format of the file changed!
---------------------------------------------------------------------
October 13, 2009 at 10:49 am
George is correct...that file is going to give you trouble. It appears to be comma field delimited with text delimiters of ¬
I would recommend using ¬ as a field terminator with no text delimiters (unless you have ¬'s that are included with the data).
jg
October 13, 2009 at 11:06 am
...or comma as the field delimiter but that means the ¬ will appear in the columns which will cause problems for the int data types. you need to strip out those ¬ characters.
---------------------------------------------------------------------
October 14, 2009 at 5:42 am
thanks for the help.
So should I try:
bcp kroll..cdg9525_bri01208Training_Export in d:\_krollBat\AllData.txt -S meathead-pc\sqlexpress -U sa -P password -w -t¬ -k -F 2
Am on a training course, will try when I get home later
Many thanks for the help
October 14, 2009 at 7:45 am
Can you get the format changed on that input file? That would be the easiest.
I don't believe bcp handles text delimiters, and from that example it appears to have ¬ as the text delimiters.
I don't think you can use commas as the field delimiters because the PrivType column has commas as part of the data. I also think ¬ will not work because there is one at the beginning and end of each row (the text delimiters around the first and last column)
You could user ¬,¬ as field delimiter, as long as you could remove or ignore the 1st and last ¬. I've not used format files very much, but they might be the answer for you. It appears you can use different field terminators between each column. You might be able define the 1st and last ¬ as columns to ignore.
jg
October 14, 2009 at 8:50 am
have to agree with jg, this is not a good file to use with bcp. format file may be the way to go, worth trying. Outside of a format file I have tried every combination I can think of with no luck (0 rows copied)
If this is aone off load you can specify the format file via prompts if you omit any of the standard format arguments (-c,-n,-w,-N)
Might be doable with SSIS as you can transform data on the way in.
---------------------------------------------------------------------
October 14, 2009 at 1:17 pm
I'm gonna see if the data dump file can be different, it comes from an external company.
I am creating scripts for a project that will run weekly for several months. The BCP is only one part of that but is key.
Thanks for your help everyone
October 16, 2009 at 8:52 am
One way to find the issues with the data is try inserting a record in the table. BCP out ( probably using '~' as delimitor ) and compare the output with the input file you are trying to load 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply