Memindahkan data dari Excel ke Access

Artikel ini memperlihatkan cara memindahkan data anda dari Excel ke Access dan mengonversi data anda menjadi tabel relasional sehingga Anda bisa menggunakan Microsoft Excel dan Access bersama-sama. Untuk meringkas, Access paling baik untuk menangkap, menyimpan, membuat kueri, dan berbagi data, dan Excel paling baik untuk menghitung, menganalisis, dan memvisualisasikan data.

Dua artikel, Menggunakan Access atau Excel untuk mengelola data Anda dan 10 alasan teratas untuk menggunakan Access dengan Excel, mendiskusikan program mana yang paling cocok untuk tugas tertentu dan cara menggunakan Excel dan Access bersama-sama untuk membuat solusi praktis.

Saat Anda memindahkan data dari Excel ke Access, ada tiga langkah dasar untuk proses tersebut.

Langkah 1: Mengimpor data dari Excel ke Access

Mengimpor data adalah operasi yang dapat berjalan jauh lebih lancar jika Anda meluangkan waktu untuk menyiapkan dan membersihkan data Anda. Mengimpor data seperti berpindah ke rumah baru. Jika Anda membersihkan dan mengatur harta Anda sebelum Anda pindah, menjadwalkan ke rumah baru Anda jauh lebih mudah.

Bersihkan data Anda sebelum mengimpor

Sebelum anda mengimpor data ke Access, di Excel ada baiknya untuk:

• Mengonversi sel yang berisi data non-atom (yaitu, beberapa nilai dalam satu sel) menjadi beberapa kolom. Misalnya, sel dalam kolom "Keterampilan" yang berisi beberapa nilai keterampilan, seperti "Pemrograman C#," "Pemrograman VBA," dan "Desain web" harus diuraikan untuk memisahkan kolom yang masing-masing berisi hanya satu nilai keterampilan.
• Gunakan perintah TRIM untuk menghapus spasi di depan, akhir, dan beberapa spasi yang disematkan.
• Menghapus karakter non-cetak.
• Menemukan dan memperbaiki kesalahan ejaan dan tanda baca.
• Hapus baris duplikat atau bidang duplikat.
• Pastikan bahwa kolom data tidak berisi format campuran, terutama angka yang diformat sebagai teks atau tanggal yang diformat sebagai angka.

Untuk informasi selengkapnya, lihat topik bantuan Excel berikut ini:

• Sepuluh cara teratas untuk membersihkan data Anda
• Memfilter untuk mendapatkan nilai unik atau menghapus nilai duplikat
• Mengonversi angka yang disimpan sebagai teks menjadi angka
• Mengonversi tanggal yang disimpan sebagai teks menjadi tanggal

Memilih tipe data terbaik saat Anda mengimpor

Selama operasi impor di Access, Anda ingin membuat pilihan yang baik sehingga Anda menerima beberapa (jika ada) kesalahan konversi yang memerlukan intervensi manual. Tabel berikut ini merangkum bagaimana format angka Excel dan tipe data Access dikonversi saat Anda mengimpor data dari Excel ke Access, dan menawarkan beberapa tips tentang tipe data terbaik untuk dipilih dalam Panduan Impor Lembar Bentang.

Format angka Excel	Tipe data Access	Komentar	Praktik terbaik
Teks	Teks, Memo	Tipe data Teks Access menyimpan data alfanumerik hingga 255 karakter. Tipe data Memo Access menyimpan data alfanumerik hingga 65.535 karakter.	Pilih Memo untuk menghindari memotong data apa pun.
Angka, Persentase, Pecahan, Ilmiah	Angka	Access memiliki satu tipe data Angka yang bervariasi berdasarkan properti Ukuran Bidang (Byte, Bilangan Bulat, Bilangan Bulat Panjang, Tunggal, Ganda, Desimal).	Pilih Ganda untuk menghindari kesalahan konversi data.
Tanggal	Tanggal	Access dan Excel sama-sama menggunakan nomor tanggal seri yang sama untuk menyimpan tanggal. Di Access, rentang tanggal lebih besar: dari -657.434 (1 Januari 100 M) hingga 2.958.465 (31 Desember 9999 M). Karena Access tidak mengenali sistem tanggal 1904 (digunakan di Excel untuk Macintosh), Anda perlu mengonversi tanggal baik di Excel atau Access untuk menghindari kebingungan. Untuk informasi selengkapnya, lihat Mengubah sistem tanggal, format, atau interpretasi tahun dua digit dan Mengimpor atau menautkan ke data dalam buku kerja Excel.	Pilih Tanggal.
Waktu	Waktu	Access dan Excel sama-sama menyimpan nilai waktu dengan menggunakan tipe data yang sama.	Pilih Waktu, yang biasanya merupakan default.
Mata Uang, Akuntansi	Mata Uang	Di Access, tipe data Mata Uang menyimpan data sebagai angka 8-byte dengan presisi ke empat tempat desimal, dan digunakan untuk menyimpan data keuangan dan mencegah pembulatan nilai.	Pilih Mata Uang, yang biasanya default.
Boolean	Ya/Tidak	Access menggunakan -1 untuk semua nilai Ya dan 0 untuk semua nilai Tidak, sedangkan Excel menggunakan 1 untuk semua nilai TRUE dan 0 untuk semua nilai FALSE.	Pilih Ya/Tidak, yang secara otomatis mengonversi nilai yang mendasar.
Hyperlink	Hyperlink	Hyperlink di Excel dan Access berisi URL atau alamat Web yang bisa Anda klik dan ikuti.	Pilih Hyperlink, jika tidak Access dapat menggunakan tipe data Teks secara default.

