Import data using BCP

Question

Post reply

Import data using BCP

apat

SSCertifiable

Points: 5929
More actions
February 8, 2010 at 1:46 pm

#215396

All,
I have 100 files in a folder on my local computer. I would like to import data from all those files with name ending "_Final" to my sql server table using BCP. I can not use bulk insert as the data is on my pc and dont have access to server.
So if there are 30 such files with this name then the data from only those 30 files to go to table.
Thanks in advance...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

Is the layoutthe same for all the files? Are they all going into the same table? Do you have a format file?

Shamless self promotion - read my blog http://sirsql.net

Laerte Poltronieri Junior-367636 Hall of Fame Points: 3314 More actions · Answer 2

Hi Apat, I dont know if I understand well, but You said you can not use bulk insert because you dont have access to the server. I belive you said to use BCP because you can pass server and database..even this way, you have to share the paths to your files.

Well, I belive powershell can help this case. and you can use bulk insert, passing the servername, database, password..etc

Try this

let say he files are :

1,1,1,1

2,2,2,2

3,3,3,3

and the table

CREATE TABLE testebcp (c INT, c1 INT, c2 INT, c3 INT)

Get-ChildItem \\yourpath\...| Where-Object { $_.name -match ""_Final" } | foreach { Invoke-Sqlcmd -ServerInstance YourServer -Database YourDatabase -Username YourUserUname -Password YourPassword -Query "BULK INSERT dbo.testebcp FROM \\yourX\YourPath\$($_)'

WITH

(

FIELDTERMINATOR =','

)

" }

$hell your Experience !!![/url]

apat SSCertifiable Points: 5929 More actions · Answer 3

Yes format is the same for all the files. They all are going to the same table and I do not have format file.

Each file has 3 columns and character data.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 4

I would follow the process that Laerte posted. It's quick and simple to manage and does exactly what you need.

Shamless self promotion - read my blog http://sirsql.net

apat SSCertifiable Points: 5929 More actions · Answer 5

will bulk insert work if I have files lying on my local desktop?