Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

Excel, macro per importazione dati xml

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

Excel, macro per importazione dati xml

Postdi alessio9899 » 20/05/19 14:57

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:
Immagine

cliccare su Trasforma dati e all'apertura di questa:

Immagine
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.
alessio9899
Newbie
 
Post: 8
Iscritto il: 20/05/19 14:06

Sponsor
 

Re: Excel, macro per importazione dati xml

Postdi Anthony47 » 20/05/19 17:16

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
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 16510
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel, macro per importazione dati xml

Postdi klingklang » 20/05/19 17:24

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"
Enrico
Windows 7 + Office 2016 64bit / Windows 10 + Office 365 32/64bit
Avatar utente
klingklang
Utente Junior
 
Post: 97
Iscritto il: 23/11/18 15:01
Località: San Giovanni in Persiceto

Re: Excel, macro per importazione dati xml

Postdi Anthony47 » 20/05/19 17:30

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.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 16510
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel, macro per importazione dati xml

Postdi alessio9899 » 21/05/19 08:59

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!
alessio9899
Newbie
 
Post: 8
Iscritto il: 20/05/19 14:06

Re: Excel, macro per importazione dati xml

Postdi klingklang » 21/05/19 10:43

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
Enrico
Windows 7 + Office 2016 64bit / Windows 10 + Office 365 32/64bit
Avatar utente
klingklang
Utente Junior
 
Post: 97
Iscritto il: 23/11/18 15:01
Località: San Giovanni in Persiceto

Re: Excel, macro per importazione dati xml

Postdi alessio9899 » 21/05/19 12:06

Grazie mille davvero, ti faccio sapere il prima possibile!
alessio9899
Newbie
 
Post: 8
Iscritto il: 20/05/19 14:06

Re: Excel, macro per importazione dati xml

Postdi Anthony47 » 21/05/19 22:08

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...
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 16510
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel, macro per importazione dati xml

Postdi alessio9899 » 22/05/19 17:58

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
Newbie
 
Post: 8
Iscritto il: 20/05/19 14:06

Re: Excel, macro per importazione dati xml

Postdi alessio9899 » 23/05/19 15:06

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:
Immagine
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à:
Immagine

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.
alessio9899
Newbie
 
Post: 8
Iscritto il: 20/05/19 14:06

Re: Excel, macro per importazione dati xml

Postdi Anthony47 » 25/05/19 00:52

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:
Immagine

(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
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 16510
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel, macro per importazione dati xml

Postdi klingklang » 25/05/19 12:56

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...
Enrico
Windows 7 + Office 2016 64bit / Windows 10 + Office 365 32/64bit
Avatar utente
klingklang
Utente Junior
 
Post: 97
Iscritto il: 23/11/18 15:01
Località: San Giovanni in Persiceto

Re: Excel, macro per importazione dati xml

Postdi alessio9899 » 25/05/19 15:22

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?
alessio9899
Newbie
 
Post: 8
Iscritto il: 20/05/19 14:06

Re: Excel, macro per importazione dati xml

Postdi klingklang » 25/05/19 15:36

alessio9899 ha scritto: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)
Avatar utente
klingklang
Utente Junior
 
Post: 97
Iscritto il: 23/11/18 15:01
Località: San Giovanni in Persiceto

Re: Excel, macro per importazione dati xml

Postdi alessio9899 » 25/05/19 16:20

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.
alessio9899
Newbie
 
Post: 8
Iscritto il: 20/05/19 14:06

Re: Excel, macro per importazione dati xml

Postdi Anthony47 » 29/05/19 18:41

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
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 16510
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel, macro per importazione dati xml

Postdi alessio9899 » 01/06/19 14:08

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
alessio9899
Newbie
 
Post: 8
Iscritto il: 20/05/19 14:06


Torna a Applicazioni Office Windows


Topic correlati a "Excel, macro per importazione dati xml":

Excel
Autore: fra.dibia
Forum: Software Windows
Risposte: 1

Chi c’è in linea

Visitano il forum: Nessuno e 20 ospiti