Excel, macro per importazione dati xml

[alessio9899]

Salve a tutti, ringrazio anticipatamente chiunque possa essermi d'aiuto, arrivo subito al punto.

Avrei bisogno di una macro che possa automatizzare questa serie di operazioni che sto per descrivere, so che alcuni potranno dirmi che gran parte di questo lavoro può essere fatto con degli script di visual basic, ma purtroppo esistono delle condizioni che vanificano tutti gli script che ho trovato in rete; di conseguenza l'unica cosa che mi viene in mente è quella di eseguire in serie automatizzata delle operazioni che è possibile singolarmente svolgere con excel. Nella fattispecie vorrei:

Aprire Excel, cliccare su Dati, recupera dati da file XML, recuperare il file corretto all'interno della cartella(si dovrà quindi far in modo di prendere ogni volta il file consecutivo all'interno della cartella), dopodichè nella seguente schermata:


cliccare su Trasforma dati e all'apertura di questa:


jpg upload

vorrei poter espandere tutte le colonne con la scritta "TABLE" per portarle tutte sullo stesso piano ed avere tutte le colonne possibili sullo stesso foglio di lavoro. Per poi alla fine esportare tutto in CSV col nome del file identico a quello del XML.
Grazie a tutti.

[Anthony47]

Non sono un esperto di PowerQuery (in un recente caso si e' cimentato l'utente KlingKlang, vediamo se legge il tuo quesito) quindi non posso seguirti sulla strada che hai tracciato.

Pero' se alleghi un paio di file xml e su questi esempi descrivi il risultato che vuoi ottenere magari potrebbe accendersi una lucina.

Per le istruzioni su come allegare un file:
viewtopic.php?f=26&t=103893&p=605487#p605487

Sono anche curioso di sapere che succede se registri una macro mentre esegui passo passo le istruzioni che hai elencato.
Per le istruzioni di base su come registrare una macro, vedi viewtopic.php?f=26&t=103893&p=622593#p622593
Poi alleghi il codice della macro che hai ottenuto

Ciao

[klingklang]

Leggere ho letto, capito... un po' meno. In modo particolare non mi è chiaro se tutto quanto descritto debba avvenire in modo trasparente per l'utente, e cosa significa "si dovrà quindi far in modo di prendere ogni volta il file consecutivo all'interno della cartella"

[Anthony47]

Ciao Enrico, grazie per l'intervento.
Credo che alessio intendesse dire che ha una serie di file e tutti, uno dopo l'altro, dovrebbero subire lo stesso processo di conversione. Pero' attendiamo la sua spiegazione, insieme con qualche esempio di file xml e di risultato che cerca di ottenere.

[alessio9899]

Grazie per gli interventi ragazzi, cercherò di spiegarmi meglio.
http://www.filedropper.com/023-000003205
http://www.filedropper.com/023-000003205_1

Nei due file allegati abbiamo l'XML di partenza e il CSV convertito manualmente tramite le funzioni DATI di Excel, quello che voglio ottenere è proprio questa conversione su larga scala di tantissimi file all'interno di una cartella, fatta automaticamente e senza il mio intervento, compresa la trasformazione dei dati (vale a dire l'operazione di estrapolazione delle tabelle nidificate per avere nello stesso foglio tutte le colonne di dati possibili).
Con:

"si dovrà quindi far in modo di prendere ogni volta il file consecutivo all'interno della cartella"

intendevo banalmente che la macro per automatizzare questi processi dovrebbe ripetere la stessa operazione per tutti i file presenti nel folder.

Non ho capito il discorso sulla trasparenza piuttosto.
Fatemi sapere se sono stato chiaro e grazie ancora!

[klingklang]

Ciao, metto il link a un filmato in cui ti mostro passo-passo quello che devi fare in Power Query per ottenere il risultato che chiedi (almeno spero!).
Se hai domande siamo qua

https://vimeo.com/337477684

[alessio9899]

Grazie mille davvero, ti faccio sapere il prima possibile!

[Anthony47]

Approfittero' anche io del video per una lezione intensiva su PowerQuery.

Nel frattempo, una ipotesi anche con macro.

Procedimento:

1)
Partri da un nuovo file Excel, e su Foglio1 (il nome non e' importante) in riga 1 ci metti i nomi dei "Nodi" che vuoi leggere (sottostanti il nodo pricipale, root element, che nel nostro caso e' il tag ItacWorkItem) e degli "Attributi" che vuoi leggere.
Eventuali gerarchie vanno indicate con uno "/", mentre eventuali "Attributi" vanno preceduti dal segno "#".

Con riferimento al tuo caso, le prime 5 intestazioni saranno quindi:

Codice: Seleziona tutto

ShortTitle
LongTitle
ItacAcademyItem
ItacAcademyItem#AcademyId
ItacAcademyItem#RecordID


In colonna G c'e' un caso di nodi gerarchici:

Codice: Seleziona tutto

City/PublicationPlaceItalianName


In colonna R c'e' un Attributo da riferire al secondo livello gerarchico, e sara' indicato

Codice: Seleziona tutto

Printers/ItacPersonItem#PersonId


In Colonna X c'e' un attributo riferito al root element, e sara' indicato

Codice: Seleziona tutto

#RecordId


Queste intestazioni devono corrispondere a quanto presente nel sorgente xml, rispettando Maiuscolo/Minuscolo

2)
In questo file, in un Modulo standard del vba inserisci questa macro:

Codice: Seleziona tutto

Sub XMLParse()
'Vedi http://www.pc-facile.com/forum/viewtopic.php?f=26&t=110596
'
'Richiede il riferimento alla libreria Microsoft XML:
'   in Menu /Strumenti /Riferimenti, cercare "Microsoft XML V.xx" e Spuntarla
'
Dim myF As String, MasterN As String, mySplit, myNext As Long
Dim xmlDoc As Object, I As Long, myPath As String, fCnt As Long
'
''myF = "D:\DDownloads\byALESSIO_9899\023-000003205.xml"
'
MasterN = "//ItacWorkItem"                      '<<< Nodo principale
myPath = ThisWorkbook.Path                      '<<< Il percorso dei file xml
'
Set xmlDoc = New MSXML2.DOMDocument
'
myF = Dir(myPath & "\*.xml")                    'Cerca file xml
Do While myF <> ""
    fCnt = fCnt + 1
    xmlDoc.Load (myF)                           'Carica xmlDoc
    myNext = Cells(Rows.Count, 1).End(xlUp).Row + 1
    On Error GoTo gErr                          '
    For I = 1 To Cells(1, Columns.Count).End(xlToLeft).Column               'per ogni intestazione colonna
        If InStr(1, Cells(1, I).Value, "#", vbTextCompare) <> 0 Then        'Verifica se "attributo"
            mySplit = Split("/" & Cells(1, I).Value, "#", , vbTextCompare)  'Gestion Attributo
            If UBound(mySplit) > 0 Then
                If Len(mySplit(0)) < 3 Then mySplit(0) = ""
                Cells(myNext, I) = xmlDoc.SelectSingleNode(MasterN & mySplit(0)).Attributes.getNamedItem(mySplit(1)).Text
            End If
        Else                                                                'Gestion ItemText
            Cells(myNext, I) = xmlDoc.SelectSingleNode(MasterN & "/" & Cells(1, I).Value).Text
        End If
    Next I
    DoEvents
    myF = Dir               'Cerca altri file
Loop
'Fine:
Set xmlDoc = Nothing
MsgBox ("Completata importazione; n° file: " & fCnt)
Exit Sub
gErr:                           'In caso di errore, scrive ****
    Cells(myNext, I) = "****"
    Resume Next
'
End Sub


Per inserire il codice, vedi viewtopic.php?f=26&t=103893&p=647675#p647675

La macro richiede che sia impostato il riferimento alla libreria Microsoft XML; dall'editor delle macro:
-Menu /Strumenti /Riferimenti, cerca "Microsoft XML V.xx" e Spuntala; premi Ok

Per adattarlo ad altre strutture, l'istruzione MasterN = etc etc va personalizzata col nome del "root element".

Quando il file e' pronto salvalo in formato ".xlsm" (macro enabled)

3)
Infine manda in esecuzione la Sub XMLParse

Questa aprira' in sequenza i singoli file xlm e ne estrarra' gli ItemText elencati nell'intestazione.
In caso di errore (es un nodo o attributo mancante) la cella sara' compilata con ****

Un messaggio informera' del completamento delle operazioni.

Fai sapere...

[alessio9899]

Ora come ora è perfetto.
Nel momento in cui andrò a implementare tutto su larga scala, se dovessi avere dei problemi, posso chiedere qui?
Grazie ragazzi

[alessio9899]

Ciao ragazzi, rieccomi con i feedback. Purtroppo su 2 cartelle su 3 ho riscontrato problemi di natura diversa:
Partiamo dalla prima.
Abbiamo la suddetta cartella che allego per facilitarvi la cosa qualora possiate aiutarmi:
http://www.filedropper.com/itacacademyitem_2

Questo è ciò che si verifica:

pur isolando l'xml in questione contenente quel valore, l'errore si ripresenta ugualmente con altri valori.
Idee?

Per quanto riguarda la seconda cartella: http://www.filedropper.com/itacpersonitem ho riscontrato questa difficoltà:


come potete vedere, la colonna Role, ma anche altre, contengono sia valori nidificati in Table che valori singoli, come faccio quindi a estrarre tutto il contenuto della colonna in questo caso?
Grazie come sempre.

[Anthony47]

Probabilmente la tua seconda risposta e' sfuggita a KK, come era sfuggita a me.

Sull'uso delle PowerQuery non ti so dire niente. Pero' ti suggerisco di valutare l'uso della macro...

Nel codice che ti avevo proposto c'e' pero' da correggere la riga xmlDoc.Load (myF)
in

Codice: Seleziona tutto

    xmlDoc.Load (myPath & "\" & myF)                   'Carica xmlDoc

Noto pero', nei nuovi file allegati, un tracciato alquanto diverso dai file precedenti; in particolare ci sono numerosi nodi che sono ripetuti (es ItacPersonItem#RecordId, ItacPersonItem/Name, ItacPersonItem/Nickname e altri).
Ebbene la macro precedente non e' in grado di riprendere tutti questi valori, ma si limita a prelevare il primo di quei nodi (uso di SelectSingleNode).
Se vogliamo prelevare tutte le valorizzazioni dello stesso nodo allora la macro va complicata leggermente:

Codice: Seleziona tutto

Sub XMLParseMulti()
'Vedi http://www.pc-facile.com/forum/viewtopic.php?f=26&t=110596
'Copia anche nodi multipli
'Richiede il riferimento alla libreria Microsoft XML:
'   in Menu /Strumenti /Riferimenti, cercare "Microsoft XML V.xx" e Spuntarla
'
Dim myF As String, MasterN As String, mySplit, myNext As Long
Dim xmlDoc As Object, I As Long, myPath As String, fCnt As Long
Dim myNext1 As Long, cNCnt As Long, CipCiop As Object

''MasterN = "//ItacWorkItem"                      '<<< Nodo principale
MasterN = "//ItacAcademyItem"                   '<<< Nodo principale
myPath = ThisWorkbook.Path                      '<<< Il percorso dei file xml
'
Set xmlDoc = New MSXML2.DOMDocument
'
myF = Dir(myPath & "\*.xml")                    'Cerca file xml
Do While myF <> ""
    fCnt = fCnt + 1
    xmlDoc.Load (myPath & "\" & myF)                                        'Carica xmlDoc
'    myNext = Cells(Rows.Count, 1).End(xlUp).Row + 1
    On Error Resume Next
    myNext = 0
    myNext = Range("A1:AZ10000").Find(What:="*", After:=Range("A1"), _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious).Row
    On Error GoTo 0
    myNext = myNext + 1
'
Cells(myNext, Cells(1, Columns.Count).End(xlToLeft).Column + 2) = myF
    Debug.Print myF
'    On Error GoTo gErr                          '
    For I = 1 To Cells(1, Columns.Count).End(xlToLeft).Column               'per ogni intestazione colonna
        For cNCnt = 0 To 1000
            If InStr(1, Cells(1, I).Value, "#", vbTextCompare) <> 0 Then        'Verifica se "attributo"
                mySplit = Split("/" & Cells(1, I).Value, "#", , vbTextCompare)  'Gestion Attributo
    '            cNCnt = Application.WorksheetFunction.CountIf(Range("A1").Resize(1, I), Cells(1, I)) - 1
                If UBound(mySplit) > 0 Then
                    If Len(mySplit(0)) < 3 Then mySplit(0) = ""
                    Set CipCiop = Nothing
                    On Error Resume Next
                        Set CipCiop = xmlDoc.SelectNodes(MasterN & mySplit(0))(cNCnt).Attributes.getNamedItem(mySplit(1))
                    On Error GoTo 0
                    If Not CipCiop Is Nothing Then
                        Cells(myNext + cNCnt, I) = xmlDoc.SelectNodes(MasterN & mySplit(0))(cNCnt).Attributes.getNamedItem(mySplit(1)).Text
                    End If
                    Debug.Print MasterN & mySplit(0) & "#" & mySplit(1), cNCnt, Cells(myNext + cNCnt, I)
                End If
            Else                                                                'Gestion ItemText
                Set CipCiop = Nothing
                On Error Resume Next
                    Set CipCiop = xmlDoc.SelectNodes(MasterN & "/" & Cells(1, I).Value)(cNCnt)
                On Error GoTo 0
                If Not CipCiop Is Nothing Then
                    Cells(myNext + cNCnt, I) = xmlDoc.SelectNodes(MasterN & "/" & Cells(1, I).Value)(cNCnt).Text
                End If
                Debug.Print MasterN & "/" & Cells(1, I).Value, cNCnt, Left(Cells(myNext + cNCnt, I), 80)
            End If
        If CipCiop Is Nothing Then Exit For
        Next cNCnt
    Next I
    DoEvents
    myF = Dir               'Cerca altri file
Loop
'Fine:
Set xmlDoc = Nothing
MsgBox ("Completata importazione; n° file: " & fCnt)
Exit Sub
gErr:                           'In caso di errore, scrive ****
    Cells(myNext, I) = "****"
    Resume Next
'
End Sub

Il risultato sara' del tipo:


(riferito al file 021-000000001.xml, con le intestazioni visibili nell'immagine. Nota che il file contiene oltre 200 ItacPersonItem, con relativi attributi RecordId e PersonId, e relativi nodi child Name, Nickname, Motto, PersonEmblemDescription, e altro ancora)

Ricordo che la macro richiede che venga spuntato il collegamento alla libreria Microsoft XML v3.0; potrebbe anche essere usata una versione diversa da 3.0 ma in quel caso va modificata la dichiarazione Set xmlDoc = New MSXML2.DOMDocument con il "tipo documento" congruente con la versione.

Ciao

[klingklang]

Non mi è sfuggita la risposta, ma se l'xml ha un formato incoerente, o comunque Power Query non lo interpreta correttamente, non mi resta che alzare le mani...

[alessio9899]

Grazie mille Anthony47, mi cimenterò con questa macro e ti farò sapere il risultato appena possibile.
KlingKlang, in nessuno dei due casi che ho descritto è possibile far nulla?

[klingklang]

KlingKlang, in nessuno dei due casi che ho descritto è possibile far nulla?

Diciamo che io non sono disponibile ad analizzare migliaia di file per scoprire quali differenze nella struttura possano contenere. La procedura illustrata funziona bene se i file hanno struttura IDENTICA. Nel tuo caso evidentemente non è così, quindi il metodo proposto è da scartare (ma dubito ne esista uno migliore)

[alessio9899]

Perdona il malinteso, naturalmente non avevo nessuna intenzione che controllassi migliaia di file per me, nella mia ignoranza magari credevo che ci fosse un modo rapido per arginare almeno il caso 2, quello dell'ulteriore estrazione dei table sullo stesso piano degli altri, ma va benissimo così. Grazie infinitamente per tutto l'aiuto dato fino ad ora.

[Anthony47]

Alessio non si e' piu' fatto sentire, non vorrei che avesse rinunciato al tentativo via macro...

Il file su cui io avevo provato e' scaricabile qui: https://www.dropbox.com/s/wd81kohwvo864 ... .xlsm?dl=0

La macro ultima che avevo pubblicato e' la Sub XMLParseMulti in Modulo3; lavora con le intestazioni presenti in Foglio1.

La Sub XMLParse presente in Modulo1 e' la prima versione, sviluppata sul primo tracciato dei file xml. La Sub XMLParseTEST di Modulo2 e' invece una versione per prove.

Il contenuto di Foglio1 e' stato ottenuto elaborando i file pubblicati dall'utente (la seconda versione). Ricordo che la macro presuppone che i file xml si trovino nella stessa directory in cui viene salvato il file Excel.

Ciao

[alessio9899]

Non ho assolutamente intenzione di mollare! Semplicemente al momento mi trovo fuori per una settimana di vacanze, al rientro della settimana prossima mi ci ridedico e porterò avanti i suggerimenti che mi hai fornito sia qui che per mp, grazie mille Anthony47