Todos temos muitos dados no MS Excel que queremos guardar em nossas tabelas do banco de dados SQL Server, por exemplo. Faço muito isso, e a maneira CORRETA de fazê-lo é criando uma Proc - Stored Procedure - para cada operação, executando-as a partir do VBA.
Mas também existe a maneira mais RÁPIDA de se fazer, e também a menos correta, construindo uma cadeia de código String SQL, executando-a. Mas, como pôde imaginar pelo título, escolhi a maneira CORRETA, certo? Errado, escolhi a maneira RÁPIDA e por isso estamos aqui, para aprender com os erros.
Aqui estão, ambas as formas. A mais longa (e CORRETA) e a mais curta (e RÁPIDA) de se fazer isso.
Para ajudar, como sempre acontece, alguns números tinham sido formatados como datas. Havia algum código que se parecia com isso:
vaData = lo.DataBodyRange.ValuesSql = "INSERT INTO Blend (ManifestID) VALUES (" & vaData(i,1) & ")"adConn.Execute sSql
O campo ManifestID é um BIGINT e vaData (i, 1) um campo com valores no formato 4/15/2023. Mas no campo ManifestID continha valores como 45031, alguém (eu) se confundiu, com a data, que perdeu a formatação. Assim, percebi que várias dezenas de entradas colocaram zero no campo ManifestID. O SQL Server obedientemente executou 2023/04/15, fazendo uma divisão (dividindo 4 por 15, dividido por 2023), surgindo diversos zeros, que foram colocados nos campos.
Depois de alguma auto-flagelação, me perguntava se uma Proc teria pego esse erro. Conclui que ao tentar passar uma data como parâmetro para o BIGINT, o código teria dado errado e teria evitado toda essa confusão. Mas estava errado. Em vez disso, a Proc teria convertido a data para o seu valor inteiro - ao invés de dividí-lo como no método SQL String. O Excel armazena datas com números de dias desde 1899/12/31. E isso não é tudo, pois tenho certeza de que o SQL Server não armazena dessa forma. E como o ADO ou T-SQL sabem que precisam convertê-lo dessa forma?
Assim, fiz um teste. Primeiro criei uma tabela:
CREATE TABLE [dbo].[TestDateBigInt]( [c1] [BIGINT] NULL
Depois criei uma Proc para inserir registros:
CREATE PROCEDURE [dbo].[spTestDateBigInt] @BigInt AS BIGINTAS INSERT INTO dbo.TestDateBigInt ( c1 ) VALUES ( @BigInt -- c1 - bigint )
Então escrevi algum código para inserir linhas:
Sub TestDateBigInt()
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim pm As ADODB.Parameter
Dim rs As ADODB.Recordset
Dim sSql As String
Dim i As Long
Dim vaFormats As Variant
Dim vaData As Variant
On Error GoTo ErrH
Set cn = New ADODB.Connection
cn.Open msCONN
Let vaFormats = Split("General m/d/yyyy")
For i = 0 To 1
'Change the format
Let Sheet1.Range("G1").NumberFormat = vaFormats(i)
Let vaData = Sheet1.Range("G1:G2").Value
'Insert record
Let sSql = "INSERT INTO TestDateBigInt (c1) VALUES (" & vaData(1, 1) & ")"
cn.Execute sSql
'Insert record via stored procedure
Set cmd = New ADODB.Command
Let cmd.ActiveConnection = cn
Let cmd.CommandText = "spTestDateBigInt"
Let cmd.CommandType = adCmdStoredProc
Set pm = cmd.CreateParameter("@BigInt", adBigInt, adParamInput)
Let pm.Value = Sheet1.Range("G1").Value
cmd.Parameters.Append pm
cmd.Execute
Next i
ErrH:
On Error Resume Next
Set rs = cn.Execute("SELECT * FROM TestDateBigInt")
Debug.Print rs.GetString
rs.Close
cn.Close
Set rs = Nothing
Set cmd = Nothing
Set cn = Nothing
End Sub
No código, defini dois formatos gerais para a matriz: m / d / aaaa. Ao percorrer essa matriz e aplicar os formatos da célula G1 onde estão os número inteiros insuspeitos.
Na primeira passagem, ele é formatado como geral, aparecendo como um número inteiro adequado. Então preparei a string INSERT INTO SQL e a executei logo após a conexão. Assim, ainda dentro do loop, faço tudo da maneira certa: Crio um objeto, adiciono um parâmetro, e o executo.
Na segunda iteração do loop, a célula G1 fica formatada como data, e então, tudo acontece novamente.
Estava esperando um erro, então tinha um manipulador de erros que mostraria toda a tabela sempre que algo não ocorresse bem. Mas tudo ocorreu bem. Foi muito bem executado.
4500045000045000
Com o número inteiro formatado, tanto o método SQL String como o método Proc inserem os dados corretamente.
Fiquei feliz por compreender todo o código mais claramente e ter aprendido a lição, prometendo sempre usar Procs como um bom menino.
#A&A #ADO #MSEXCEL #EXCEL #PIECEOFCAKE #SQL #SQLServer #SQLString #StoredProcedures #TSQL #VBA #POC
Defina a Latitude e a Longitude - Find Latitude and Longitude of any address using Google Map API and VBA
VBA Excel - Traduzindo Planilhas - MS Excel VBA Script to Translate worksheets using the Google Translate API
Excel - Manipule o Google Maps em sua Planilha - Put a Google Map in your Spreadsheet
Convertendo Texto em Imagem - Convert Text to an Image using the VBA Windows API
Correção de Métricas - For Subscripts, Superscripts and Common Typos
MS Access - Cinco Formas Manuais de Reparo
MS Access e MS Word - Técnica de Automação
Microsoft Access - Removendo Prefixo das Tabelas
Sempre Use Stored Procedures - Always Use Stored Procedures
A&A - Dados ou Informações?
Consulte-nos
⬛◼◾▪ CONTATO ▪◾◼⬛
Nenhum comentário:
Postar um comentário