February 8, 2010 at 1:46 pm
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...
February 8, 2010 at 2:46 pm
Is the layoutthe same for all the files? Are they all going into the same table? Do you have a format file?
February 8, 2010 at 4:11 pm
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 =','
)
" }
February 8, 2010 at 4:12 pm
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.
February 8, 2010 at 4:14 pm
I would follow the process that Laerte posted. It's quick and simple to manage and does exactly what you need.
February 8, 2010 at 4:17 pm
will bulk insert work if I have files lying on my local desktop?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply