Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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, October 3, 2013 5:13 PM
Points: 400, Visits: 339
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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 36,752, Visits: 31,208
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

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, October 3, 2013 5:13 PM
Points: 400, Visits: 339
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: 2 days ago @ 8:17 PM
Points: 12,881, Visits: 31,820
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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, October 3, 2013 5:13 PM
Points: 400, Visits: 339
Thanks that helped alot.

Post #1156506
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse