Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Zasilenie danymi z XLSa bazy SQL Server 2005 (64bit) z Excel

shyha · Starszy Forumowicz Posty: 50

Czy istnieje sposób aby zasilić bazę SQL Server 2005 z poziomu Excela 2007?
Mam ustawione ODBC z bazą SQL Server 2005 (64bit).
Mam zakładkę w Excelu z danymi do importu. Na serwerze SQL Server jest już gotowa baza do załadunku. Nazwy kolumn w Excelu i SQL Serverze pokrywają się.

W Excelu jest możliwość wpisania querki sqlowej z poziomu DATA CONNECTIONS. Googlałem i widziałem wykorzystanie JET, ale chyba to nie działa w 64bitowym środowisku :/

Oczywiście z poziomu SQL Server można bardzo łatwo zaimportować dane (wizard, SSIS), ale w moim przypadku to musi być wykonane z poziomu Excela

OShon · Wysłany: 2011-11-16, 13:59

System x64 jak najbardziej - wszystko chodzi, ale w przypadku Officea to jest wiele niekompatybilności. Głownie chodzi o drivery, które będą honorowane w office.
Wiem że są hece z Outlookiem x64 (synchronizacja z telefonami) ale ze z Excelem .. ?
Faktycznie mógłbyś to sprawdzić. W przypadku niepowodzenia przeinstalować na x86 - jak pamiętam na tej samej płytce są 2ie wersje do wyboru podczas instalacji.

zam import danych z mySQLa jest opisany na youtube

shyha · Starszy Forumowicz Posty: 50

Źle mnie zrozumiałeś.

Jak zrobić selecta do bazy i pokazać w Excelu to wiem.

Nie wiem natomiast jak zasilić bazę z poziomu Excela. Przykład:
Mam w Excelu zakładkę Sheet1. W niej 3 kolumny z danymi:
IMIE | NAZWISKO | ADRES
Andrzej | Kowalski | Gdynia
Marta | Kowalska | Rzeszow
Donald | Tusk | Warszawa

Ustawione mam ODBC do SQL Server 2005. Istnieje w niej pusta baza (Baza_A) i tabela (Tablica_A) oraz kolumny (IMIE | NAZWISKO | ADRES).

Chcę dane z Excela przesłać na serwer. Próbowałem pisać inserty w command text, ale chyba zła składnia lub ograniczenia po stronie XLSa.

OShon · Wysłany: 2011-11-17, 11:08

Na youtube masz podane jak importować do bazy MySQL
Choć są tam różne rozwiązania to myślę że najlepiej realizować to zadanie przez ADO.
Zakładamy że dane do importy zawierają się w kolumnach od A:C

konstrukcja zapytania:

Kod:

'dodać do modułu referencje: Microsoft ActiveX Data Objects 2.5 Library
Dim pytanie As String, x as long
Dim c As New ADODB.Connection
Dim F As New ADODB.Recordset
c.Open '....dane logowania zgodne z twoją bazą
'deklaracja zmienych oraz połączenie umiescić pow.
For x = 2 To Cells(rows.count, "A").End(xlUp).Row 'zaczynamy od wiersza 2 o ile jest nagłówek
If Len(Cells(x, 1).Value) = 0 Then GoTo przeskocz 'jeśli mamy przerwę w danych
pytanie = "insert into schemat.nazwa_tabeli (IMIE,NAZWISKO,ADRES) Values ('" & _
Cells(x, 1) & "','" & _
Cells(x, 2) & "','" & _
Cells(x, 3) & "')"

F.Open pytanie, c, adOpenStatic, adLockReadOnly
przeskocz:
Next x
c.Close

tkuchta1 · Excel Expert **Pomógł:** 1495 razy Posty: 2270

ja też kiedyś pisałem o współpracy XL'a z mySQL (w obie strony)
możesz poczytać
http://www.apocotenexcel.pl/mysql.htm

Zapis do mySQL jest w procedurze InsertIntoTableSQL_ADO

jednak nie próbowałem tego z XL'a 64bit'owego.

shyha · Starszy Forumowicz Posty: 50

Dzięki. Właśnie spróbowałem tej metody.
Łączy się z ODBC, ale wywala mi błąd:

Cytat:

Runtime error 1004:
Application-defined or Object-defined error

i w debugu podświetla tę linie:

Kod:

For x = 2 To Cells(rowscount, "A").End(xlUp).Row 'zaczynamy od wiersza 2 o ile jest nagłówek

OShon · Wysłany: 2011-11-17, 11:51

Cytat:

Application-defined or Object-defined error

referencje dodałeś?

rowscount zamień na rows.count
Pisałem z palca i kropka gdzieś poszła w las

shyha · Starszy Forumowicz Posty: 50

Referencje dodałem 2.5
Rzeczywiście Rows.Count powinno być. Poprawiłem. Teraz wywalała błąd na uprawnieniach, ale jeszcze sprawdzam czy są dla usera na bazie odpowiednie accessy.

OShon · Wysłany: 2011-11-17, 12:28

Uprawnienia ustawie się w połączeniu (nazwa konta logującego się, oraz hasło).

shyha · Starszy Forumowicz Posty: 50

Uprawnienia na bazie miałem tylko read dla tego usera, dodałem write i działa! :clap

Dane został zapisane na serwerze :)
DZIĘKI PANOWIE!

Pozostaje mi tylko ostatni problem - jak zapobiec przypadkowemu wysłaniu 2x tych samych danych?

Może zrobić z tych 3 kolumn klucz złożony, albo dodatkową kolumnę?

OShon · Wysłany: 2011-11-17, 12:56

Klucz zakładasz na tabeli w bazie MySQL
Względnie, możesz wykonać w pętli dodanie pozycji do tablicy i sprawdzenie czy pozycja była już importowana:

zmodyfikuj kod w odpowiednim miejscu

Kod:

'deklaracja zmienych oraz połączenie umiescić pow.
Dim max_row, y&
max_row = Cells(Rows.Count, "A").End(xlUp).Row
ReDim baza(1 To max_row)
For x = 2 To max_row 'zaczynamy od wiersza 2 o ile jest nagłówek
If Len(Cells(x, 1).Value) = 0 Then GoTo przeskocz 'jeśli mamy przerwę w danych

For y = LBound(baza) To UBound(baza)
If baza(y) = Cells(x, 1).Value Then GoTo przeskocz
Next y

baza(x - 1) = Cells(x, 1).Value
pytanie = "insert into schemat.nazwa_tabeli (IMIE,NAZWISKO,ADRES) Values ('" & _
Cells(x, 1) & "','" & _
Cells(x, 2) & "','" & _
Cells(x, 3) & "')"

F.Open pytanie, c, adOpenStatic, adLockReadOnly
przeskocz:
Next x

shyha · Starszy Forumowicz Posty: 50

Dzięki za kod, ale chyba sprawdzanie dubli z poziomu Excela nie zda egzaminu bo może się zdarzyć, że zakładka z danymi będzie usuwana i zapełniania ponownie tymi samymi danymi do exportu (przez przypadek).

Spróbuję założyć klucz złożony w SQL. Nie wiem tylko jak zachowa się Excel przy napotkaniu takiego zdublowanego rekordu podczas eksportu.

Mam teraz inny problem. Gdy włączam eksport danych do SQL to z 17,000 rekordów wysyła się ~15,500. Trwało to oczywiście z 20-30 minut. Na optymalizacji tej metody mi aż tak nie zależy jak na tym aby te dane w całości zostały wyeksportowane. Spróbuje zaraz dokleić do postu kod błędu.

OShon · Wysłany: 2011-11-18, 10:40

shyha · Starszy Forumowicz Posty: 50

Tym razem wyeksportowało 17,000 rekordów w 1h bez żadnego błędu.
Połączenie jest tworzone przed pętlą. Jeden rekord składa się z 10 kolumn. Połączenie jest z serwerem w sieci LAN.

Próbowałem wrzucić drugi raz aby sprawdzić czy klucz złożony zadziała....zadziałał i wywala błąd w Excelu z informacją o duplikacie.

Czy da się zrobić, aby skipował pętle z błędem i przechodził do następnej pętli i przy okazji zapisując w jakimś logu lub zakładce "bledy" nr wiersza z błędem?

Z góry dzięki za pomoc!

OShon · Wysłany: 2011-11-18, 13:09

Polecenie on error goto miejsce lub po nr konkretnego błędu.

Kod:

for .....
'kod zlepiania stringa
on error goto blad
F.Open pytanie, c
dalej:
next
'....reszta kodu
exit sub
debug.print err.nr & " " & err.description & " -> " & pytanie 'zapis w oknie immediate
resume dalej 'lub goto dalej
exit sub