I have csv file with column: id,name,value
I need to write simple Test.bat file (windows batch file), where I put:
server name, database, table, user and pass
and script will insert all *.csv data to my db. (something similar to SQL loader)
2
-
Hi Shnugo, thanks it works :)4est– 4est2016-03-07 13:20:59 +00:00Commented Mar 7, 2016 at 13:20
-
BCP - it's installed with MS Management Studio? because if someone doesnt have, will not run my bat file4est– 4est2016-03-07 13:23:42 +00:00Commented Mar 7, 2016 at 13:23
Add a comment
|
2 Answers
ad BCP:
There is no system built in tool which is there by any means. But I think BCP is installed together with the SQL Server - probably not for all client's machines...
ad security:
One problem with a BCP call within a BAT-file is security... Be aware that everybody who can reach your BAT-File finds the full connection string in it. Everybody could introduce any SQL cmd from there...
ad own application
It might be easier and better in this point of view to write a tiny application in the coding laguage you like most, which does what you want. Thus it's on you to make sure, that this app exists on your client's machines. Such an app allows you to do side work like tracing a log file, show "real" error messages or do some kind of user's right management.
answered Mar 7, 2016 at 14:43
Gottfried Lesigang
67.6k9 gold badges60 silver badges124 bronze badges
Comments
ok,
by user I mean people from my team, but with are using Windows Authentication....
do you recomend to use other tools?
I have already wrote vba code to upload data(conn.Execute "insert.....), but it's not so fast as BCP. Maybe if you have other examples as better solution please share
4 Comments
Gottfried Lesigang
Hi, Please avoid posting answers which aren't answers... Better post a comment to the appropriate contribution or edit your question. Now you have a new question. You might think to close this question (with vote and/or acceptance - that's up to you) and then start a new question. Please post the VBA you use, At the end BCP itself is nothing else than a tool, running in your client's context. Any tool you create can be quite as fast...
4est
my vba insert code: Dim conn As ADODB.Connection Dim rcrds As ADODB.Recordset Dim iRowNo, i As Long Dim id, name, value As String Dim wb As Workbook Dim ws As Worksheet Set wb = ActiveWorkbook Set ws = wb.Worksheets("Sheet1") Dim login,passwd,host,dbname As String login = "" passwd = "" host = "" dbname = " Set conn = New ADODB.Connection
4est
With conn .ConnectionString = "driver={sql server};Server=" + host + _ ";Database=" + dbname + _ ";User ID=" + login + _ ";Password=" + passwd + _ ";Trusted_Connection=False;" .Open End With iRowNo = 1
4est
Do Until ws.Cells(iRowNo, 1) = "" id = ws.Cells(iRowNo, 1) name = ws.Cells(iRowNo, 2) value = ws.Cells(iRowNo, 3) conn.Execute "insert into table_name(id, name, value) values ('" & id & "', '" & name & "', '" & value & "')" iRowNo = iRowNo + 1 Loop conn.Close