Setelah data berada di Access, Anda bisa menghapus data Excel. Jangan lupa untuk mencadangkan buku kerja Excel asli terlebih dahulu sebelum menghapusnya.

Untuk informasi selengkapnya, lihat topik bantuan Access Mengimpor atau menautkan ke data dalam buku kerja Excel.

Menambahkan data secara otomatis dengan cara yang mudah

Masalah umum yang dimiliki pengguna Excel adalah menambahkan data dengan kolom yang sama ke dalam satu lembar kerja besar. Misalnya, Anda mungkin memiliki solusi pelacakan aset yang dimulai di Excel tetapi sekarang telah berkembang untuk menyertakan file dari banyak grup kerja dan departemen. Data ini mungkin berada di lembar kerja dan buku kerja yang berbeda, atau dalam file teks yang merupakan umpan data dari sistem lain. Tidak ada perintah antarmuka pengguna atau cara mudah untuk menambahkan data yang serupa di Excel.

Solusi terbaik adalah menggunakan Access, tempat Anda dapat dengan mudah mengimpor dan menambahkan data ke dalam satu tabel menggunakan Panduan Impor Lembar Bentang. Selain itu, Anda dapat menambahkan banyak data ke dalam satu tabel. Anda dapat menyimpan operasi impor, menambahkannya sebagai tugas Microsoft Outlook terjadwal, dan bahkan menggunakan makro untuk mengotomatisasi proses.

Langkah 2: Menormalkan data menggunakan Panduan Penganalisis Tabel

Sekilas, menelusuri proses normalisasi data Anda mungkin tampak seperti tugas yang menakutkan. Untungnya, menormalkan tabel di Access adalah proses yang jauh lebih mudah, berkat Panduan Penganalisis Tabel.

1. Seret kolom yang dipilih ke tabel baru dan buat hubungan secara otomatis

2. Gunakan perintah tombol untuk mengganti nama tabel, menambahkan kunci utama, membuat kolom yang sudah ada sebagai kunci utama, dan membatalkan tindakan terakhir

Anda bisa menggunakan panduan ini untuk melakukan hal berikut:

• Mengonversi tabel menjadi sekumpulan tabel yang lebih kecil dan secara otomatis membuat hubungan kunci utama dan asing antar tabel.
• Tambahkan kunci utama ke bidang yang sudah ada yang berisi nilai unik, atau buat bidang ID baru yang menggunakan tipe data AutoNumber.
• Buat hubungan secara otomatis untuk menerapkan integritas referensial dengan pembaruan bertingkat. Penghapusan bertingkat tidak ditambahkan secara otomatis untuk mencegah penghapusan data secara tidak sengaja, tetapi Anda bisa dengan mudah menambahkan penghapusan bertingkat nanti.
• Cari tabel baru untuk data duplikat atau yang berlebihan (seperti pelanggan yang sama dengan dua nomor telepon yang berbeda) dan perbarui ini sesuai keinginan.
• Cadangkan tabel asli dan ganti namanya dengan menambahkan "_OLD" ke namanya. Lalu, buat kueri yang menyusun ulang tabel asli, dengan nama tabel asli sehingga formulir atau laporan yang sudah ada berdasarkan tabel asli akan berfungsi dengan struktur tabel baru.

Untuk informasi selengkapnya, lihat Menormalkan data Anda menggunakan Penganalisis Tabel.

Langkah 3: Menyambungkan ke data Access dari Excel

Setelah data dinormalkan di Access dan kueri atau tabel telah dibuat yang menyusun ulang data asli, ini adalah masalah sederhana menyambungkan ke data Access dari Excel. Data Anda sekarang berada di Access sebagai sumber data eksternal, dan bisa tersambung ke buku kerja melalui koneksi data, yang merupakan wadah informasi yang digunakan untuk menemukan, masuk ke, dan mengakses sumber data eksternal. Informasi koneksi disimpan dalam buku kerja dan juga bisa disimpan dalam file koneksi, seperti file Koneksi Data Office (ODC) (ekstensi nama file.odc) atau file Nama Sumber Data (ekstensi.dsn). Setelah menyambungkan ke data eksternal, Anda juga dapat melakukan refresh (atau memperbarui) buku kerja Excel secara otomatis dari Access setiap kali data diperbarui di Access.

Untuk informasi selengkapnya, lihat Mengimpor data dari sumber data eksternal (Power Query).

Memasukkan data Anda ke Access

Bagian ini memandu Anda melalui fase normalisasi data berikut: Memisahkan nilai dalam kolom Tenaga Penjual dan Alamat ke dalam bagian atom mereka, memisahkan subjek terkait ke dalam tabelnya sendiri, menyalin dan menempelkan tabel tersebut dari Excel ke Access, membuat hubungan kunci antara tabel Access yang baru dibuat, dan membuat dan menjalankan kueri sederhana di Access untuk mengembalikan informasi.

Contoh data dalam formulir yang tidak dinormalkan

Lembar kerja berikut ini berisi nilai non-atomik di kolom Tenaga Penjual dan kolom Alamat. Kedua kolom harus dipisahkan menjadi dua atau beberapa kolom terpisah. Lembar kerja ini juga berisi informasi tentang tenaga penjual, produk, pelanggan, dan pesanan. Informasi ini juga harus dipisahkan lebih lanjut, menurut subjek, menjadi tabel terpisah.

Penjual	ID Pesanan	Tanggal Pemesanan	Product ID	Qty	Harga	Nama Pelanggan	Alamat	Telepon
Li, Yale	2349	3/4/09	C-789	3	$7,00	Fourth Coffee	7007 Cornell St Redmond, WA 98199	425-555-0201
Li, Yale	2349	3/4/09	C-795	6	$9,75	Fourth Coffee	7007 Cornell St Redmond, WA 98199	425-555-0201
Adams, Ellen	2350	3/4/09	A-2275	2	$16,75	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Adams, Ellen	2350	3/4/09	F-198	6	$5,25	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Adams, Ellen	2350	3/4/09	B-205	1	$4,50	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Jim	2351	3/4/09	C-795	6	$9,75	Contoso, Ltd.	2302 Harvard Ave Bellevue, WA 98227	425-555-0222
Hance, Jim	2352	3/5/09	A-2275	2	$16,75	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Jim	2352	3/5/09	D-4420	3	$7,25	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Koch, Reed	2353	3/7/09	A-2275	6	$16,75	Fourth Coffee	7007 Cornell St Redmond, WA 98199	425-555-0201
Koch, Reed	2353	3/7/09	C-789	5	$7,00	Fourth Coffee	7007 Cornell St Redmond, WA 98199	425-555-0201

Informasi di bagian terkecilnya: data atom

Bekerja dengan data dalam contoh ini, Anda bisa menggunakan perintah Teks ke Kolom di Excel untuk memisahkan bagian "atom" sel (seperti alamat jalan, kota, negara bagian, dan kode pos) ke dalam kolom terpisah.

Tabel berikut ini memperlihatkan kolom baru dalam lembar kerja yang sama setelah dipisahkan untuk membuat semua nilai atom. Perhatikan bahwa informasi dalam kolom Tenaga Penjual telah dipisahkan menjadi kolom Nama Belakang dan Nama Depan dan bahwa informasi dalam kolom Alamat telah dipisahkan menjadi kolom Alamat Jalan, Kota, Negara Bagian, dan Kode Pos. Data ini berada dalam "formulir normal pertama."

Nama Belakang	Nama Depan	Alamat Jalan	Kota	Negara Bagian	Kode pos
Li	Yale	2302 Harvard Ave	Bellevue	WA	98227
Adams	Ellen	1025 Lingkaran Columbia	Kediri	WA	98234
Hance	Jim	2302 Harvard Ave	Bellevue	WA	98227
Koch	Reed	7007 Cornell St Redmond	Redmond	WA	98199

Memisahkan data menjadi subjek tertata di Excel

Beberapa tabel data contoh yang mengikuti memperlihatkan informasi yang sama dari lembar kerja Excel setelah dipisahkan menjadi tabel untuk tenaga penjual, produk, pelanggan, dan pesanan. Desain tabel belum final, tetapi berada di jalur yang benar.

Tabel Tenaga Penjual hanya berisi informasi tentang tenaga penjualan. Perhatikan bahwa setiap catatan memiliki ID unik (ID Tenaga Penjual). Nilai ID Tenaga Penjual akan digunakan dalam tabel Pesanan untuk menyambungkan pesanan ke tenaga penjual.

Tenaga Penjual
ID Tenaga Penjual	Nama Belakang	Nama Depan
101	Li	Yale
103	Adams	Ellen
105	Hance	Jim
107	Koch	Reed

Tabel Produk hanya berisi informasi tentang produk. Perhatikan bahwa setiap catatan memiliki ID unik (ID Produk). Nilai ID Produk akan digunakan untuk menyambungkan informasi produk ke tabel Detail Pesanan.

Produk
Product ID	Harga
A-2275	16.75
B-205	4.50
C-789	7.00
C-795	9.75
D-4420	7.25
F-198	5.25

Tabel Pelanggan hanya berisi informasi tentang pelanggan. Perhatikan bahwa setiap catatan memiliki ID unik (ID Pelanggan). Nilai ID Pelanggan akan digunakan untuk menyambungkan informasi pelanggan ke tabel Pesanan.

Pelanggan
ID Pelanggan	Nama	Alamat Jalan	Kota	Negara Bagian	Kode pos	Telepon
1001	Contoso, Ltd.	2302 Harvard Ave	Bellevue	WA	98227	425-555-0222
1003	Adventure Works	1025 Lingkaran Columbia	Kediri	WA	98234	425-555-0185
1005	Fourth Coffee	7007 Cornell St	Redmond	WA	98199	425-555-0201

Tabel Pesanan berisi informasi tentang pesanan, tenaga penjual, pelanggan, dan produk. Perhatikan bahwa setiap catatan memiliki ID unik (ID Pesanan). Beberapa informasi dalam tabel ini perlu dipisahkan menjadi tabel tambahan yang berisi detail pesanan sehingga tabel Pesanan hanya berisi empat kolom — ID pesanan unik, tanggal pesanan, ID tenaga penjual, dan ID pelanggan. Tabel yang diperlihatkan di sini belum dipisahkan menjadi tabel Detail Pesanan.

Pesanan
ID Pesanan	Tanggal Pemesanan	ID Tenaga Penjual	ID pelanggan	Product ID	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

Detail pesanan, seperti ID produk dan jumlah dipindahkan dari tabel Pesanan dan disimpan dalam tabel bernama Detail Pesanan. Ingatlah bahwa ada 9 pesanan, jadi masuk akal bahwa ada 9 rekaman dalam tabel ini. Perhatikan bahwa tabel Pesanan memiliki ID unik (ID Pesanan), yang akan dirujuk dari tabel Detail Pesanan.

Desain akhir tabel Pesanan akan terlihat seperti berikut:

Pesanan
ID Pesanan	Tanggal Pemesanan	ID Tenaga Penjual	ID pelanggan
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

Tabel Detail Pesanan tidak berisi kolom yang memerlukan nilai unik (yaitu, tidak ada kunci utama), sehingga tidak masalah bagi setiap atau semua kolom untuk berisi data "berlebihan". Namun, tidak ada dua rekaman dalam tabel ini yang harus benar-benar identik (aturan ini berlaku untuk tabel apa pun dalam database). Dalam tabel ini, harus ada 17 rekaman — masing-masing terkait dengan produk dalam urutan individual. Misalnya, dalam urutan 2349, tiga produk C-789 terdiri dari salah satu dari dua bagian seluruh pesanan.

Oleh karena itu, tabel Detail Pesanan harus terlihat seperti berikut ini:

Detail Pesanan
ID Pesanan	Product ID	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

Menyalin dan menempelkan data dari Excel ke Access

Setelah informasi tentang tenaga penjual, pelanggan, produk, pesanan, dan detail pesanan telah dipecah menjadi subjek terpisah di Excel, Anda dapat menyalin data tersebut langsung ke Access, tempat data akan menjadi tabel.

Membuat hubungan antara tabel Access dan menjalankan kueri

Setelah memindahkan data ke Access, Anda dapat membuat hubungan antar tabel lalu membuat kueri untuk mengembalikan informasi tentang berbagai subjek. Misalnya, Anda bisa membuat kueri yang mengembalikan ID Pesanan dan nama tenaga penjual untuk pesanan yang dimasukkan antara 3/05/09 dan 3/08/09.

Selain itu, Anda bisa membuat formulir dan laporan untuk membuat entri data dan analisis penjualan lebih mudah.

Perlu bantuan lainnya?

Anda selalu dapat bertanya kepada pakar dalam Komunitas Teknologi Excel atau mendapatkan dukungan di Komunitas.