Opis problemu
Przez ostatnie dwa dni walczyłem nad importem pliku XML do Access-a, sprawa nie była skomplikowana, jednak przyniosła sporo nowej wiedzy jak i stresów spowodowanych niemożnością użycia standardowych mechanizmów oferowanych przez Accessa. To niestety nie była moja zachcianka, ale funkcje transformxml i importxml losowo genrowały błędy, a co za tym idzie tracenie czasu na napisanie obejścia.
Przejdźmy jednak do rzeczy, na wstepie dostawałem plik xml-a:
<root>
<a id="1">
<a1>
<b>
<c>blabla</c>
</b>
<d>
<e>blabla</e>
</d>
</a1>
</a>
<a id="2">
<a1>
<b>
<c>blabla</c>
</b>
<d>
<e>blabla</e>
</d>
</a1>
</a>
</root>
Efektem oczekiwanym było stworzenie 2 tabel b i d o takiej strukturze
tabela b
| id | c |
|---|---|
| 1 | blabla |
| 2 | blabla |
tabela d
| id | e |
|---|---|
| 1 | blabla |
| 2 | blabla |
Rozwiązanie metoda I
Nie zastanawiałem się długo i stwierdziłem że skoro, Access może importować pliki + dodatkowo można je przekształcać za pomocą xslt, postanowiłem to zrobić w ten sposób. Zacząłem od napisania szablonu który przekształci mi źródłowy plik do postaci oczekiwanej:
<?xml version="1.0"?>
<root>
<a>
<a1>
<b>
<id>1</id>
<c>blabla</c>
</b>
<d>
<id>1</id>
<e>blabla</e>
</d>
</a1>
</a>
<a>
<a1>
<b>
<id>2</id>
<c>blabla</c>
</b>
<d>
<id>2</id>
<e>blabla</e>
</d>
</a1>
</a>
</root>
Nie będe ukrywał, że było to dla mnie problematyczne, ale wsparty przez społeczność devpytania.pl, skorzystałem z odpowiedzi użytkownika lqc
Także mając szablon:
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="a1/*">
<xsl:copy>
<xsl:apply-templates select="@*" />
<id>
<xsl:value-of select="ancestor::a/@id" />
</id>
<xsl:apply-templates select="node()" />
</xsl:copy>
</xsl:template>
<xsl:template match="a">
<xsl:copy>
<xsl:apply-templates select="attribute::*[name()!='id']" />
<xsl:apply-templates />
</xsl:copy>
</xsl:template>
<!-- standard copy template -->
<xsl:template match="@*|node()">
<xsl:copy>
<xsl:apply-templates select="@*" />
<xsl:apply-templates />
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
Napisałem ten nieskomplikowany kod w VBA:
Dim src As String Dim xslt As String Dim dest As String src = CurrentProject.Path & "\doimportu.xml" xslt = CurrentProject.Path & "\doimportuxml.xsl" dest = CurrentProject.Path & "\import.xml" Application.TransformXML src, xslt, dest Application.importxml dest, acStructureAndData
Efekt: miałem w myślach już zakończone działanie, jednak rzeczywistość okazała się brutalna, po testach okazało się, że dostaje błąd runtime:
Method ‘ImportXML’ of object ‘_Application’ failed
Niestety google nie pomógł, te same czynności odpalane ręcznie via kreator importu działały poprawnie, nie miałem wyjścia jak, przeorganizować to wszystko.
Rozwiązanie Metody posrednie (nieskuteczne)
Tutaj nie będe sie rozpisywał, bo efekt nadal był taki sam, a próbowałem obejść to w ten sposób:
- Stworzenie osobnych arkuszy xsl-a, które stworzą niezależne xml-e zawierające dane tylko o wybranej podtabeli: b i d
- Przetworzenie pliku xml programowo i zaimportowanie po kolei
Efekt niestety nie rozwiązał błędu.
Stwierdziłem, że skoro xml dla access-a jest be, to posilę się starym sprawdzonym CSV…
Rozwiązanie Metoda II
Na wstępie wspomnę tylko, że pominę kolejność w której to robiłem, bo nie była ona taka oczywista, opiszę to jak pełny przykład.
Skoro miałem oprzeć się na CSV to postanowiłem, że bedę go rozdzielał tabulatorami, ponieważ przecinki mogą wystąpić w treści, stworzyłem
styl który mi stworzył wymaganego CSV-a, natchenieniem był ten wpis na blogu Davida Lee:
Styl dla tabeli d
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="text" encoding="utf-8" media-type="text/plain" omit-xml-declaration="yes"/> <xsl:variable name="tab" select="' '" /> <xsl:variable name="newline" select="' '" /> <xsl:template match="d"> <xsl:value-of select="ancestor::a/@id" /> <xsl:value-of select="$tab" /> <xsl:value-of select="e"/> <xsl:value-of select="$newline" /> </xsl:template> <xsl:template match="b"> </xsl:template> <xsl:template match="root"> <xsl:apply-templates/> </xsl:template> </xsl:stylesheet>
styl dla tabeli b
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="text" encoding="utf-8" media-type="text/plain" omit-xml-declaration="yes"/> <xsl:variable name="tab" select="' '" /> <xsl:variable name="newline" select="' '" /> <xsl:template match="d"> </xsl:template> <xsl:template match="b"> <xsl:value-of select="ancestor::a/@id" /> <xsl:value-of select="$tab" /> <xsl:value-of select="c"/> <xsl:value-of select="$newline" /> </xsl:template> <xsl:template match="root"> <xsl:apply-templates/> </xsl:template> </xsl:stylesheet>
Te style pozwoliły mi stworzyć interesujące mnie plik CSV (b.csv,d.csv):
1 blabla 2 blabla
Tu niestety okazało się, że TransformXML ignoruje encoding zadeklarowane w xsl:output, więc musiałem z niego zrezygnować i stworzyć funkcje w oparciu o msxml:
Private Function TransformXSLT(xmlFile, xsltFile, resultFile) As Boolean
Dim xml As Object
Dim xslt As Object
Dim dest As New ADODB.Stream
On Error GoTo BLAD
TransformXSLT = True
'Załaduj xml-a
Set xml = CreateObject("MSXML2.DOMDocument.3.0")
xml.async = False
xml.load xmlFile
'Załaduj XSLT
Set xslt = CreateObject("MSXML2.DOMDocument.3.0")
xslt.async = False
xslt.load xsltFile
'przekształć
dest.Open
dest.Charset = "utf-8"
dest.WriteText xml.transformNode(xslt)
dest.SaveToFile resultFile, adSaveCreateOverWrite
POSPRZATAJ:
On Error Resume Next
dest.Close
Set dest = Nothing
Set xml = Nothing
Set xslt = Nothing
Exit Function
BLAD:
TransformXSLT = False
GoTo POSPRZATAJ
End Function
Dodatkowo (wyszło w sumie na końcu prac) należy z wygenerowanych plików usunąć BOM, ponieważ pierwszy rekord zaimportowany posiadał dziwne krzaczki, niewidoczne gołym okiem, funkcja autorstwa alj, użytkownika stackoverflow:
Public Function RemoveBOM(filePath)
' Create a reader and a writer
Dim writer, reader, fileSize
Set writer = CreateObject("Adodb.Stream")
Set reader = CreateObject("Adodb.Stream")
' Load from the text file we just wrote
reader.Open
reader.LoadFromFile filePath
' Copy all data from reader to writer, except the BOM
writer.Mode = 3
writer.Type = 1
writer.Open
reader.Position = 5
reader.CopyTo writer, -1
' Overwrite file
writer.SaveToFile filePath, 2
' Return file name
RemoveBOM = filePath
' Kill objects
Set writer = Nothing
Set reader = Nothing
End Function
Na tym etapie poszło z górki, pozostało stworzyć schema.ini:
[b.csv] Format=TabDelimited ColNameHeader=False TextDelimiter=none CharacterSet=65001 Col1=id Text Col2=b Text [d.csv] Format=TabDelimited ColNameHeader=False TextDelimiter=none CharacterSet=65001 Col1=id Text Col2=e Text
Tutaj najważniejsze w sumie 2 rzeczy:
- TextDelimiter=none – zaznaczenie, ze nie ma kwalifikatora tekstu
- CharacterSet=65001 – wskazanie kodowania na utf-8, (numery stron kodowych można znaleźć tutaj)
Uwieńczeniem tych prac było połączenie tego w całość:
Dim src As String
Dim xslt As String
Dim dest As String
src = CurrentProject.Path & "\doimportu.xml"
xslt = CurrentProject.Path & "\b.xsl"
dest = CurrentProject.Path & "\b.csv"
'przekształć
TransformXSLT xmlpath, xslt, dest
xslt = CurrentProject.Path & "\d.xsl"
dest = CurrentProject.Path & "\d.csv"
TransformXSLT xmlpath, xslt, dest
'usunBOM
RemoveBOM dest
dest = CurrentProject.Path & "\b.csv"
RemoveBOM dest
'przegraj dane
SQL = "SELECT * into d FROM [Text;DATABASE=" & CurrentProject.Path & "].d.csv;"
CurrentProject.Connection.Execute SQL
SQL = "SELECT * into b FROM [Text;DATABASE=" & CurrentProject.Path & "].b.csv;"
CurrentProject.Connection.Execute SQL
Podsumowanie
Problem został rozwiązany, pewnie te arkusze można zrobić lepiej, ale to wszystko na co mnie stać w tym temacie, dużo wiedzy nowej mi zostało, mam nadzieje że komuś innemu oszczędzi podobnych problemów.
0 Comments.