Import Sheet to Table SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Import Sheet to Table SQL

Author

Topic

lalmeida
Starting Member

9 Posts

Posted - 2005-06-17 : 15:40:32

Hello!

I have a DTS what import sheet to table in SQL Database, but I need do a verification about the exists data in table, for don´t replicate data.

I was thinking do this with ActiveX Script Transoformation using a Visual Script ... See it:

Function Main()

If (DTSSource("dt_contato")) <= DTSDestination("dt_contato") and (DTSSource("hora_contato")) = DTSDestination("hora_conta") then
MsgBox "Dados já importados. Selecione outra planilha."
Endif
Main = ???

End Function

Anybody help me?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-17 : 15:43:41

Import the sheet into a staging table. Then use T-SQL to move your data from the staging table into your table:

INSERT INTO YourTable (Column1, ...)
SELECT Column1, ...
FROM StagingTable
WHERE NOT EXISTS (SELECT * FROM YourTable)

Tara

lalmeida
Starting Member

9 Posts

Posted - 2005-06-17 : 15:54:23

Hi Tara, see the procedure what I did, but there is an error what I don't know resolve:

Use Retencao

Create Proc ImportaDadosExcel @CaminhoPlanilha Varchar(30)
As
Set NoCount On --não indica a qtde de linhas afetadas pela instrução SQL

Declare @StringSql Varchar(1000)

Set @StringSql = 'Select * From OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',
''Data Source="' + @CaminhoPlanilha + '";User ID=Admin;Password=;Extended
properties=Excel 97-2000'')...Plan1$'

--cria tabela temporária
Create Table #Temp (
[nrc] [numeric](19, 0) NULL ,
[seg] [int] NOT NULL ,
[ate_ins] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[esc_tel] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[ddd_tel] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[nro_tel] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[cnl_ins] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[dt_contato] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[hora_conta] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[DATA_HORA] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
[qt_parcela] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[produto] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[versão] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[e_mail] [varchar] (40) COLLATE Latin1_General_CI_AS NULL ,
[dt_emissao] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[dt_instala] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[codigo_ate] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[nome_atend] [varchar] (40) COLLATE Latin1_General_CI_AS NULL ,
[origem] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[GRUPO] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[motivo_lig] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[DESCR_MOT] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[detalhe_mo] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[DESCR_DETALHE] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[reteve] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[submotivo] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[DESCR_SUBMOT] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[desconto] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[DESCR_DESCONTO] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[codajuste] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[DESCR_AJUSTE] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[ajuste] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[observacao] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[codigo_aud] [varchar] (6) COLLATE Latin1_General_CI_AS NULL ,
[nome_audit] [varchar] (40) COLLATE Latin1_General_CI_AS NULL ,
[atividade_] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[num_ativid] [varchar] (9) COLLATE Latin1_General_CI_AS NULL ,
[data_ativi] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[hora_ativi] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ligou_escr] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[nome_conta] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[data_agend] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[agendou_cl] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[confirmou_] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[nome_cont2] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[ligou_clie] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[status_aud] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[telefone_d] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[obs_audito] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[status_os] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[ddd_tel_n] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[nro_tel_n] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[endereco_n] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cnl_ins_n] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[cliente_n] [varchar] (64) COLLATE Latin1_General_CI_AS NULL ,
[tp_ativ_n] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[solicita_n] [varchar] (64) COLLATE Latin1_General_CI_AS NULL ,
[rgsolic_n] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[cpfsolic_n] [varchar] (11) COLLATE Latin1_General_CI_AS NULL ,
[nascsolicn] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[dddcont_n] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[nrocont_n] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[ddd_tel2_c] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[nro_tel2_c] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[e_mail_n] [varchar] (40) COLLATE Latin1_General_CI_AS NULL ,
[produto_n] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[versao_n] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[parcelas_n] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[provedor_n] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[obs_n] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[dt_inclu_n] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[hr_inclu_n] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[at_n] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[es_n] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[dddcont2_n] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[nrocont2_n] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[dddcont3_n] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[nrocont3_n] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[retmod] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[endretmod] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[numretmod] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[compretmod] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[bairetmod_] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[cepretmod_] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[munretmod] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[bairetmod2] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[cepretmod2] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[ufretmod] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[sedex] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[multa] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[vlrmulta] [float] NULL ,
[migracao] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[tpcompranp] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[produtonp] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[versaonp] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[produtold] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[versaoold] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[TRANSFE] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[DESC/ARGU] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[VÁLIDA S/N] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[APTA S/N] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[FILA AUD S/N] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[MES] [smalldatetime] NULL ,
[BLC] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[BLQ] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[DESPESQU] [varchar] (5) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]

