BCP Input - 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 Programming

BCP Input

Author

Topic

DeanaMiller
Starting Member

12 Posts

Posted - 2006-01-04 : 20:09:28

Using MSDE and OSQL
I begin with:

C:\OSQL -D VID -i C:\accepted.sql -o C:\Results\accepted.txt -n -w500 -Usa

That gives me data such as this:

363 Cynthia KY 36
542 Charlene NC 3
594 Amanda NJ 9
592 Robert NJ 54

Then this command to create a table

CREATE TABLE accepted
(
Customer_id nvarchar(50) NULL,
Cust_Name nvarchar(50) NULL,
Cust_State nvarchar(50) NULL,
Cust_Count nvarchar(50) NULL
)
GO

I've created this BCP format file:

8.0
4
1 SQLCHAR 0 50 "/t" 1 Customer_id SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "/t" 2 Cust_Name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 "/t" 3 Cust_State SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 50 "/r/n"4 Cust_Count SQL_Latin1_General_CP1_CI_AS

Table is created. I can SELECT * FROM accepted and see my column names.

Then I try to BCP into the table using:

C:\>BCP sales..east in C:\Results\accepted.txt -t -f C:\bcpformat\accepted.fmt -Usa -Ppwd

I get this error:

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

and so on......

In the .fmt file I've tried "", "\t", " " and everthing I could think of as a delimiter. Still no luck. I've tried almost every switch available to both OSQL and BCP.
The data I am trying to BCP is a SQL result so I don't think any special delimiters are placed. I've tried not using the .fmt file and using the prompts but still no luck. Data is CAST in the query and doesn't excede 45 characters.
Hope I've explained my problem well enough.

-Deana

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-04 : 23:38:58

Welcome to SQLTeam Deana.

Let me try to understand what you are doing here first.
1. you use OSQL to export the data out from table accepted into accepted.txt
2. then you want to BCP IN accepted.txt into sales..east ?

Question
1. your fmt file is tab delimited. Is the accepted.txt tab delimited and CR+LF terminated ?

Try
1. use BCP OUT from the sales..east and save the FMT file and then edit this FMT file to your requirement. It is easier this way rather than create the FMT from scratch

Also the CR+LF is \r\n not /r/n. TAB is \t not /t

-----------------
[KH]
2006 a new beginning

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-04 : 23:40:54

Also refer to SQL Server Books Online on 'Using Format File' under Administering SQL Server - Importing and Exporting Data - Using bcp and BULK INSERT

-----------------
[KH]
2006 a new beginning

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-04 : 23:49:03

Also Refer this
http://www.nigelrivett.net/SQLTsql/ImportTextFiles.html

Madhivanan

Failing to plan is Planning to fail

DeanaMiller
Starting Member

12 Posts

Posted - 2006-01-05 : 11:35:21

Ok...at least I'm getting a different error:

Starting copy...
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP data-file

BCP copy in failed

Any help is appreciated. And how do I check to see if my accepted.txt is Tab Delimitied or CR+LF terminated....that might be my problem.

-Thanks
Deana

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-05 : 13:50:32

Also have a look at this.
http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

It is better to start with a manually created text file.
Create one with a single row and column and bcp it into a single column table.
When that works add another row.
When that works add more columns.
Keep going until you get a file identical to your produced file. You should find the problem - or it is something non-display in the file.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DeanaMiller
Starting Member

12 Posts

Posted - 2006-01-05 : 15:53:06

nr,
OK. I was able to import a text file I created:
1 1 1 1
2 2 2 2
3 3 3 3
..and it works so beautiful...
Then I appended my actual data to the file

1 1 1 1
2 2 2 2
3 3 3 3
526 KYDJJF 24 KY

and still got EOF error...until I went into the text file and manually added TABs between my '526 KYDJ.....' column data. Then it will import nicely...problem is that I have many txt files and don't have time to open them up one at a time and add TAB spaces. Any suggestions? I can save it as a .csv but still no spaces, EXCEL opens the .csv up with all data in one column.

DeanaMiller
Starting Member

12 Posts

Posted - 2006-01-05 : 20:14:26

Ok found the problem to be my first command:
C:\OSQL -D VID -i C:\accepted.sql -o C:\Results\accepted.txt -n -w500 -Usa

Changed to

C:\OSQL -D VID -w300 -s" " -i C:\accepted.sql -o C:\Results\accepted.txt -Usa

The -w300 makes the file wider.
The -s" " inserts a TAB delimiter

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-05 : 20:54:03

quote:
Originally posted by khtan
Question
1. your fmt file is tab delimited. Is the accepted.txt tab delimited and CR+LF terminated ?

-----------------
[KH]

DeanaMiller
Starting Member

12 Posts

Posted - 2006-01-05 : 22:08:03

Thanks for pointing me in the right direction khtan. -Deanna

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-05 : 23:15:02

quote:
Originally posted by DeanaMiller

Thanks for pointing me in the right direction khtan. -Deanna

you are most wellcome

-----------------
[KH]

tonyknibb
Starting Member

3 Posts

Posted - 2007-05-01 : 07:12:41

Hi peeps,

I've used BCP to output my table data (delimited with "|", but I could stick with tabs):

>>
1|Spiracles are used by the louse to:|1|1
2|How long does it take for a louse egg to hatch?|2|1
3|Head lice are transmitted via:|3|1
<<

How do I import this data to a specific table (AssessQs_tbl) using BCP? I'm looking for the simplest method here, as I'm a total noob.

The following code is used to output the data from one of my tables (which, rather obviously, I didn't write myself - I just tweaked it):

>>
DECLARE @FileName1 varchar(50),@bcpCommand1 varchar(2000)
SET @FileName1 = REPLACE('c:\TK_Assess_Qs_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand1 = 'bcp "SELECT * FROM headlice..AssessQs_tbl" queryout "'
SET @bcpCommand1 = @bcpCommand1 + @FileName1 + '" -S TONY\SQLEXPRESS -c -T /t "|"'
EXEC master..xp_cmdshell @bcpCommand1
<<

Also, I'm doing all this with SQLCMD (and MS SQL 2005) and I'd rather stick to that if at all possible.

Any help will be very much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources