Memindahkan data dari Excel ke Access
Memindahkan data dari Excel ke Access
Artikel ini memperlihatkan pada Anda cara memindahkan data dari Excel ke Access dan mengonversi data menjadi tabel relasional sehingga Anda bisa menggunakan Microsoft Excel dan Access bersama-sama. Singkatnya, 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 10alasan teratas untuk menggunakan Access dengan Excel , diskusikan program mana yang paling sesuai untuk tugas tertentu dan cara menggunakan Excel dan Access bersama-sama untuk membuat solusi praktis.
Saat memindahkan data dari Excel ke Access, terdapat tiga langkah dasar untuk prosesnya.
Catatan: Untuk informasi tentang pemodelan dan hubungan data di Access, lihat Dasar-dasar desain database.
Langkah 1: Mengimpor data dari Excel ke Access
Mengimpor data adalah operasi yang dapat berjalan lebih lancar jika Anda luangkan waktu untuk mempersiapkan dan membersihkan data Anda. Mengimpor data sama seperti memindahkan ke rumah baru. Jika Anda membersihkan dan menguasai milik Anda sebelum pindah, menyiapkan rumah baru Anda jauh lebih mudah.
Membersihkan data sebelum Anda mengimpor
Sebelum Mengimpor data ke Access, Excel ada baiknya untuk:
Konversi sel yang berisi data non-atomic (data, beberapa nilai dalam satu sel) ke beberapa kolom. Misalnya, sel dalam kolom "Keterampilan" yang berisi beberapa nilai keterampilan, seperti pemrograman "C#," "Pemrograman VBA," dan "Desain web" harus diputus untuk memisahkan kolom yang masing-masing berisi hanya satu nilai keterampilan.
Gunakan perintah TRIM untuk menghapus awalan, akhiran, dan beberapa spasi yang disematkan.
Menghapus karakter non-cetak.
Menemukan dan memperbaiki kesalahan ejaan dan tanda baca.
Menghapus baris duplikat atau bidang duplikat.
Pastikan bahwa kolom data tidak berisi format campuran, khususnya angka yang diformat sebagai teks atau tanggal yang diformat sebagai angka.
Untuk informasi selengkapnya, lihat topik Excel bantuan berikut ini:
Memfilter untuk mendapatkan nilai unik atau menghapus nilai duplikat
Mengonversi tanggal yang disimpan sebagai teks menjadi tanggal
Catatan: Jika kebutuhan pembersihan data anda kompleks, atau Anda tidak memiliki waktu atau sumber daya untuk mengotomatisasi proses sendiri, Anda mungkin mempertimbangkan menggunakan vendor pihak ketiga. Untuk informasi selengkapnya, cari "perangkat lunak pembersihan data" atau "kualitas data" oleh mesin pencarian favorit Anda di browser Web.
Memilih tipe data terbaik saat Anda mengimpor
Selama operasi impor di Access, Anda ingin membuat pilihan yang baik sehingga Anda menerima sedikit kesalahan konversi (jika ada) yang memerlukan intervensi manual. Tabel berikut ini merangkum Excel format angka 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.
Excel format angka | 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 pemotongan data. |
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 keduanya menggunakan nomor seri yang sama untuk menyimpan tanggal. Di Access, rentang tanggal lebih besar: dari -657.434 (1 Januari 100 M) sampai 2.958.465 (31 Desember 9999 M). Karena Access tidak mengenali sistem tanggal 1904 (digunakan di Excel untuk Macintosh), Anda harus 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 di dalam Excel kerja . | Pilih Tanggal. |
Waktu | Waktu | Access dan Excel keduanya menyimpan nilai waktu menggunakan tipe data yang sama. | Pilih Waktu, yang biasanya merupakan default. |
Currency, Accounting | Mata Uang | Di Access, tipe data Mata Uang menyimpan data sebagai angka 8-byte dengan presisi hingga empat tempat desimal, dan digunakan untuk menyimpan data keuangan dan mencegah membulatkan nilai. | Pilih MataUang, yang biasanya merupakan default. |
Boolean | Ya/Tidak | Access menggunakan -1 untuk semua nilai Ya dan 0 untuk semua nilai Tidak, sementara Excel menggunakan 1 untuk semua nilai TRUE dan 0 untuk semua nilai FALSE. | Pilih Ya/Tidak, yangsecara otomatis mengonversi nilai yang mendasarinya. |
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 dapat menghapus Excel data. Jangan lupa untuk mencadangkan buku kerja asli Excel kerja terlebih dahulu sebelum menghapusnya.
Untuk informasi selengkapnya, lihat topik bantuan Access Mengimpor atau menautkan ke data dalam buku Excel kerja.
Menambahkan data secara otomatis dengan mudah
Masalah umum yang Excel pengguna tambahkan data dengan kolom yang sama ke dalam satu lembar kerja yang besar. Misalnya, Anda mungkin memiliki solusi pelacakan aset yang dimulai pada tahun Excel tetapi sekarang telah berkembang untuk menyertakan file dari banyak grup kerja dan departemen. Data ini mungkin berada di lembar kerja dan buku kerja berbeda, atau di 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 dengan menggunakan Access, di mana Anda bisa dengan mudah mengimpor dan menambahkan data ke dalam satu tabel dengan menggunakan Panduan Impor Lembar Bentang. Selanjutnya, Anda bisa menambahkan banyak data ke dalam satu tabel. Anda bisa menyimpan operasi impor, menambahkannya sebagai tugas Microsoft Outlook terjadwal, dan bahkan menggunakan makro untuk mengotomatiskan proses.
Langkah 2: Menormalkan data menggunakan Panduan Penganalisis Tabel
Sekilas, menormalkan proses normalisasi data mungkin tampak menjadi tugas yang berat. Untungnya, normalisasi tabel di Access merupakan 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, menjadikan kolom yang ada sebagai kunci utama, dan membatalkan tindakan terakhir
Anda bisa menggunakan panduan ini untuk melakukan hal berikut:
Konversi tabel menjadi kumpulan tabel yang lebih kecil dan secara otomatis membuat hubungan kunci primer 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.
Secara otomatis membuat hubungan untuk menerapkan integritas referensial dengan pembaruan kaskad. Penghapusan terhubung tidak secara otomatis ditambahkan untuk mencegah penghapusan data secara tidak sengaja, tapi Anda bisa dengan mudah menambahkan penghapusan kaskakad nanti.
Cari tabel baru untuk data berulang atau duplikat (seperti pelanggan yang sama dengan dua nomor telepon yang berbeda) dan perbarui sesuai keinginan.
Mencadangkan tabel asli dan mengganti namanya dengan menambahkan "_OLD" ke namanya. Lalu, Anda membuat kueri yang merekonstruksi tabel asli, dengan nama tabel asli sehingga formulir atau laporan apa pun yang sudah ada berdasarkan tabel asli akan bekerja dengan struktur tabel baru.
Untuk informasi selengkapnya, lihat Menormalkan data Anda menggunakan Penganalisis Tabel.
Langkah 3: Koneksi mengakses data dari Excel
Setelah data dinormalkan di Access dan kueri atau tabel telah dibuat, yang merekonstruksi data asli, hal yang menjadi masalah mudah menyambungkan ke data Access dari Excel. Sekarang data Anda berada di Access sebagai sumber data eksternal, dan sehingga 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 di buku kerja dan juga bisa disimpan di file koneksi, seperti file Koneksi Data Office (ODC, Data Connection) (ekstensi nama file .odc) atau file Nama Sumber Data (ekstensi .dsn). Setelah menyambungkan ke data eksternal, Anda juga bisa secara otomatis melakukan refresh (atau memperbarui) Excel kerja Anda dari Access setiap kali data diperbarui di Access.
Untuk informasi selengkapnya, lihat Mengimpor data dari sumber data eksternal (Power Query).
Dapatkan data Anda ke Access
Bagian ini memandu Anda melalui fase normalisasi data berikut: Memisahkan nilai dalam kolom Tenaga Penjual dan Alamat ke dalam bagian paling intinya, memisahkan subjek terkait ke dalam tabel mereka sendiri, menyalin dan menempelkan tabel tersebut dari Excel ke Access, membuat hubungan utama antara tabel Access yang baru dibuat, serta membuat dan menjalankan kueri sederhana di Access untuk mengembalikan informasi.
Contoh data dalam bentuk yang tidak dinormalkan
Lembar kerja berikut berisi nilai non-atomic dalam kolom Tenaga Penjual dan kolom Alamat. Kedua kolom harus dipisahkan menjadi dua kolom atau lebih terpisah. Lembar kerja ini juga berisi informasi tentang tenaga penjual, produk, pelanggan, dan pesanan. Informasi ini juga harus dipisahkan lebih lanjut, menurut subjek, ke dalam tabel terpisah.
Tenaga penjual | ID Pesanan | Tanggal Pemesanan | Product ID | Jml | Harga | Nama Pelanggan | Alamat | Telepon |
|---|---|---|---|---|---|---|---|---|
Li, Yale | 2349 | 3/4/09 | C-789 | 3 | $7,00 | Fourth Coffee | 7007 Gdansk St Redmond, WA 98199 | 425-555-0201 |
Li, Yale | 2349 | 3/4/09 | C-795 | 6 | $9,75 | Fourth Coffee | 7007 Gdansk 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 Gdansk St Redmond, WA 98199 | 425-555-0201 |
Koch, Reed | 2353 | 3/7/09 | C-789 | 5 | $7,00 | Fourth Coffee | 7007 Gdansk St Redmond, WA 98199 | 425-555-0201 |
Informasi dalam bagian terkecilnya: data atom
Bekerja dengan data dalam contoh ini, Anda dapat menggunakan perintah Teks ke Kolom di Excel untuk memisahkan bagian "atomic" sel (seperti alamat, kota, negara bagian, dan kode pos) ke dalam kolom-kolom diskret.
Tabel berikut ini memperlihatkan kolom baru dalam lembar kerja yang sama setelah terpisah untuk membuat semua nilai atomic. Perhatikan bahwa informasi dalam kolom Tenaga Penjual telah dipisahkan ke dalam Nama Belakang dan kolom Nama Depan dan bahwa informasi di kolom Alamat telah dipisahkan ke dalam kolom Alamat Jalan, Kota, Negara Bagian, dan Kode Pos. Data ini berada dalam "bentuk normal pertama."
Nama Belakang | Nama Depan |
| Alamat Jalan | Kota | Negara Bagian | Kode pos |
|---|---|---|---|---|---|---|
Li | Yale | 2302 Harvard Ave | Bellevue | WA | 98227 | |
Adams | Ellen | 1025 Columbia Circle | Kediri | WA | 98234 | |
Hance | Jim | 2302 Harvard Ave | Bellevue | WA | 98227 | |
Koch | Reed | 7007 St Redmond | Redmond | WA | 98199 |
Memecah data menjadi subjek yang tertata di Excel
Beberapa tabel dari contoh data berikut ini memperlihatkan informasi yang sama dari lembar kerja Excel setelah dibagi 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 personil 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 Columbia Circle | Kediri | WA | 98234 | 425-555-0185 |
1005 | Fourth Coffee | 7007B 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 harus 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 ke dalam tabel Detail Pesanan.
Pesanan | |||||
|---|---|---|---|---|---|
ID Pesanan | Tanggal Pemesanan | SalesPerson ID | ID pelanggan | Product ID | Jml |
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 kuantitas dipindahkan dari tabel Pesanan dan disimpan di tabel bernama Detail Pesanan. Ingat 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 ini:
Pesanan | |||
|---|---|---|---|
ID Pesanan | Tanggal Pemesanan | SalesPerson ID | 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 (artinya, tidak ada kunci utama), jadi tidak masalah untuk setiap atau semua kolom berisi data "berlebihan". Namun, tidak ada dua rekaman dalam tabel ini yang benar-benar identik (aturan ini berlaku untuk tabel apa pun dalam database). Dalam tabel ini, harus ada 17 catatan, masing-masing terkait dengan produk dalam satu pesanan. Misalnya, dalam urutan 2349, tiga produk C-789 terdiri dari salah satu dari dua bagian dari seluruh pesanan.
Oleh karena itu, tabel Detail Pesanan harus terlihat seperti berikut ini:
Detail Pesanan | ||
|---|---|---|
ID Pesanan | Product ID | Jml |
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
Sekarang karena informasi tentang tenaga penjual, pelanggan, produk, pesanan, dan detail pesanan telah dibagi menjadi subjek yang berbeda dalam Excel, Anda dapat menyalin data tersebut secara 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.
Comments
Post a Comment