--copia dados da planilha para tabela temporária
Insert Into #Temp Exec(@StringSql)

----insere na tabela do banco os registros não existentes
Insert Into OPERACAO
(nrc, seg, ate_ins, esc_tel, ddd_tel, nro_tel, cnl_ins, dt_contato, hora_conta,
DATA_HORA, qt_parcela, produto, versão, e_mail, dt_emissao, dt_instala, codigo_ate,
nome_atend, origem, GRUPO, motivo_lig, DESCR_MOT, detalhe_mo, DESCR_DETALHE, reteve,
submotivo, DESCR_SUBMOT, desconto, DESCR_DESCONTO, codajuste, DESCR_AJUSTE, ajuste,
observacao, codigo_aud, nome_audit, atividade_, num_ativid, data_ativi, hora_ativi,
ligou_escr, nome_conta, data_agend, agendou_cl, confirmou_, nome_cont2, ligou_clie,
status_aud, telefone_d, obs_audito, status_os, ddd_tel_n, nro_tel_n, endereco_n,
cnl_ins_n, cliente_n, tp_ativ_n, solicita_n, rgsolic_n, cpfsolic_n, nascsolicn,
dddcont_n, nrocont_n, ddd_tel2_c, nro_tel2_c, e_mail_n, produto_n, versao_n,
parcelas_n, provedor_n, obs_n, dt_inclu_n, hr_inclu_n, at_n, es_n, dddcont2_n,
nrocont2_n, dddcont3_n, nrocont3_n, retmod, endretmod, numretmod, compretmod,
bairetmod_, cepretmod_, munretmod, bairetmod2, cepretmod2, ufretmod, sedex, multa,
vlrmulta, migracao, tpcompranp, produtonp, versaonp, produtold, versaoold, TRANSFE,
[DESC/ARGU], [VÁLIDA S/N], [APTA S/N], [FILA AUD S/N], MES, BLC, BLQ, DESPESQU
)
Select
nrc, seg, ate_ins, esc_tel, ddd_tel, nro_tel, cnl_ins, dt_contato, hora_conta,
DATA_HORA, qt_parcela, produto, versão, e_mail, dt_emissao, dt_instala, codigo_ate,
nome_atend, origem, GRUPO, motivo_lig, DESCR_MOT, detalhe_mo, DESCR_DETALHE, reteve,
submotivo, DESCR_SUBMOT, desconto, DESCR_DESCONTO, codajuste, DESCR_AJUSTE, ajuste,
observacao, codigo_aud, nome_audit, atividade_, num_ativid, data_ativi, hora_ativi,
ligou_escr, nome_conta, data_agend, agendou_cl, confirmou_, nome_cont2, ligou_clie,
status_aud, telefone_d, obs_audito, status_os, ddd_tel_n, nro_tel_n, endereco_n,
cnl_ins_n, cliente_n, tp_ativ_n, solicita_n, rgsolic_n, cpfsolic_n, nascsolicn,
dddcont_n, nrocont_n, ddd_tel2_c, nro_tel2_c, e_mail_n, produto_n, versao_n,
parcelas_n, provedor_n, obs_n, dt_inclu_n, hr_inclu_n, at_n, es_n, dddcont2_n,
nrocont2_n, dddcont3_n, nrocont3_n, retmod, endretmod, numretmod, compretmod,
bairetmod_, cepretmod_, munretmod, bairetmod2, cepretmod2, ufretmod, sedex, multa,
vlrmulta, migracao, tpcompranp, produtonp, versaonp, produtold, versaoold, TRANSFE,
[DESC/ARGU], [VÁLIDA S/N], [APTA S/N], [FILA AUD S/N], MES, BLC, BLQ, DESPESQU
From [Plan1$] where hora_conta <> (Select hora_conta from OPERACAO) and
data_hora >= (Select max(dt_contato) from OPERACAO group by dt_contato)

--executa a procedure
ImportaDadosExcel 'D:\_LUCIANA_CTIS\PROJETO_RETENCAO\PLANILHA PARA CARGA\relstatus_oper_04062005.xls'

lalmeida
Starting Member

9 Posts

Posted - 2005-06-17 : 15:55:09

Tara,

The message error is:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Não foi possível encontrar ISAM instalável.]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-17 : 15:57:33

In response to your e-mail, a staging table is a copy of your real table. Say your real table is this:

CREATE TABLE Table1 (Column1 int, Column2 int)

Then your staging table would be:

CREATE TABLE Table1_Stage (Column1 int, Column2 int)

So import your data into Table_Stage as is, do not make any changes to it. Then run the INSERT statement to only copy the new rows and not duplicates.

Tara

lalmeida
Starting Member

9 Posts

Posted - 2005-06-17 : 16:10:21

Ok, I did it, but happend an erro in my sql server:
The message error is:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Não foi possível encontrar ISAM instalável.]

I updated MDAC 2.8, but the error continue.

Do you belive what I am doing it since monday? I am tired and thinking about give up...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-17 : 16:12:33

First, I can't read whatever language the error is. Second, post exactly what you tried. We need to know detail information. Like did you put the query in an Execute SQL Task inside the DTS package? Did the data get imported into the staging table correctly?

Tara

lalmeida
Starting Member

9 Posts

Posted - 2005-06-17 : 16:16:19

Ok, see it the query in an Execute SQL Task inside DTS package:

Select
nrc, seg, ate_ins, esc_tel, ddd_tel, nro_tel, cnl_ins, dt_contato, hora_conta,
DATA_HORA, qt_parcela, produto, versão, e_mail, dt_emissao, dt_instala, codigo_ate,
nome_atend, origem, GRUPO, motivo_lig, DESCR_MOT, detalhe_mo, DESCR_DETALHE, reteve,
submotivo, DESCR_SUBMOT, desconto, DESCR_DESCONTO, codajuste, DESCR_AJUSTE, ajuste,
observacao, codigo_aud, nome_audit, atividade_, num_ativid, data_ativi, hora_ativi,
ligou_escr, nome_conta, data_agend, agendou_cl, confirmou_, nome_cont2, ligou_clie,
status_aud, telefone_d, obs_audito, status_os, ddd_tel_n, nro_tel_n, endereco_n,
cnl_ins_n, cliente_n, tp_ativ_n, solicita_n, rgsolic_n, cpfsolic_n, nascsolicn,
dddcont_n, nrocont_n, ddd_tel2_c, nro_tel2_c, e_mail_n, produto_n, versao_n,
parcelas_n, provedor_n, obs_n, dt_inclu_n, hr_inclu_n, at_n, es_n, dddcont2_n,
nrocont2_n, dddcont3_n, nrocont3_n, retmod, endretmod, numretmod, compretmod,
bairetmod_, cepretmod_, munretmod, bairetmod2, cepretmod2, ufretmod, sedex, multa,
vlrmulta, migracao, tpcompranp, produtonp, versaonp, produtold, versaoold, TRANSFE,
[DESC/ARGU], [VÁLIDA S/N], [APTA S/N], [FILA AUD S/N], MES, BLC, BLQ, DESPESQU
From [Plan1$] where hora_conta <> (Select hora_conta from OPERACAO) and
data_hora >= (Select max(dt_contato) from OPERACAO group by dt_contato)

lalmeida
Starting Member

9 Posts

Posted - 2005-06-17 : 16:17:35

Do you have msn?
My msn is luciana_alsouza@msn.com
Please, add me.

lalmeida
Starting Member

9 Posts

Posted - 2005-06-17 : 16:18:42

What's your MSN Messenger ?

Do you have hotmail?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-17 : 16:30:18

I don't give that information out to people I don't know.

The query that you posted isn't using a staging table, so I'm not sure how I can help. You also haven't translated the error that you posted.

A staging table really is the solution when you are importing data into SQL Server.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources