Bagaimana cara mengimpor data dari odbc ke excel?

Objektif

Saya memiliki data yang disimpan dalam database yang ingin saya impor menggunakan sumber data ODBC

Resolusi

1. Jika Anda mengimpor dari Excel atau Access, pastikan perangkat lunak Microsoft, Leapfrog, dan versi driver ODBC Anda semuanya kompatibel. Misalnya, jika Anda menggunakan Microsoft office 32-bit, semua perangkat lunak lainnya juga harus 32-bit. Contoh berikut adalah untuk mengimport Excel. file xls. Unggul. File xlsx juga dapat diimpor, tetapi pastikan untuk memilih driver yang sesuai dalam petunjuk di bawah ini. Catatan. Agar driver ODBC mengenali semua tabel Anda (kerah, survei, interval), nama setiap lembar kerja harus dimulai dengan akhiran yang sama. Sebagai contoh. M-kerah, M-Assay, M-Geologi
2. Siapkan driver ODBC. Di Windows 7, Anda dapat menemukan Administrator Sumber Data ODBC 32-bit di sini. (C. ) > Windows > SysWOW64 > odbcad32. exe. Klik dua kali file ini untuk membukanya
   
   
3. Untuk menyiapkan driver ODBC Excel, pilih 'Tambah' untuk membuat Sumber Data Pengguna baru. Gulir ke bawah untuk menemukan driver yang tepat (dalam hal ini adalah Microsoft Excel Driver for. xls,. xlsx, dll. )
   
   
4. Di jendela berikutnya, beri nama sumber data Anda (mis. Proyek Leapfrog), berikan deskripsi (jika diinginkan), dan ubah versi Excel, jika perlu. Pilih database dengan mengklik "Pilih Buku Kerja" dan temukan. file .xls
   
   
5. Jika Anda memilih untuk menambahkan sumber data baru, Anda sekarang akan melihatnya di daftar sumber data. Untuk mengonfigurasi satu, pilih, dan klik "Konfigurasi". Klik OK untuk keluar
   
   
6. Sekarang buka Leapfrog. Anda harus membuka Geo 32-bit jika Anda menggunakan driver ODBC 32-bit, dan Excel 32-bit. Buat proyek baru. Impor lubang bor melalui ODBC
   
   
7. Sumber data yang digunakan adalah "File database". Di jendela yang muncul, lihat semua jenis file. Cari dan pilih. file .xls. Klik Ok untuk kembali ke jendela sumber data ODBC. Ubah tipe data menjadi File Excel. Klik Oke
   
   
8. Pilih tabel kerah, survei, dan interval dari dropdown. Dengan asumsi setiap nama lembar kerja dimulai dengan awalan yang sama, Anda akan melihatnya dalam daftar. Klik OK dan lanjutkan mengimpor ke Geo seperti biasa
   
   

Catatan

• Leapfrog sekarang hanya tersedia dalam versi 64-bit. MS Office dan driver masing-masing harus dalam 64-bit

Dapatkan lebih banyak dukungan produk

Terkait

Dengan ODBC, Anda dapat meringkas, dan memilih data yang Anda perlukan saja, di buku kerja Excel sebelum mengimpornya ke SQL Server. Anda dapat menggabungkan data dari berbagai area atau lembar kerja. Anda bahkan bisa mendapatkan data dari hasil pernyataan SQL Server SELECT ke dalam spreadsheet Excel. Phil Factor menunjukkan caranya, dan memperingatkan beberapa jebakan

Mengapa Menggunakan ODBC?

Cukup mudah untuk memasukkan data dari Excel ke dalam SQL Server, atau sebaliknya, dari database ODBC lainnya ke yang lain, menggunakan PowerShell. Arah yang paling penting tentu saja dari Excel ke SQL Server. Ini lebih cepat daripada mengotomatiskan Excel dan Anda dapat melakukannya tanpa memerlukan salinan Excel. Ini juga lebih rapi daripada SSIS, dan lebih serbaguna. Namun, yang paling penting adalah Anda dapat mengagregasi sebelum mengirim data. Dimungkinkan untuk melakukan banyak pemfilteran dan agregasi data sebelum masuk ke SQL Server, karena Anda dapat mengubah Buku Kerja Excel yang ada menjadi database relasional orang miskin, atau bahkan membuatnya. Artikel ini akan bertujuan untuk menunjukkan bagaimana hal ini dilakukan

Saya selalu merasa sedikit canggung berbicara tentang ODBC. Ini adalah teknologi Sekali dan Masa Depan, dikembangkan sebelum waktunya, tetapi sekarang menunjukkan nilainya untuk memproses data dalam jumlah besar, terlepas dari keanehannya, dokumentasi yang buruk, dan dukungan yang kurang bagus. Jika Anda menggunakan driver ODBC, maka buku kerja Excel Anda menjadi basis data relasional berbasis SQL kecil. Lembar kerja, atau area di dalam lembar kerja, menjadi tabel. Ada beberapa fitur yang hilang, tentu saja, tetapi Anda dapat melakukan penggabungan antar tabel, memfilter baris sesuai selera, melakukan agregasi, dan beberapa manipulasi string. Ini berarti bahwa Anda perlu menarik jauh lebih sedikit data ke dalam SQL karena Anda dapat melakukan banyak pemilihan dan pra-pemrosesan sebelum data mendekati SQL server. Jika, misalnya, Anda hanya memerlukan total, hitungan, dan varian dari pembacaan sehari, lalu mengapa Anda ingin mengimpor lebih dari angka agregat tersebut?

Saya juga lebih suka menggunakan ODBC dan pembaca data berurutan untuk membaca data dari Excel, atau sumber ODBC lainnya, karena cepat; . Ketika Anda memiliki banyak spreadsheet besar untuk disisipkan sebagai tugas, maka kecepatan itu penting

Driver ODBC Excel (ACE)

ODBC dipahami sebagai cara untuk membuatnya mudah terhubung ke sumber data tertentu seperti database relasional, file teks, dokumen data (e. g. XML), data berbasis web atau spreadsheet

Saat ini, ODBC for Access dan Excel yang canggih adalah Microsoft Access Database Engine 2010 Redistributable yang dapat diunduh di sini. Ini termasuk driver OLEDB yang lebih populer yang juga berjalan dengan baik di PowerShell. Driver ini memungkinkan Anda untuk mengakses berbagai file data melalui SQL seolah-olah itu adalah database relasional. Format mencakup Access, CSV,  dibatasi, DBase, dan Excel

Untuk mengembangkan pada komputer desktop 64-bit tujuan umum, Anda kemungkinan besar akan mengalami kekacauan Microsoft yang sangat konyol. Microsoft  menyarankan Anda menginstal Office 2010 versi 32-bit, bahkan pada mesin 64-bit, karena banyak Add-in Office umum tidak berjalan di lingkungan Office 64-bit. Nasihat ini telah menjadi 'praktik terbaik'. Jika Anda menggunakan PowerShell 64-bit, seperti kebanyakan dari kita, maka Anda perlu menggunakan driver versi 64-bit. Jika Anda hanya memiliki Office 32-bit di komputer Anda, perangkat tersebut sudah memiliki driver 32-bit, yang tidak akan terlihat oleh PowerShell 64-bit, dan tidak akan berfungsi. Anda tidak dapat menginstal driver 64 bit ketika Anda sudah memiliki driver 32-bit dan menurut saya Anda tidak dapat memperoleh sesuatu yang baik dengan menghapus instalan driver 32-bit. Tidak. Ketiganya (atau empat jika Anda menyertakan Visual Studio) harus 64 bit. Saya menyimpulkan bahwa salah satu jalan keluar dari Catch 22 ini adalah menginstal driver Office 2010 ODBC/OleDB 64-bit terlebih dahulu dan setelah itu Office (32-bit), tetapi ada perbaikan yang lebih baik yang melibatkan mengutak-atik registri. Lihat ini untuk cerita lengkap yang membuat frustrasi

Driver ODBC Excel di ACE bekerja dengan format spreadsheet Excel terbaru hingga 2010 serta yang lama. Saya menduga versi terbaru akan berfungsi dengan Office 2013, meskipun saya belum mencobanya

Pengemudi ini  berharga karena fleksibilitas yang diberikannya. Ini benar-benar mengeksekusi ODBC SQL, yang merupakan bagian dari SQL92, sehingga Anda dapat memasukkan alias kolom, mengubah urutan kolom, dan memfilter baris yang tidak ingin Anda impor. Akibatnya, ini memberi Anda tabel SQL yang dapat diberi nama rentang, jika itu adalah lembar kerja yang sudah ada yang telah Anda tambahkan rentang bernama ke

1

Pilih * dari MyNamedRange

Lebih umum, Anda dapat menentukan dengan nama lembar kerja yang dibatasi diikuti dengan rentang, rentang menjadi spesifikasi area lembar kerja yang cukup untuk memungkinkan driver menemukan data yang Anda inginkan. Jika Anda mengabaikan spesifikasi rentang seluruhnya, seluruh lembar kerja menjadi tabel

1

Pilih * dari [Lembar Kerjaku$]

Jika, misalnya, Anda menginginkan data dalam rentang dari C3 hingga L8, Anda akan menggunakan pernyataan tersebut

1

  Pilih * dari [MyWorksheet$C3:M8]

Di ODBC, jika Anda menentukan, katakanlah, baris 8 sebagai akhir tabel, Anda hanya dapat memilih baris hingga baris 8, bahkan jika Anda telah menyisipkan lebih banyak baris di luar batas tersebut, seperti yang diizinkan ODBC. Jika Anda menggunakan beberapa ragam, seperti mesin database MDAC 'JET' lama, Anda tidak dapat menambahkan baris baru di luar batas rentang yang ditentukan, jika tidak, Anda akan mendapatkan Exception: "Cannot expand named range" message

Jika Anda ingin menentukan tabel Anda berada di antara kolom C dan L, mulai dari baris 3 yang akan Anda gunakan

1

  Pilih * dari [NameOfExcelSheet$C3:M]

Jika Anda melakukan ini, maka tidak ada batasan panjang tabel sehingga Anda dapat menyisipkan baris sebanyak yang Anda suka. Penyedia ODBC menambahkan baris baru ke baris yang sudah ada di area yang ditentukan sesuai ruang yang memungkinkan

Tali koneksi yang ditakuti

Sekarang, sebelum kita mulai melakukan hal-hal menarik dengan driver ACE, saya harus menjelaskan sedikit tentang rangkaian koneksinya. Ini berisi spesifikasi driver ODBC yang ingin Anda gunakan, dan pengaturan yang ingin Anda kirimkan ke driver

Mengabaikan, untuk saat ini, pengaturan properti yang diperluas, Untuk data Microsoft Office Access, setel Connection String ke

1

" Pengemudi={Microsoft Access Driver (*.mdb, *. accdb)};DBQ= MyPath/MyFile"

Untuk data Excel, gunakan

1

" Pengemudi={Microsoft Excel Driver (*.xls, *. xlsx, *. xlsm, *. xlsb)};DBQ=MyPath/MyFile"

Untuk data dBASE, gunakan

1

" Pengemudi={Microsoft Access dBASE Driver {*.dbf, *. ndx, *. mdx)};DBQ=MyPath/MyFile"

Untuk data teks, gunakan

1

" Pengemudi={Microsoft Access Text Driver {*.txt, *. csv);DBQ=MyPath"

Tetapi Anda mungkin menginginkan beberapa properti yang diperluas untuk pengaturan untuk menambahkan beberapa detail tentang cara penyedia ODBC menangani koneksi khusus ini. Karena default dapat diubah secara global di registri, lebih baik menentukan properti yang diperluas ini daripada mengandalkan default

Properti yang diperluas ini hanya relevan untuk driver yang Anda gunakan. Mereka tidak selalu dapat diandalkan dan didokumentasikan dengan buruk oleh Microsoft. Saya hanya akan menyebutkan hal-hal yang penting

Pengemudi perlu mengetahui apakah baris pertama tabel berisi nama kolom. “HDR=Ya;” . Ini sebenarnya hanya akan menggunakan 64 karakter pertama dari header. “HDR=Tidak;”

ODBC Excel tidak menyimpan definisi skema terperinci dari tabel. (sebaliknya, driver Teks dan Akses) Driver ODBC Excel akan mencoba memahami data yang ditemukannya dengan mengujinya untuk melihat jenis data apa yang dapat digunakan untuk hasilnya. Itu dilakukan dengan menguji sejumlah baris sebelum melakukan impor, dan Anda dapat menentukan berapa banyak baris yang diuji sebelum memutuskan tipe data kolom dengan menggunakan MaxScanRows di properti yang diperluas. Secara default nilai ini adalah 8. Anda dapat menentukan nilai apa pun dari 1 – 16 untuk 1 hingga 16 baris. Anda juga dapat membuat nilai menjadi 0 sehingga mencari semua baris yang ada sebelum menentukan tipe data, tetapi ini memperlambat semuanya

Ini bagus untuk tabel relasional tetapi Excel sering memiliki tipe campuran di kolom Penyedia ODBC akan mencoba mengembalikan data dari tipe mayoritas, tetapi mengembalikan nilai NULL untuk sisanya yang tidak akan dikonversi. Jika kedua jenis tercampur rata dalam kolom, penyedia memilih numerik daripada teks, dan Anda kehilangan semua teks. Juga, itu akan menilai panjang tipe data karakter di kolom dari baris pertama dan jika baris pertama kurang dari 255 karakter, itu akan memotong semua data berikutnya menjadi 255 karakter bahkan jika nilai sel di bawah lebih panjang

Dengan mengatur Mode Impor (IMEX=1). Anda dapat memaksa data campuran untuk dikonversi menjadi teks, tetapi hanya jika menemukan nilai campuran pada baris yang diperiksa

Anda juga dapat membuka buku kerja Excel dalam mode baca-saja dengan menentukan ReadOnly=true; . Namun, ini akan mengunci seluruh buku kerja dari akses hingga Anda menutup koneksi

Mari kita coba

Agar Anda dapat membuktikan semua ini pada diri Anda sendiri, saya telah menyediakan buku kerja Excel yang mewakili database PUBS lama yang dulunya didistribusikan dengan SQL Server dan Sybase. Ini berarti Anda dapat menggunakan SQL dari contoh lama yang menggunakan PUBS dan melihat apa yang berhasil. Yang perlu Anda lakukan adalah sedikit mengonversi versi SQL Server dengan sedikit mengubah nama tabel untuk memberi tahu driver bahwa Anda menginginkan seluruh lembar kerja dari nama itu ($ adalah pemisah antara nama lembar kerja dan spesifikasi rentang)

Jadi mari kita buat test-rig yang sangat sederhana untuk mencoba berbagai hal di PowerShell. Berhati-hatilah, saya telah menyiapkan ini dalam mode baca-tulis sehingga akan memperbarui spreadsheet Anda dalam beberapa keadaan (CUD). Untuk memainkannya, Anda harus mengunduh database PUBS versi Excel saya dan  mengubah jalur ke file excel

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

set-psdebug -strict

$ErrorActionPreference = "stop"

$ExcelFilePath='MyPath\pubs. xlsx' #jalur lengkap buku kerja excel

jika (. (Jalur Uji $ExcelFilePath))

{

Write-Error "Tidak dapat menemukan '$($ExcelFilePath)'. Maaf, tidak dapat melanjutkan karena ini"

KELUAR

}

coba {

$Connection = Objek Baru sistem. data. odbc. odbckoneksi

$Connection. ConnectionString = 'Driver={Microsoft Excel Driver (*. xls, *. xlsx, *. xlsm, *. xlsb)};DBQ='+$ExcelFilePath+';

$Connection. Buka()

}

menangkap

{

$ex = $_. Pengecualian

Write-Error "saat membuka koneksi ke $ExcelFilePath. Maaf, tidak dapat melanjutkan karena ini"

KELUAR

}

coba {

$Query = Objek Baru sistem. data. odbc. odbcommand

$Query. Koneksi = $koneksi

$Query. CommandText = @'

PILIH judul, SUM(qty) SEBAGAI penjualan,

COUNT(*) SEBAGAI pesanan

DARI [judul$] t

INNER JOIN [sales$] ON t. title_id=s. judul_id

DI MANA judul seperti '%?'

KELOMPOK DENGAN judul

ORDER BY SUM(qty) DESC

'@

$Reader = $Query. ExecuteReader([Sistem. Data. Perilaku Perintah]. SequentialAccess) #dapatkan pembaca data dan dapatkan hasilnya dalam sekali teguk

}

menangkap

{

$ex = $_. Pengecualian

Write-Error "saat mengeksekusi kueri '$($Query. CommandText)' $ex. Pesan Maaf, tetapi kami tidak dapat melanjutkan karena hal ini. "

$Pembaca. Tutup()

$Connection. Tutup()

Keluar;

}

Mencoba

{

$Penghitung = $Pembaca. FieldCount #dapatkan sekali saja

$result=@() #initialise the empty array of rows

sementara ($Pembaca. Baca()) {

$Tuple = Objek-Baru -TypeName ' . Pengelolaan. Otomatisasi. Objek PSO'

untuk setiap ($i di (0.. ($Penghitung - 1 ))) {

Tambah-Anggota `

-InputObject $Tuple `

-MemberType PropertiCatatan `

-Nama $Pembaca. GetName($i) `

-Nilai $Pembaca. GetValue($i). ToString()

}

$Result+=$Tuple

}

$result . Format-Tabel

}

menangkap

{

$ex = $_. Pengecualian

Write-Error "saat membaca data dari tabel data. $mis. Pesan"

}

$Pembaca. Tutup()

$Connection. Tutup()

Semua ini berhasil

Sambungan batin

1

2

3

PILIH logo, pr_info, pub_name, city, state, country

DARI [pub_info$] pif INNER JOIN [publishers$] p

AKTIF p. pub_id=pif. pub_id

Sambungan luar kiri atau kanan

1

2

3

PILIH   judul, stor_id, ord_num, qty,ord_date

  DARI [judul$] t LEFT OUTER JOIN  [sales$] s

    AKTIF t. title_id=s. title_id

Ekspresi menggunakan kolom

1

2

3

4

PILIH   fname+' '+ minit+' '+lname AS name, job_desc

    DARI [pekerjaan$] d

    INNER GABUNG   [employee$] e

    AKTIF d. job_id=e. job_id

Ekspresi GROUP BY sederhana

1

PILIH COUNT(*) FROM [sales$] GROUP BY stor_ID

Agregasi yang lebih kompleks dengan klausa ORDER BY dan klausa WHERE

1

2

3

4

5

6

7

PILIH   judul, SUM(qty) AS sales,

        COUNT(*) AS orders

    DARI [judul$] t

      INNER GABUNG   [sales$] s ON t.title_id=s. title_id

      DIMANA judul suka '%?'

    GROUP OLEH judul

    PESAN OLEH JUMLAH(qty) DESC

Fungsi string

1

PILIH   judul, kiri(notes,20)+'...' sebagai [catatan] FROM [titles$]

UNI dan UNI SEMUA

1

PILIH    au_fname DARI [authors$] UNION ALL SELECT lname FROM [employee$]

Orang bisa terus dan terus; . Hal yang sama berlaku untuk driver TEXT untuk OLEDB. Itu akan melakukan semua ini juga. Agar sesuai dengan sintaks minimum untuk ODBC, driver harus dapat mengeksekusi CREATE TABLE, DELETE FROM (searched), DROP TABLE, INSERT INTO, SELECT, SELECT DISTINCT, dan UPDATE (searched). Pernyataan SELECT dapat memiliki klausa WHERE dan ORDER BY. ACE melakukan sedikit lebih baik dari ini, karena bahkan driver teks memungkinkan SELECT INTO, dan pernyataan SELECT memungkinkan GROUP BY dan HAVING.  

Membuat spreadsheet

Anda tentu saja dapat menggunakan driver ODBC untuk membuat spreadsheet Excel dan menulis data ke dalamnya. Ini adalah demo kerja paling sederhana yang bisa saya tulis tanpa tersipu. Berhati-hatilah untuk memastikan bahwa spreadsheet tidak ada karena inti dari demo adalah untuk membuktikan kepada Anda bahwa itu dapat membuat seluruh buku kerja spreadsheet dengan beberapa lembar kerja

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

$ExcelFilePath='MyPath\Baru. xlsx' #jalur lengkap buku kerja excel

$Header= $true # kami ingin baris pertama Anda menjadi tajuk kolom

coba {

$Connection = Objek Baru sistem. data. odbc. odbckoneksi

$TheConnectionString = 'Driver={Microsoft Excel Driver (*. xls, *. xlsx, *. xlsm, *. xlsb)};DBQ='+$ExcelFilePath+';Mode+"$(if ($Header){'YES'}else{'NO'})"+'"'

$Connection. ConnectionString=$TheConnectionString

$Connection. Buka()

}

menangkap

{

$ex = $_. Pengecualian

Write-Error "saat membuka koneksi ke $ExcelFilePath menggunakan '$($TheConnectionString)'. $mis. Pesan"

}

$perintahku=$koneksi. BuatPerintah()

$MyCommand. CommandText="buat tabel MyTable (varchar Kolom Saya, kolom Kolom Lainnya)"

jika ($perintahku. JalankanNonQuery() -eq -1)

{

$MyCommand. CommandText="insert into MyTable (MyColumn, MyOtherColumn) pilih 'myfirstRowCol','myFirstRowCol2'"

$baris=$perintahku. JalankanNonQuery()

"$rows baris dimasukkan ke dalam lembar kerja MyTable"

}

$koneksi. Tutup()

Perhatikan bahwa saya tidak dapat membuat tabel dan melakukan penyisipan dalam satu kumpulan sebagai perintah. Satu pernyataan hanya dapat digunakan dalam teks perintah

Menjelajahi metadata Excel Anda

Anda dapat mengetahui tipe data apa yang tersedia untuk sumber ODBC apa pun, dengan menggunakan metode _______________

1

$Jenis Data=$koneksi. GetSchema('DATATYPES').NamaJenis

Yang dengan koneksi saya hanya memberikan tipe data _______________. Lebih bermanfaat adalah

1

$tables=$koneksi. GetSchema('TABEL').Table_Name

… yang memberi Anda daftar lembar kerja yang tersedia. Daftar lengkapnya, jika Anda ingin mengintipnya, adalah

1

2

3

4

5

6

7

8

$koneksi. GetSchema('TABEL')

$connection. GetSchema('DATATYPES')

$connection. GetSchema('DataSourceInformation')

$connection. GetSchema('Restrictions')

$connection. GetSchema('ReservedWords')

$connection. GetSchema('Columns')

$connection. GetSchema('Indexes')

$connection. GetSchema('Views')

Hmm. This is beginning to look a bit more like a database. With the Columns MetadataCollection, you can find out as much as you’d ever want to know about the data that is available in the spreadsheet so if you want to read all the worksheets straight into SQL Server, this is a wide-open goal

Creating Worksheets

Going back to the PUBS Excel database, let’s create a peoples table and populate it with both authors and salespeople. This has to be done in three gulps since the driver seems to dislike the idea of doing a batch, and it kicks when I try to UNION the two results

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

$ExcelFilePath='C. \Users\Administrator\Documents\POSHScripts\Pubs. xlsx' #the full path of the excel workbook

$Header= $true # true if you want your first row to be read as column headers

jika (. (Jalur Uji $ExcelFilePath))

    {

    Write-Error "Can't find '$($ExcelFilePath)'.   Sorry, can't proceed because of this"

    exit

    }

coba {

$Connection = Objek Baru sistem. data. odbc. odbckoneksi

$TheConnectionString = 'Driver={Microsoft Excel Driver (*. xls, *. xlsx, *. xlsm, *. xlsb)};DBQ='+$ExcelFilePath+';Mode+"$(if ($Header){'YES'}else{'NO'})"+'"'

$Connection. ConnectionString=$TheConnectionString

$Connection. Buka()

}

menangkap

{

    $ex = $_. Exception

    Write-Error "whilst opening connection to  $ExcelFilePath using '$($TheConnectionString)' . $ex. Message"

}

$perintahku=$koneksi. BuatPerintah()

$MyCommand. CommandText=@"

BUAT TABEL orang

(Orang varchar)

"@

jika ($perintahku. JalankanNonQuery() -eq -1)

    {$MyCommand. CommandText=@"

MASUKKAN ke dalam [orang$](orang)

PILIH nama DARI [karyawan$]

"@

    $baris=$perintahku. JalankanNonQuery()

    $MyCommand. CommandText=@"

MASUKKAN ke dalam [orang$](orang)

PILIH  au_fname DARI [penulis$]

"@

    $baris=$baris+$Mycommand.JalankanNonQuery()

    }

"$baris baris dimasukkan ke dalam tabel"

$koneksi. Tutup()

Anda akan menemukan bahwa Anda dapat UPDATE, INSERT dan DELETEdata sangat bahagia dengan cara ini. Jika Anda menghubungkan spreadsheet ke database SQL Server, Anda dapat bersenang-senang menyalin seluruh database ke dalam spreadsheet, dan kembali lagi. Robyn dan saya menunjukkan bagaimana melakukannya di sini

Masalahnya ada di Buku Kerja yang Anda buat. Apakah Anda menamainya XLS atau XSLX, itu menghasilkan spreadsheet XLSX, dalam bentuk zip Office Open XML terbaru. Masalahnya adalah, dengan versi driver saya,  saya hanya bisa membuat  Excel membacanya dengan jenis file XLS, karena dikatakan ada kesalahan jika Anda mencoba membukanya sebagai. berkas XLSX. Saya curiga driver ODBC belum diuji dengan baik oleh Microsoft

Mendapatkan data ke SQL Server dari Excel menggunakan PowerShell

Sekarang, bagaimana dengan menggunakan PowerShell untuk menyalin data, mungkin difilter, disortir, dan diagregasi, ke dalam SQL Server, menggunakan PowerShell dan ODBC. Ke arah ini kita dapat menghemat banyak waktu dengan menggunakan perpustakaan BCP. Kami sekarang akan menjelaskan rutinitasnya

Kami akan tetap membongkarnya, sebagai skrip daripada fungsi, karena ini dirancang untuk mengilustrasikan prosesnya

Kami akan mulai dengan menentukan kredensial, preferensi, sumber, dan tujuan kami. Kami akan membaca data dari dan excel spreadsheet dan kemudian meludahkannya ke SQL Server, membuat tabel jika perlu. Untuk membuat tabel tujuan (beberapa spreadsheet ini agak lebar dan karenanya lebih mudah untuk diimpor secara otomatis), kita perlu memeriksa metadata, dan menafsirkannya ke SQL Server yang setara, jadi kita akan melakukannya. Untuk menggunakan perpustakaan BCP, bagus untuk memiliki indikasi kemajuan, jadi saya akan menunjukkan bagaimana Anda melakukannya

Saya telah menyediakan data sampel sehingga Anda tidak perlu mencari-cari sesuatu yang cocok. Ini adalah beberapa data iklim, yang berguna untuk memeriksa hal-hal seperti konversi tanggal

Anda akan melihat bahwa meskipun Anda dapat merender angka dalam berbagai cara, hanya ada satu cara untuk menyimpan angka di Excel, dalam tipe data 'NUMBER' (tipe data lain di Excel adalah LOGICAL, CURRENCY, VARCHAR dan ReadOnly=true0). Karena itu saya harus menentukan ketepatan data numerik, yang sulit jika Anda memiliki beberapa kolom dengan bilangan bulat dan lainnya dengan data desimal nyata dengan angka setelah titik desimal (skala). Ingatlah bahwa rutinitas ini hanyalah membuat tabel pementasan, bukan tujuan akhir. Yang perlu Anda lakukan hanyalah menambahkan pernyataan Anda sendiri untuk mentransfer data ke tabel finalnya dengan CAST ke tipe data internal yang benar

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

set-psdebug -strict

$ErrorActionPreference = "stop"

$ExcelFilePath = 'MyPath\CambridgeWeatherData. xlsx' #jalur lengkap buku kerja excel

$Worksheet = 'cambridgedata' #ini adalah lembar kerja aktual tempat data berada

$DataRange = '' #e. g. 'A2. M33'  ini adalah rentang sel yang membentuk tabel. biarkan kosong untuk membaca seluruh lembar kerja

# tinggalkan nomor baris kedua untuk membaca semua baris dari rentang kolom

$Header = $true # true if you want your first row to be read as column headers

# Jika Anda tidak membaca kolom, mereka diberi label F1. n. Anda dapat dengan mudah menentukannya

#$ColumnNames='''2011'' sebagai tahun,[F1] sebagai Hari'

$ColumnNames = '*'

#Jika Anda tidak memiliki nama bidang di header lembar kerja Anda, Anda dapat menentukan $Header= $false dan menggunakan F1. Fn sebagai gantinya

$DestinationTable = 'CambridgeClimateData' #nama tabel SQL Server tempat Anda ingin

$Destinationinstance = 'MyInstance' #nama server atau instance

$Destinationdatabase = 'MyDataBase' #nama database tempat Anda ingin meletakkan

$DestinationWindowsSecurity = $true #or $False jika Anda tidak menggunakan keamanan Windows<

$DestinationUserID = '' #nama pengguna SQL Server jika keamanan tidak terintegrasi<

$DeleteContentsOfTableBeforeCopy = $false

$PrecisionForNumericData = 1

jika (. (Jalur Uji $ExcelFilePath))

{

    Kesalahan Tulis "Tidak dapat menemukan '$($ExcelFilePath)'. Maaf, tidak dapat melanjutkan karena ini"

    keluar

}

mencoba

{

    $Connection = Objek Baru sistem.data. odbc. koneksi odbc

    $TheConnectionString = 'Driver={Microsoft Excel Driver (*. xls, *. xlsx, *. xlsm, *. xlsb)};DBQ=' + $ExcelFilePath + '; + "$(if ($Header) { 'YES' }

        else { 'TIDAK' })" + '"'

    $Koneksi. ConnectionString = $TheConnectionString

    $Koneksi. Buka()

}

menangkap

{

    $ex = $_. Pengecualian

    Write-Error "saat membuka koneksi ke  $ExcelFilePath menggunakan '$($TheConnectionString)'. $($mis. Pesan). Maaf, tidak dapat melanjutkan karena ini"

    keluar

}

# dapatkan tipenya melalui $Connection. GetSchema('Tipe Data'). pilih TypeName, DataType, SQLType

mencoba

{

    $Query = Objek Baru sistem.data. odbc. odbcommand

    $Query. Koneksi = $koneksi

    $Query. CommandText = 'Pilih' + $columnNames + ' from [' + $Worksheet + '$' + $DataRange + ']'

    $Reader = $Query. ExecuteReader([Sistem. Data. Perilaku Perintah]. SequentialAccess) #dapatkan pembaca data dan dapatkan hasilnya dalam sekali teguk

}

menangkap

{

    $ex = $_. Pengecualian

    Kesalahan Tulis "saat membuat kueri '$($Query. CommandText)' $ex. Pesan Maaf, tetapi kami tidak dapat melanjutkan karena hal ini. "

    Keluar;

}

$kolom = $pembaca. GetSchemaTable() . pilih namakolom, tipe data

jika ($DeleteContentsOfTableBeforeCopy) { $deletionScript = "ELSE DELETE from $DestinationTable " }

else { $deletionScript = '' }

$CreateScript =@"

JIKA TIDAK ADA

(pilih TABLE_NAME dari information_schema. tabel

di mana TABLE_NAME seperti '$DestinationTable')

BUAT TABEL $DestinationTable (

"@

$CreateScript += $columns . foreach-object{ $tipe data = "$($_.dataType)"; "`n`t[$($_. nama kolom. Trim())]  $(switch ($dataType) { 'double'{ "numerik( 18,$PrecisionForNumericData)" } 'boolean'{ 'int' } 'decimal'{ 'Money' } 'datetime'{ 'DateTime' }

            default { 'NVARCHAR(MAX)' } })," }

$CreateScript = $CreateScript. Substring( 0 , $CreateScript. Length - 1 ) + "`n`t)`n $deletionScript"

$DestinationConnectionString = "Data Source=$Destinationinstance;Initial Catalog=$Destinationdatabase;$(

    if ($DestinationWindowsSecurity) { 'integrated security=true' }

    else { 'User Id=' + $DestinationUserID + ';Password=' + "$(((Get-Credential $DestinationUserID). GetNetworkCredential()). Password)" + ';integrated security=false' }

)"

mencoba

{

    #test to see if the table is there. If it isn't, then create it. If it is, then delete the contents

    $SqlCommand = new-object ('Data. SqlClient. SqlCommand') $CreateScript, $DestinationConnectionString;

    $SqlCommand. Connection. Open();

    $handler = [System. Data. SqlClient. SqlInfoMessageEventHandler] { param ($sender,

            $event) Write-Host "Message. $($event. Message)" };

    $SqlCommand. Connection. add_InfoMessage($handler);

    $success = $SqlCommand. ExecuteNonQuery();

    #now squirt the data in using the bulk copy library.

    $bulkCopy = objek baru ("Data.SqlClient.SqlBulkCopy") $DestinationConnectionString

    $bulkCopy. DestinationTableName = $DestinationTable

    $bulkCopy. BatchSize = 5000 #The number of rows in each batch sent to the server

    $bulkcopy. NotifyAfter = 200 #The number of rows to copy before firing a notification

    $bulkCopy. BulkCopyTimeout = 0 #the number of seconds before a time-out

    $objectEvent = Register-ObjectEvent $bulkcopy SqlRowsCopied -Action { write-host "Copied $($eventArgs. RowsCopied) rows " }

    $bulkCopy. WriteToServer($reader) #copy all rows to the server

}

menangkap

{

    $ex = $_. Pengecualian

    Write-Error "Whilst doing the bulk copy '$($Query. CommandText)' $ex. Message Sorry, but we can't proceed because of this. "

}

$Pembaca. Tutup()

$SqlCommand. Connection. Close()

$Connection. Tutup()

OK, but does it work with real data? Off to the Health and Social Care Information Centre for some realistic data in spreadsheet form. I’ve included some data just so you don’t have to go to the site to play along, but it is far better to use the latest version of this data from the site. I’m sure I don’t have to tell you how easy this is to do in a script via PowerShell

1

2

3

$ExcelFilePath='MyPath\hosp-epis-stat-admi-tot-ops-11-12-tab. xlsx' #the full path of the excel workbook

$Worksheet='Total procedures' #this is the actual worksheet where the data is

$DataRange=   'A16. J1509'   #e. g. 'A2. M33'  this is the range of the cells that make up the table. leave blank to read the whole worksheet

Also

1

  $DestinationTable='Hosp' # or whatever you want. The name of the SQL Server table where you want to put the data

 …and

1

$PrecisionForNumericData= 0

 Try it. Whoosh. In it goes. If you were doing this as a routine, you’d be wanting to wrap this script into a function with parameters by now, but you know how to do this already, I’m sure. I’m trying to give you the ‘workbench’ narrative here

Writing to Excel from SQL Server

The process of going from SQL Server to excel via ODBC is, I think, needlessly complicated, especially if you use parameterised queries (excellent for SQL Server but they add very little for writing to Excel). In this example, I’ll do the old and horrible approach of using insert statements. There are other ways, including even using a dataset, but this is the most obvious

I’m not particularly happy with this sample because Excel whines a bit when it opens it, saying that it is in the wrong format, (which it is, but you try naming it XLSX) but it deigns to open it

“The file you are trying to open, ‘MyExcelFile. xls’, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?”

More seriously, it complains that the numbers in the columns are ‘formatted as text’. It turns out that the data is saved in the correct format, but the next time the file is opened, all columns revert to ReadOnly=true1 Seasoned users of ODBC gets used to the bugs, but if anyone knows of a workaround to this, I’d be grateful

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

set-psdebug -strict

$ErrorActionPreference = "stop"

$Sourceinstance = 'MyServerOrInstance' #the name of the server or instance

$Sourcedatabase = 'AdventureWorks' #the name of the datatabase where you want to get the data

#here is where we put the SQL command to get the result from the database

$SelectStatementForDatabase =@"

SELECT ProductNumber, p. Name AS ProductName, color,  SafetyStockLevel, ReorderPoint, StandardCost, ListPrice,

NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)

FROM Production. Product AS p

INNER JOIN Sales. SalesOrderDetail AS sod

Pada p. ProductID = tanah. ID Produk

di mana ((Jumlah Pesanan * Harga Satuan) * DiskonHarga Satuan)>0

ORDER BY ProductName DESC;

"@

$SourceWindowsSecurity = $false #or $True jika Anda menggunakan keamanan Windows

$SourceUserID = 'SA' #nama pengguna SQL Server jika keamanan tidak terintegrasi

$DestinationTable = 'ProductWithDiscounts'

$DestinationExcelFilePath = 'JalurSaya\NamaSaya. xls' #jalur lengkap buku kerja excel

$DestinationHeader = $true # true jika Anda ingin baris pertama dibaca sebagai kolom

#pertama, kita membuat string koneksi 'on the fly'

#koneksi ke database

#. dan dapatkan objek DataReader

$SourceConnectionString = "Sumber Data=$Sourceinstance;Initial Catalog=$Sourcedatabase;$(

if ($SourceWindowsSecurity) { 'keamanan terintegrasi = benar' }

    else { 'User Id=' + $SourceUserID + ';Password=' + "$(((Get-Credential $SourceUserID).GetNetworkCredential()). Kata Sandi)" + ';integrated security=false' })"

mencoba

{

    #di sini kita membuka koneksi ke database sumber SQL Server

    $SqlCommand = objek baru ('Data.SqlClient.SqlCommand')  $SelectStatementForDatabase, $SourceConnectionString;

    $SqlCommand. Koneksi. Buka(); #

    $handler = [Sistem.Data. SqlClient. SqlInfoMessageEventHandler] {

        param ($sender,

            $event) Write-Host "Message. $($acara. Pesan)"

    };

    $SqlCommand. Connection. add_InfoMessage($handler);

    $Reader = $SqlCommand. ExecuteReader([Sistem. Data. Perilaku Perintah]. SequentialAccess) #dapatkan pembaca data dan dapatkan hasilnya dalam sekali teguk

}

menangkap

{

    $ex = $_. Pengecualian

    Write-Error "saat mendapatkan data dari  $Sourceinstance  $Sourcedatabase '. $mis. Pesan"

    keluar

}

# excel hanya memiliki tipe data LOGICAL,CURRENCY,NUMBER,VARCHAR,DATETIME

# menurut $koneksi. GetSchema('DATATYPES'). Ketik nama

# mari kita cari tahu seperti apa tipe data Excel nantinya

$kolom = $pembaca. GetSchemaTable() . pilih namakolom, tipe data, @{

    nama = 'ExcelDatatype'; expression = {

        beralih ($_. tipe data)

        { { @('float', 'decimal', 'Numeric') -contains $_ } { 'Number' } 'bit' { 'logical' } 'int16'{ 'Int' } { @('smallmoney', 'money') -contains $_ } { 'currency' } 'DateTime'{ 'datetime' }

            default { 'VarChar' }

        }

    }

}

# sekarang kita perlu membuat lembar kerja yang setara di Buku Kerja

#Jika tidak ada buku kerja, itu akan membuatnya

$CreateScript =@"

BUAT TABEL $DestinationTable (

"@

$CreateScript += $columns . untuk setiap objek{ "`n`t$($_. Nama kolom. Pangkas())  $($_. ExcelDataType)," }

$CreateScript = $CreateScript. Substring( 0 , $ . .Panjang - 1 ) + "`n`t)"

# dan buat daftar kolom untuk pernyataan insert

$columnList = '[' + $columns . [0].NamaKolom + ']'

untuk ($ii = 1 ; $ii -le $columns.Panjang - 1 ; $ . ++) { $params += ',?'; $columnList += ' ,[' + $columns[$ii].NamaKolom + ']' }

mencoba

{

    #untuk membuka buku kerja tujuan atau membuatnya jika tidak ada

    $Connection = Objek Baru sistem.data. odbc. koneksi odbc

    $TheConnectionString = 'Driver={Microsoft Excel Driver (*. xls, *. xlsx, *. xlsm, *. xlsb)};DBQ=' + $DestinationExcelFilePath + ';Mode + "$(if ($DestinationHeader) { 'YES' }

        else { 'TIDAK' })" + '"'

    $Koneksi. ConnectionString = $TheConnectionString

    $Koneksi. Buka()

    $insertionCommand = $Connection. BuatPerintah()

}

menangkap

{

    $ex = $_. Pengecualian

    Write-Error "saat membuka koneksi ke  $DestinationExcelFilePath menggunakan '$($TheConnectionString)'. $mis. Pesan"

    keluar

}

mencoba

{

    #jika tabel tidak ada kita buat.

    $CreateTableCommand = $Connection. BuatPerintah()

    $CreateTableCommand. CommandText = $CreateScript

    jika ($koneksi. GetSchema('TABEL').Table_Name -notcontains $DestinationTable)

    { if ($CreateTableCommand.JalankanNonQuery() -eq -1) { write-host "created table (worksheet) $DestinationTable" } }

}

menangkap

{

    $ex = $_. Pengecualian

    Write-Error "tidak dapat membuat tabel dengan perintah $CreateScript. $mis. Pesan"

    keluar

}

$baris = 0

mencoba

{

    #sekarang kita membuat setiap pernyataan penyisipan dengan cepat. Pengembang berpaling, tolong

    sementara ($Reader. Baca())

    {

        $insertcommand = "INSERT INTO [$destinationTable" + '$] (' + "$columnList) VALUES("

        untuk ($i = 0; $i -lt $Reader.FieldCount; $i++)

         {

            $insertcommand += "$(if ($i -eq 0) { ''

                else { ',' }) $(if ($columns[$i]. ExcelDataType -eq 'VarChar') { "'$($reader. GetValue($i) -replace "'", "''")'" }

                else { "$($reader. GetValue($i))" }) "

}

$perintah penyisipan. Teks Perintah = $insertcommand + ')'

$baris += $perintah penyisipan. JalankanNonQuery()

}

}

menangkap

{

$ex = $_. Pengecualian

    Write-Error "sementara menulis ke kolom< . $mis. Pesan" $i of file  $DestinationExcelFilePath ' : $ex.Message"

}

#we report what we'telah dilakukan.

tulis-host "Menulis $baris baris dari $($kolom. count) ke lembar kerja $destinationTable"

$Pembaca. Tutup()

$SqlCommand. Connection. Close()

$koneksi. Tutup()

CSV dan Sumber ODBC Terpisah. Teks AdventureWorks

Meskipun driver ACE lebih banyak digunakan oleh orang yang membaca file Excel, saya harus menekankan bahwa ada driver untuk sejumlah format lain. Sangat mudah, misalnya, untuk mengubah sekumpulan file CSV menjadi database relasional. Untuk membuktikannya, saya telah membuat AdventureWorks versi CSV/Teks, beserta skemanya. ini. Ini awalnya dibuat dalam artikel ini The TSQL of CSV. Kesalahan yang Dibatasi Koma. Dengan basis data berbasis teks ini, Anda dapat melakukan banyak contoh contoh SQL AdventureWorks hanya dengan sedikit modifikasi

Setelah Anda menginstal driver ACE, Anda dapat menggunakan versi modifikasi dari rutinitas yang saya tunjukkan atau menjelajahi database PUBS Excel untuk bermain bersama

Yang harus Anda lakukan adalah meng-unzip Text Adventureworks ke direktori baru dengan nama database Anda (AdventureWorks) dan arahkan string koneksi Anda ke direktori dengan memberikannya path lengkap ke direktori. Saya baru saja mengubah dua baris

1

2

#set direktori tempat basis data Anda harus berada.   

$TextFilePath='MyPathToTheDirectoryTextAdventureWorks' #jalur ke database

… dan

1

$Connection. ConnectionString='Driver={Microsoft Access Text Driver (*. txt, *. csv)};DBQ='+$TextFilePath+''<

Sekarang Anda harus siap dengan basis data relasional berbasis teks Anda

Anda tentu saja dapat membuat tabel dan menuliskannya menggunakan pernyataan INSERT

1

buat tabel [Log # csv] (MyInteger int,TheDate date TheMessage char(125))

…dan masukkan pernyataan ke dalamnya. Anda dapat ReadOnly=true_2 juga, yang merupakan hal baru bagi saya. Saya tidak memperhatikan ini dalam inkarnasi sebelumnya dari pengemudi ini

Dengan ReadOnly=true_3pernyataan, Anda dapat menggunakan 'ReadOnly=true4

(Karena penasaran, driver OLEDB mengizinkan  ReadOnly=true5 dan ReadOnly=true6)

1

2

# Anda dapat membuat daftar tabel

$Connection. GetSchema("tabel")|pilih nama_tabel  

Dan skemanya

1

$Connection. GetSchema("kolom")|pilih tableName, ColumnName, cardinalPosition

Berikut adalah beberapa Pernyataan SQL yang berfungsi

1

2

3

PILIH * ke dalam [sarung tangan#csv]

DARI [Production_ProductModel # csv]

DI MANA ProductModelID IN (3, 4)

1

jatuhkan meja [sarung tangan # csv]

1

2

3

4

5

  PILIH hitung(*) as [discounted]

  DARI [Produksi_Produk # csv] AS p

  INNER GABUNG [Sales_SalesOrderDetail#csv] AS sod

  AKTIF p. ProductID = sod. IDProduk

  di mana ((Jumlah Pesanan * UnitPrice) * UnitPriceDiscount)>0

1

2

3

4

5

PILIH Nama, Nomor Produk, ListPrice AS Price

DARI [Produk_Produk # csv]

WHERE ProductLine = 'R'

DAN DaysToManufacture < 4

PESAN OLEH Nama DESC

1

2

3

4

PILIH p1. ProductModelID

DARI [Produk_Produk # csv] AS p1

GROUP OLEH p1. ProductModelID

memiliki p1. ProductModelID >100

1

2

3

4

5

6

7

PILIH p1. ProductModelID

DARI [Produk_Produk # csv] AS p1

GROUP OLEH p1. ProductModelID

MEMILIKI MAX(p1. HargaDaftar) >= SEMUA

  (PILIH AVG(p2.HargaDaftar)

  DARI [Produksi_Produk # csv] AS p2

  DI MANA p1. ProductModelID = p2. ProductModelID)

1

2

3

4

PILIH atas 50 SalesOrderID, SUM(LineTotal) AS SubTotal

DARI [Sales_SalesOrderDetail # csv]

GROUP OLEH SalesOrderID

PESAN OLEH SalesOrderID;

1

2

3

4

PILIH ProductModelID, Nama

DARI [Production_ProductModel # csv]

DI MANA ProductModelID IN (3, 4)

serikat semua

1

2

3

PILIH ProductModelID, Nama

DARI [Production_ProductModel # csv]

DI MANA ProductModelID TIDAK DI (3, 4)

Kesimpulan

Jika saja Microsoft memberikan energi ke seluruh jajaran driver ODBC mereka, termasuk semua kemungkinan penyimpanan data yang dapat dipetakan ke database relasional, mereka akan menjadi cara yang jelas untuk mentransfer data, dan akan membuat Microsoft sangat siap untuk menyediakan 'big data' . Memang, mereka sangat berguna, tetapi dirusak oleh  kebiasaan dan keanehan

Bagi saya, ODBC adalah cara yang jelas untuk  membuat skrip  data dari Excel atau Access ke dalam SQL Server, untuk melakukan impor data.  

9

• Administrasi Database

9

• Administrasi Database

20

• Blog

20

• Blog

2

• Blog
• Pemrograman T-SQL

Bagaimana cara mendapatkan data dari ODBC?

Bagaimana cara menanyakan ODBC di Excel?

Bagaimana cara mengedit kueri ODBC di Excel?

Bagaimana cara mengimpor data ke spreadsheet Excel yang sudah ada?