Экспорт Excel в SQL с помощью VBA

Был надеяться, что кто-то может помочь переписать это для меня. Я задал этот вопрос раньше, но не получил необходимую помощь.

Я пытаюсь создать файл excel с макросом, прикрепленным к кнопке, которая будет экспортировать данные в SQL.

При создании макроса в Visual Basic для приложений мой код выглядит следующим образом:

Sub Button1_Click() Dim conn As New ADODB.Connection Dim iRowNo As Integer Dim sRecordedPeriod, sEventDate, sID, sDeptCode, sOpCode, sStartTime, sFinishTime, sUnits As String With Sheets("Sheet1") 'Open a connection to SQL Server conn.Open "Provider=SQLOLEDB;Data Source=db1\db1;Initial Catalog=ProdTrack;Integrated Security=SSPI;" 'Skip the header row iRowNo = 2 'Loop until empty cell in FirstName Do Until .Cells(iRowNo, 1) = "" sRecordedPeriod = .Cells(iRowNo, 1) sEventDate = .Cells(iRowNo, 2) sID = .Cells(iRowNo, 3) sDeptCode = .Cells(iRowNo, 4) sOpCode = .Cells(iRowNo, 5) sStartTime = .Cells(iRowNo, 6) sFinishTime = .Cells(iRowNo, 7) sUnits = .Cells(iRowNo, 8) 'Generate and execute sql statement to import the excel rows to SQL Server table conn.Execute "insert into dbo.TimeLog (RecordedPeriod, EventDate, ID, DeptCode, Opcode, StartTime, FinishTime, Units) values ('" & sRecordedPeriod & "', '" & sEventDate & "', '" & sID & "', '" & sDeptCode & "', '" & sOpCode & "', '" & sStartTime & "', '" & sFinishTime & "', '" & sUnits & "')" iRowNo = iRowNo + 1 Loop MsgBox "Data Successfully Exported." conn.Close Set conn = Nothing End With End Sub 

Я получаю это сообщение об ошибке при экспорте.

Ошибка времени выполнения '2147217913 (80040e07)':

Ошибка конверсии при преобразовании даты и / или времени из символьной строки.

Таблица, которую я пытаюсь экспортировать в SQL, выглядит следующим образом. Я не думаю, что получаю ошибку в EventTime поскольку тип данных – varchar(8) не тип данных даты и времени …

Моя таблица SQL выглядит следующим образом:

  RecordedPeriod DATETIME NOT NULL DEFAULT (GETDATE()), EventDate (varchar(8), not null) ID (int, not null) DeptCode (varchar(2), not null) OpCode (varchar(2), not null) StartTime (time(0), not null) FinishTime (time(0), not null) Units (int, not null) 

Вот как выглядит таблица Excel:

 RecordedPeriod EventDate ID DeptCode OpCode StartTime FinishTime Units null 6/22/17 45318 DC DC 8:00:00 8:15:00 250 

введите описание изображения здесь

