Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP command to import data from txt file (Pipe as Field terminator)into SQL Table. Expand / Collapse
Author
Message
Posted Saturday, August 6, 2011 2:44 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, February 5, 2015 12:41 PM
Points: 400, Visits: 343
Hi all

I am facing hard time in configuring the BCP command to to import data from txt file (Pipe as Field terminator)into SQL Table.

As the TAB is the default field terminator for BCP. And My source file is | (Pipe delimited file)

I tried this

BCP Work.dbo.Customer IN "\\MyMechine\Shared\Cust.txt" -T -S "SQLSERVER" -t|

Am I missing some thing here.
Please help me.

Post #1155488
Posted Saturday, August 6, 2011 2:38 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 40,969, Visits: 38,258
anand_vanam (8/6/2011)
Hi all

I am facing hard time in configuring the BCP command to to import data from txt file (Pipe as Field terminator)into SQL Table.

As the TAB is the default field terminator for BCP. And My source file is | (Pipe delimited file)

I tried this

BCP Work.dbo.Customer IN "\\MyMechine\Shared\Cust.txt" -T -S "SQLSERVER" -t|

Am I missing some thing here.
Please help me.



Are you getting an error or what? You haven't told us what the problem actually is.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

Helpful Links:
How to post code problems
How to post performance problems
Post #1155602
Posted Monday, August 8, 2011 11:01 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, February 5, 2015 12:41 PM
Points: 400, Visits: 343
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unexpected EOF encountered in
BCP data-file

0 rows copied.

I got this Error.

I even added the "format file".

I have 3 columns in the Table. No Identtity Column. and No PK's

CREATE TABLE dbo.Cust
(Sno INT,
Name Varchar(20),
Location Varchar(30)
)

And the .txt file has 3 columns... with Pipe delimited.

Ex:

1|Cust1|Newyook
2|John|Washington
100|Alan|Seattle



Post #1156252
Posted Monday, August 8, 2011 12:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:50 PM
Points: 14,433, Visits: 37,806
my bcp OUt exampel shows the delimiter between dbl quotes, and not the sole char after the -t.
try that...
also check your row terminator; if it's not CrLf, you might need to change /add the -r command for the row terminator:
-r"\r"

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM ##PIPE_REPORT " queryout C:\Data\Objects.txt -t"|" -c -T ' 



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1156290
Posted Monday, August 8, 2011 9:33 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, February 5, 2015 12:41 PM
Points: 400, Visits: 343
Thanks that helped alot.

Post #1156506
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse