Mover dados do Excel para o Access

Este artigo mostra-lhe como mover os seus dados do Excel para o Access e converter os seus dados em tabelas relacionais para que possa utilizar o Microsoft Excel e o Access juntos. Resumindo, o Acesso é o melhor para capturar, armazenar, consultar e partilhar dados, e o Excel é o melhor para calcular, analisar e visualizar dados.

Dois artigos, Using Access ou Excel para gerir os seus dados e top 10 razões para usaro Access with Excel, discutam qual o programa mais adequado para uma determinada tarefa e como usar o Excel e o Access em conjunto para criar uma solução prática.

Quando transfere os dados do Excel para o Access, existem três passos básicos para o processo.

três passos básicos

Nota: Para obter informações sobre modelação de dados e relações no Access, consulte o básico de design da Base de Dados.

Passo 1: Importar dados do Excel para o Acesso

Importar dados é uma operação que pode ir muito mais suavemente se você demorar algum tempo a preparar e limpar os seus dados. Importar dados é como mudar-se para uma nova casa. Se limpar e organizar os seus bens antes de se mudar, instalar-se na sua nova casa é muito mais fácil.

Limpe os seus dados antes de importar

Antes de importar dados para o Access, no Excel é uma boa ideia para:

  • Converter células que contenham dados não atómicos (isto é, múltiplos valores numa célula) para várias colunas. Por exemplo, uma célula numa coluna "Skills" que contenha múltiplos valores de habilidade, tais como a programação "C#", "programação VBA" e "Web design" deve ser dividida em colunas separadas que cada uma contém apenas um valor de habilidade.

  • Utilize o comando TRIM para remover os espaços de liderança, de selagem e múltiplos espaços embutidos.

  • Remova caracteres não-impressos.

  • Encontre e corrija erros de ortografia e pontuação.

  • Remova linhas duplicadas ou campos duplicados.

  • Certifique-se de que as colunas de dados não contêm formatos mistos, especialmente números formatados como texto ou datas formatadas como números.

Para mais informações, consulte os seguintes tópicos de ajuda do Excel:

Nota: Se as suas necessidades de limpeza de dados forem complexas, ou se não tiver tempo ou recursos para automatizar o processo por conta própria, poderá considerar usar um fornecedor de terceiros. Para mais informações, procure "software de limpeza de dados" ou "qualidade de dados" pelo seu motor de pesquisa favorito no seu navegador Web.

Escolha o melhor tipo de dados quando importa

Durante a operação de importação no Access, pretende fazer boas escolhas para que receba poucos (se houver) erros de conversão que exigirão uma intervenção manual. A tabela que se segue resume como os formatos de número excel e os tipos de dados de acesso são convertidos quando importa dados do Excel para o Access, e oferece algumas dicas sobre os melhores tipos de dados para escolher no Assistente de Folha de Cálculo de Importação.

Formato de número Excel

Tipo de dados do Access

Comentários

Melhor prática

Texto

Texto, Memorando

O tipo de dados de Texto de Acesso armazena dados alfanuméricos até 255 caracteres. O tipo de dados access Memo armazena dados alfanuméricos até 65.535 caracteres.

Escolha o Memo para evitar truncar quaisquer dados.

Número, Percentagem, Fração, Científico

Número

O acesso tem um tipo de dados número que varia com base numa propriedade de Tamanho de Campo (Byte, Integer, Long Integer, Single, Double, Decimal).

Escolha o Dobro para evitar erros de conversão de dados.

Data

Data

Access e Excel usam o mesmo número de data de série para armazenar datas. No Access, o intervalo de datas é maior: de -657.434 (1 de janeiro de 100 d.C.) a 2.958.465 (31 de dezembro de 9999 d.C.).

Como o Access não reconhece o sistema de datas de 1904 (usado no Excel para o Macintosh), é necessário converter as datas no Excel ou no Access para evitar confusões.

Para mais informações, consulte Alterar o sistema de data, formato ou interpretação de dois dígitos e importar ou ligar aos dados num livro do Excel.

Escolha a data.

Hora

Hora

Aceda e Excel ambos armazenam valores de tempo utilizando o mesmo tipo de dados.

Escolha o tempo, que normalmente é o padrão.

Moeda, Contabilidade

Moeda

No Access, o tipo de dados da Moeda armazena dados como números de 8 bytes com precisão em quatro casas decimais, e é usado para armazenar dados financeiros e impedir o arredondamento de valores.

Escolha moeda, que normalmente é o padrão.

booleano

Sim/Não

O acesso utiliza -1 para todos os valores Sim e 0 para todos os valores Sem valores, enquanto o Excel utiliza 1 para todos os valores TRUE e 0 para todos os valores FALSOs.

Escolha Sim/Não,que converte automaticamente os valores subjacentes.

Hiperligação

Hiperligação

Uma hiperligação no Excel and Access contém um URL ou endereço Web que pode clicar e seguir.

Escolha Hyperlink, caso contrário o acesso pode utilizar o tipo de dados de texto por padrão.

Uma vez que os dados estão no Access, pode eliminar os dados do Excel. Não se esqueça de apoiar primeiro o livro original do Excel antes de o apagar.

Para mais informações, consulte o tópico de ajuda do Access Import ou link para dados num livro do Excel.

Anexar automaticamente os dados da maneira mais fácil

Um problema comum que os utilizadores do Excel têm é a adesão de dados com as mesmas colunas numa grande folha de cálculo. Por exemplo, você pode ter uma solução de rastreio de ativos que começou no Excel mas agora cresceu para incluir ficheiros de muitos grupos de trabalho e departamentos. Estes dados podem estar em diferentes folhas de cálculo e livros de trabalho, ou em ficheiros de texto que são feeds de dados de outros sistemas. Não existe nenhum comando de interface de utilizador ou forma fácil de anexar dados semelhantes no Excel.

A melhor solução é utilizar o Access, onde pode facilmente importar e anexar dados numa tabela utilizando o Assistente de Folha de Cálculo de Importação. Além disso, pode anexar muitos dados numa tabela. Pode salvar as operações de importação, adicioná-las como tarefas programadas do Microsoft Outlook e até utilizar macros para automatizar o processo.

Passo 2: Normalizar os dados utilizando o Assistente de Analisador de Tabelas

À primeira vista, passar pelo processo de normalização dos seus dados pode parecer uma tarefa assustadora. Felizmente, normalizar tabelas no Access é um processo muito mais fácil, graças ao Assistente de Análise de Mesa.

o assistente de análise de tabelas

1. Arrastar colunas selecionadas para uma nova tabela e criar automaticamente relações

2. Utilize comandos de botões para mudar o nome de uma tabela, adicionar uma chave primária, fazer de uma coluna existente uma chave primária e desfazer a última ação

Pode utilizar este assistente para fazer o seguinte:

  • Converta uma tabela num conjunto de tabelas mais pequenas e crie automaticamente uma relação chave primária e estrangeira entre as tabelas.

  • Adicione uma chave primária a um campo existente que contenha valores únicos, ou crie um novo campo de ID que utilize o tipo de dados AutoNumber.

  • Criar automaticamente relações para impor a integridade referencial com atualizações em cascata. As eliminações em cascata não são adicionadas automaticamente para evitar a eliminação acidental de dados, mas pode facilmente adicionar eliminações em cascata mais tarde.

  • Procure novas tabelas para dados redundantes ou duplicados (como o mesmo cliente com dois números de telefone diferentes) e atualize-os como desejado.

  • Volte para a mesa original e mude o nome, anexando "_OLD" ao seu nome. Em seguida, cria-se uma consulta que reconstrói a tabela original, com o nome original da tabela para que quaisquer formulários ou relatórios existentes baseados na tabela original funcionem com a nova estrutura da tabela.

Para mais informações, consulte Normalizar os seus dados utilizando o Analisador de Tabelas.

Passo 3: Ligar a aceder aos dados do Excel

Depois de os dados terem sido normalizados no Access e de ter sido criada uma consulta ou tabela que reconstrói os dados originais, é uma simples questão de ligar aos dados de Acesso do Excel. Os seus dados estão agora no Access como fonte externa de dados, pelo que podem ser ligados ao livro através de uma ligação de dados, que é um recipiente de informação que é usado para localizar, iniciar sessão e aceder à fonte de dados externo. As informações de ligação são armazenadas no livro e também podem ser armazenadas num ficheiro de ligação, como um ficheiro de Ligação de Dados do Office (ODC) (extensão de nome de ficheiro.odc) ou um ficheiro Data Source Name (extensão.dsn). Depois de se ligar a dados externos, também pode atualizar automaticamente (ou atualizar) o seu livro excel do Access sempre que os dados forem atualizados no Access.

Para mais informações, consulte os dados da Importação de fontes de dados externas (Consultade Energia) .

Obtenha os seus dados no Access

Esta secção acompanha-o através das seguintes fases de normalização dos seus dados: Quebrar valores nas colunas Salesperson e Address nas suas peças mais atómicas, separando os sujeitos relacionados nas suas próprias mesas, copiando e colando essas tabelas do Excel em Acesso, criação de relações-chave entre as recém-criadas tabelas de Acesso, e criação e execução de uma simples consulta no Acesso para devolver informação.

Dados de exemplo em forma não normalizada

A seguinte folha de cálculo contém valores não atómicos na coluna Salesperson e na coluna 'Endereço'. Ambas as colunas devem ser divididas em duas ou mais colunas separadas. Esta folha de cálculo também contém informações sobre vendedores, produtos, clientes e encomendas. Estas informações devem também ser divididas, por sujeito, em tabelas separadas.

Representante de Vendas

ID da Encomenda

Data da Encomenda

ID do Produto

Rio Qty

Preço

Nome do cliente

Endereço

Telemóvel

Li

2349

3/4/09

C-789

3

$7,00

Café Quatro

7007 Cornell St Redmond, WA 98199

425-555-0201

Li

2349

3/4/09

C-795

6

$9.75

Café Quatro

7007 Cornell St Redmond, WA 98199

425-555-0201

Adams

2350

3/4/09

A-2275

2

$16.75

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams

2350

3/4/09

F-198

6

$5.25

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams

2350

3/4/09

B-205

1

$4,50

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance

2351

3/4/09

C-795

6

$9.75

Contoso, Lda.

2302 Harvard Ave Bellevue, WA 98227

425-555-0222

Hance

2352

3/5/09

A-2275

2

$16.75

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance

2352

3/5/09

D-4420

3

$7.25

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Koch

2353

3/7/09

A-2275

6

$16.75

Café Quatro

7007 Cornell St Redmond, WA 98199

425-555-0201

Koch

2353

3/7/09

C-789

5

$7,00

Café Quatro

7007 Cornell St Redmond, WA 98199

425-555-0201

Informação nas suas partes mais pequenas: dados atómicos

Trabalhando com os dados neste exemplo, pode utilizar o comando Texto à Coluna em Excel para separar as partes "atómicas" de uma célula (como endereço de rua, cidade, estado e código postal) em colunas discretas.

A tabela que se segue mostra as novas colunas na mesma folha de cálculo depois de terem sido divididas para tornar todos os valores atómicos. Note que as informações na coluna Salesperson foram divididas em colunas De Nome E Primeiro Nome e que as informações na coluna 'Endereço' foram divididas em colunas de Endereço de Rua, Cidade, Estado e Código POSTAL. Estes dados estão na "primeira forma normal".

Apelido

Nome Próprio

 

Rua

Cidade

Distrito

Código Postal

Rio Li

Rio Yale

2302 Harvard Ave

Belavista

Setúbal

98227

Adams

Ellen

Círculo de Columbia 1025

Kirkland

Setúbal

98234

Hance

Jim

2302 Harvard Ave

Belavista

Setúbal

98227

Rio Koch

Junco

7007 Cornell St Redmond

Redmond

Setúbal

98199

A fuga de dados em assuntos organizados no Excel

As várias tabelas de dados de exemplo que se seguem mostram as mesmas informações da folha de cálculo do Excel depois de terem sido divididas em tabelas para vendedores, produtos, clientes e encomendas. O design da mesa não é final, mas está no caminho certo.

A tabela Salespersons contém apenas informações sobre o pessoal de vendas. Note que cada registo tem um ID único (ID SalesPerson). O valor de ID do SalesPerson será utilizado na tabela Encomendas para ligar encomendas a vendedores.

Vendedores

ID do vendedor

Apelido

Nome Próprio

101

Rio Li

Rio Yale

103

Adams

Ellen

105

Hance

Jim

107

Rio Koch

Junco

A tabela Produtos contém apenas informações sobre produtos. Note que cada registo tem um ID único (ID do produto). O valor de ID do produto será utilizado para ligar as informações do produto à tabela Detalhes da Encomenda.

Produtos

ID do Produto

Preço

A-2275

16.75

B-205

4.50

C-789

7.00

C-795

9.75

D-4420

7.25

F-198

5.25

A tabela Clientes contém apenas informações sobre os clientes. Note que cada registo tem um ID único (ID do cliente). O valor de ID do cliente será utilizado para ligar informações do cliente à tabela Encomendas.

Clientes

Código do Cliente

Nome

Rua

Cidade

Distrito

Código Postal

Telemóvel

1001

Contoso, Lda.

2302 Harvard Ave

Belavista

Setúbal

98227

425-555-0222

1003

Adventure Works

Círculo de Columbia 1025

Kirkland

Setúbal

98234

425-555-0185

1005

Café Quatro

7007 Cornell St

Redmond

Setúbal

98199

425-555-0201

A tabela Encomendas contém informações sobre encomendas, vendedores, clientes e produtos. Note que cada registo tem um ID único (ID da encomenda). Algumas das informações nesta tabela precisam de ser divididas numa tabela adicional que contenha detalhes de encomenda para que a tabela Encomendas contenha apenas quatro colunas — o ID de encomenda único, a data de encomenda, o ID do vendedor e o ID do cliente. A tabela mostrada aqui ainda não foi dividida na tabela de Detalhes da Ordem.

Encomendas

ID da Encomenda

Data da Encomenda

SalesPerson ID

ID do Cliente

ID do Produto

Rio Qty

2349

3/4/09

101

1005

C-789

3

2349

3/4/09

101

1005

C-795

6

2350

3/4/09

103

1003

A-2275

2

2350

3/4/09

103

1003

F-198

6

2350

3/4/09

103

1003

B-205

1

2351

3/4/09

105

1001

C-795

6

2352

3/5/09

105

1003

A-2275

2

2352

3/5/09

105

1003

D-4420

3

2353

3/7/09

107

1005

A-2275

6

2353

3/7/09

107

1005

C-789

5

Os detalhes da encomenda, tais como o ID do produto e a quantidade são retirados da tabela encomendas e armazenados numa tabela chamada Detalhes da Encomenda. Lembre-se que há 9 encomendas, por isso faz sentido que haja 9 registos nesta tabela. Note que a tabela Encomendas tem um ID único (ID da encomenda), que será referido na tabela Detalhes da Encomenda.

O desenho final da tabela Encomendas deve parecer o seguinte:

Encomendas

ID da Encomenda

Data da Encomenda

SalesPerson ID

ID do Cliente

2349

3/4/09

101

1005

2350

3/4/09

103

1003

2351

3/4/09

105

1001

2352

3/5/09

105

1003

2353

3/7/09

107

1005

A tabela Detalhes da Encomenda não contém colunas que exijam valores únicos (isto é, não há chave primária), por isso não faz mal que qualquer ou todas as colunas contenham dados "redundantes". No entanto, não devem ser idênticos dois registos nesta tabela (esta regra aplica-se a qualquer tabela numa base de dados). Nesta tabela, devem existir 17 registos — cada um correspondente a um produto numa encomenda individual. Por exemplo, na ordem 2349, três produtos C-789 compreendem uma das duas partes de toda a encomenda.

A tabela Detalhes da Encomenda deve, portanto, parecer o seguinte:

Detalhes da encomenda

ID da Encomenda

ID do Produto

Rio Qty

2349

C-789

3

2349

C-795

6

2350

A-2275

2

2350

F-198

6

2350

B-205

1

2351

C-795

6

2352

A-2275

2

2352

D-4420

3

2353

A-2275

6

2353

C-789

5

Copiar e colar dados do Excel para o Access

Agora que a informação sobre vendedores, clientes, produtos, encomendas e detalhes de encomendas foi dividida em assuntos separados no Excel, pode copiar esses dados diretamente para o Access, onde se tornarão tabelas.

Criar relações entre as tabelas de Acesso e fazer uma consulta

Depois de ter mudado os seus dados para o Access, pode criar relações entre mesas e, em seguida, criar consultas para devolver informações sobre vários assuntos. Por exemplo, pode criar uma consulta que deseja o ID da Encomenda e os nomes dos vendedores para encomendas inseridas entre 3/05/09 e 3/08/09.

Além disso, pode criar formulários e relatórios para facilitar a entrada de dados e análise de vendas.

Precisa de mais ajuda?

Pode sempre perguntar a um especialista na Comunidade Tecnológica do Excel, obter suporte na Comunidade de Respostas ou sugerir uma nova funcionalidade ou melhoria no User Voice do Excel.

Mantenha um passo à frente com o Microsoft 365

Precisa de mais ajuda?

Aumente os seus conhecimentos do Office
Explore as formações
Seja o primeiro a obter novas funcionalidades
Adira ao Office Insider

As informações foram úteis?

Obrigado pelos seus comentários!

Obrigado pelo seu feedback! Parece que poderá ser benéfico reencaminhá-lo para um dos nossos agentes de suporte do Office.

×