Вот подход к параметрам:

 Dim conn As ADODB.Connection Dim cmd As ADODB.Command Dim strSQL As String strSQL = "INSERT INTO dbo.TimeLog " & _ "(RecordedPeriod, EventDate, ID, DeptCode, Opcode, StartTime, FinishTime, Units) " & _ "VALUES (?,?,?,?,?,?,?,?);" Set conn = New ADODB.Connection conn.Open ""Provider=SQLOLEDB;Data Source=db1\db1;Initial Catalog=ProdTrack;Integrated Security=SSPI;" 'Skip the header row iRowNo = 2" Set cmd = New ADODB.Command cmd.ActiveConnection = conn cmd.CommandType = adCmdText cmd.CommandText = strSQL iRowNo = 2 With Sheets("Sheet1") 'Loop until empty cell in FirstName Do Until .Cells(iRowNo, 1) = "" cmd.Parameters.Append _ cmd.CreateParameter("pRecordedPeriod", adDBTimeStamp, adParamInput, 8, .Cells(iRowNo, 1)) cmd.Parameters.Append _ cmd.CreateParameter("pEventDate", adVarChar, adParamInput, 8, .Cells(iRowNo, 2)) cmd.Parameters.Append _ cmd.CreateParameter("pID", adInteger, adParamInput, , .Cells(iRowNo, 3)) cmd.Parameters.Append _ cmd.CreateParameter("pDeptCode", adVarChar, adParamInput, 2, .Cells(iRowNo, 4)) cmd.Parameters.Append _ cmd.CreateParameter("pOpCode", adVarChar, adParamInput, 2, .Cells(iRowNo, 5)) cmd.Parameters.Append _ cmd.CreateParameter("pStartTime", adDBTime, adParamInput, 0, .Cells(iRowNo, 6)) cmd.Parameters.Append _ cmd.CreateParameter("pFinishTime", adDBTime, adParamInput, 0, .Cells(iRowNo, 7)) cmd.Parameters.Append _ cmd.CreateParameter("pUnits", adInteger, adParamInput, , .Cells(iRowNo, 8)) cmd.Execute iRowNo = iRowNo + 1 Loop End With conn.Close Set conn = Nothing 

DataTypeEnum

Но это нехорошо кодировать, по сравнению с параметром-параметром DAO. Нет именованных параметров, пять параметров для настройки. Это для Stored Procedures ? Для CRUD это не весело.

АДО на помощь.

 Dim cn As New ADODB.Connection ''You should probably change Activeworkbook.Fullname to the ''name of your workbook strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _ & ActiveWorkbook.FullName _ & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";" cn.Open strCon s = "INSERT INTO [ODBC;Description=TEST;DRIVER=SQL Server;" _ & "SERVER=Server;Trusted_Connection=Yes;" _ & "DATABASE=test].SomeTable ( Col1, Col2, Col3, Col4 ) " _ & "SELECT a.Col1, a.Col2, a.Col3, a.Col4 " _ & "FROM [Sheet2$] a " _ & "LEFT JOIN [ODBC;Description=TEST;DRIVER=SQL Server;" _ & "SERVER=Server;Trusted_Connection=Yes;" _ & "DATABASE=test].SomeTable b ON a.Col1 = b.Col1 " _ & "WHERE b.Col1 Is Null" cn.Execute s 

Также ознакомьтесь с этими ссылками.

https://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm

http://tomaslind.net/2013/12/26/export-data-excel-to-sql-server/

Вот еще один способ сделать это. Может быть, это легче следовать …

Добавить ссылку на: Microsoft ActiveX Data Objects 2.8 Library

 Sub Button_Click() 'TRUSTED CONNECTION On Error GoTo errH Dim con As New ADODB.Connection Dim rs As New ADODB.Recordset Dim strPath As String Dim intImportRow As Integer Dim strFirstName, strLastName As String Dim server, username, password, table, database As String With Sheets("Sheet1") server = .TextBox1.Text table = .TextBox4.Text database = .TextBox5.Text If con.State <> 1 Then con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;" 'con.Open End If 'this is the TRUSTED connection string Set rs.ActiveConnection = con 'delete all records first if checkbox checked If .CheckBox1 Then con.Execute "delete from tbl_demo" End If 'set first row with records to import 'you could also just loop thru a range if you want. intImportRow = 10 Do Until .Cells(intImportRow, 1) = "" strFirstName = .Cells(intImportRow, 1) strLastName = .Cells(intImportRow, 2) 'insert row into database con.Execute "insert into tbl_demo (firstname, lastname) values ('" & strFirstName & "', '" & strLastName & "')" intImportRow = intImportRow + 1 Loop MsgBox "Done importing", vbInformation con.Close Set con = Nothing End With Exit Sub errH: MsgBox Err.Description End Sub 

введите описание изображения здесь

В mssql он распознает значение datetime в Excel как текст, поэтому вы должны использовать текст как дату и время.

cast ('8:00' как дата)

примените к вашему коду с помощью метода верхнего литья.

также ячейки (i, 1) определяют числовое значение.

Ваш sEventDate является строкой, поэтому sEventDate = .Cells (iRowNo, 2). Текст

 Sub Button1_Click() Dim conn As New ADODB.Connection Dim iRowNo As Integer Dim sRecordedPeriod, sEventDate, sID, sDeptCode, sOpCode, sStartTime, sFinishTime, sUnits As String With Sheets("Sheet1") 'Open a connection to SQL Server conn.Open "Provider=SQLOLEDB;Data Source=db1\db1;Initial Catalog=ProdTrack;Integrated Security=SSPI;" 'Skip the header row iRowNo = 2 'Loop until empty cell in FirstName Do Until .Cells(iRowNo, 1) = "" sRecordedPeriod = .Cells(iRowNo, 1) sEventDate = .Cells(iRowNo, 2).Text sID = .Cells(iRowNo, 3) sDeptCode = .Cells(iRowNo, 4) sOpCode = .Cells(iRowNo, 5) sStartTime = .Cells(iRowNo, 6) sFinishTime = .Cells(iRowNo, 7) sUnits = .Cells(iRowNo, 8) 'Generate and execute sql statement to import the excel rows to SQL Server table conn.Execute "insert into dbo.TimeLog (RecordedPeriod, EventDate, ID, DeptCode, Opcode, StartTime, FinishTime, Units) values ( '" & sRecordedPeriod & "' , '" & sEventDate & "' , '" & sID & "', '" & sDeptCode & "', '" & sOpCode & "', cast('" & sStartTime & "' as datetime) , cast('" & sFinishTime & "' as datetime), '" & sUnits & "')" iRowNo = iRowNo + 1 Loop MsgBox "Data Successfully Exported." conn.Close Set conn = Nothing End With End Sub 
  • Как excel конвертирует юлианские даты в даты календаря?
  • Как я могу обрезать результаты после определенного количества символов в SQL Server
  • «Поставщик« Microsoft.ACE.OLEDB.12.0 »не зарегистрирован на локальном компьютере« Ошибка при импорте процесса xlsx на сервер sql
  • импорт данных из Excel на SQL Server 2008
  • Лучший способ генерировать отчет о Excel
  • Как отслеживать изменения в MS SQL Server 2008
  • Извлечение табличных данных в SQL Server 2008 в таблицу Excel
  • Текст был усечен или один или несколько символов не совпадали на целевой кодовой странице. При импорте из файла Excel
  • SQL Server 2008 openrowset
  • Excel в SQL Server с Microsoft.ACE.OLEDB.12.0
  • Как безопасно подключать Excel к SQL Server 2008?
  • Interesting Posts

    Как я могу программно импортировать данные Excel в таблицу Access?

    C # Программный доступ к макросам Excel

    Импорт таблиц слов в excel с форматированием

    Как написать вложенную формулу Excel для SQL

    Ссылка на листы по номеру индекса

    (VBA) Countif не может смотреть в другом листе и возвращать ответ на оригинальный лист

    Присоединение к выражению, которое не поддерживается, произошло, когда осталось присоединиться к двум листам из двух разных книг

    Как передать цель в другой макрос в событиях Excel?

    API Excel JavaScript: найти родительские объекты

    Как выводить значения из ячеек в Excel в текстовые поля с датой в качестве параметра поиска

    Извлечение вывода из основной функции в пакете psych в качестве кадра данных

    Можете ли вы загрузить файл Excel с макросами в Google Spreadsheet?

    Как автоматически запускать макрос Microsoft Excel каждый раз, когда он изменяется?

    Excel 2007 простая копия и вставка vba, не работающая в excel 2016

    Небольшая настройка кода для Excel «Rollup»

    Давайте будем гением компьютера.