Riempimento tabella con dati su Excel

TuZa

il 07 lug 2021, 17:07

21 risposte

TuZa

Pochi posts

Iscritto da: gen, 2021

Messaggi:: 14

Riempimento tabella con dati su Excel

07 lug 2021, 17:43

Salve a tutti,

ho un foglio Excel che è composto da diverse colonne rappresentanti tutte dei dati singoli, ma una di esse contiene una serie di dati che sono scritti nella forma che segue:

Col1 Col2 Col3 Col4
ID1 Dato Dato DatoA, DatoB, DatoC; DatoE, DatoF, DatoG; DatoN...

La quarta colonna in pratica rappresenterebbe in un DB una tabella collegata alla tabella principale tramite la colonna 1 (ID univoco). Ogni record della tabella collegata è definito dal ";" e ogni campo della medesima tabella è definito dalla ",".

Da Excel devo passare i dati a Access.
Per riempire una tabella con le colonne a dato singolo (nell'esempio Col1, Col2, Col3) ci vuole poco; ho usato il metodo DoCmd.TransferSpreadsheet per importare tutti i dati in una tabella temporanea e poi in VBA ho scritto una query di accodamento che preleva i dati che mi servono e che esclude la colonna con dati multipli (nell'esempio Col4). Tutto funziona a meraviglia fin qui.

Per la Col4 ho usato la funzione split che in un primo momento estrae ogni record

singleRecord = Split(allData, ";")

e per ogni record poi estrae i valori singoli

singleValue = Split(singleRecord(a), ",")

[dove "a" è il contatore dell'array che vado a scorrere].
Estratti i singoli valori, genero una query di accodamento che riempie la tabella collegata.
A questo punto, il tempo di esecuzione del codice diventa molto lungo (circa 30-40 secondi per poco meno di 1000 record) e vorrei capire se esiste un modo più performante per ottenere il risultato che mi necessita.

Spero di essermi spiegato abbastanza bene.
Grazie per ogni vostro consiglio.
TZ

21 Risposte

O
OsvaldoLaviosa
Super Famoso
Iscritto da
apr, 2012
Messaggi:
7499
Re: Riempimento tabella con dati su Excel
07 lug 2021, 18:22
Io non ho capito come sono fatti questi dati. Tuttavia lavorerei su più steps. Hai detto di essere riuscito con Split a suddividere "qualcosa". Bene, questo "qualcosa" lo butti in una TabellaX. Poi da TabellaX rielabori i dati...forse non solo con Split...ci vedrei anche le funzioni Left, Mid, Right (anche se non ho capito come vanno usate), da riversare in TabellaY. Tutte queste operazioni vanno fatte ciclando i record sfruttando DAO.Recordset.
Però, almeno per me, spiegati meglio sul come sono fatti questi dati...e a come vorresti il risultato finale...non riesco ad afferrare.
S
Sgrubak
Molto attivo
Iscritto da
feb, 2021
Messaggi:
507
Re: Riempimento tabella con dati su Excel
08 lug 2021, 08:30
TuZa ha scritto:
A questo punto, il tempo di esecuzione del codice diventa molto lungo (circa 30-40 secondi per poco meno di 1000 record) e vorrei capire se esiste un modo più performante per ottenere il risultato che mi necessita.
Senza vedere il codice, ipotizzo che tu effettui un INSERT per ogni record. Hai provato ad inserirne più di uno per volta? Magari a blocchi di 10/20?

TuZa

Pochi posts

Iscritto da: gen, 2021

Messaggi:: 14

Re: Riempimento tabella con dati su Excel

08 lug 2021, 08:56

Grazie ad entrambi per le risposte.

Provo a riprodurre quello che devo fare; a partire dal file Excel, fino ad ottenere le due tabelle.

La Tabella principale la ottengo grazie al metodo

DoCmd.TransferSpreadsheet acImport, , "Excel_Imported", strFile, True

Quindi con una query INSERT estraggo dalla tabella importata solo le colonne di interesse e le inserisco nella tabella principale.

Successivamente creo un recordset dei dati che devo importare sulla tabella secondaria

Set objRecordset = New ADODB.Recordset
objRecordset.ActiveConnection = CurrentProject.Connection
objRecordset.Open ("SELECT ID, Configurazioni FROM Excel_Imported)

A questo punto scorro tutti i record ed estraggo i valori presenti nel campo Configurazioni e con una query li inserisco nella tabella secondaria:

While objRecordset.EOF = False
    allData = objRecordset.Fields.Item(1) 'campo Configurazioni
    singleData = Split(allData, ";") 'Inserisco ogni record delimitato da ";" in un array

    For a = LBound(singleData) To UBound(singleData)
        singleValue = Split(singleData(a), ",") 'per ogni record nell'array, prendo i valori delimitati da "," e li inserisco in un array
        
        'Imposto la query
        sqlInsert2 = "INSERT INTO tbl_Secondaria (ID, Versione, Motore, Trazione, Cavalli)" _
                     & "VALUES ('" & objRecordset.Fields.Item(0) & "' ,'" & singleValue(0) & "' ,'" & singleValue(1) & "' ,'" & singleValue(2) & "' ,'" & singleValue(3) & "');"
        
        DoCmd.RunSQL sqlInsert2
    Next

    objRecordset.MoveNext
Wend

Al momento che "gira" la query sqlInsert2, denoto il rallentamento.

Grazie ancora per i vostri consigli.
TZ

S
Sgrubak
Molto attivo
Iscritto da
feb, 2021
Messaggi:
507
Re: Riempimento tabella con dati su Excel
08 lug 2021, 09:50
E hai provato ad inserire più di un record alla volta? Nel ciclo continui a concatenare l'istruzione SQL di INSERT aggiungendo i valori da inserire e poi la esegui solo se ne hai aggiunte 10, o 20 o se cambia l'ID...

Nella tabella secondaria ci vedo bene anche in ID_Secondario, non soltanto ID che penso sia una tua FK.
Ed il campo "Motore" non rispetta il criterio di atomicità del dato. Devono diventare due colonne distinte.
O
OsvaldoLaviosa
Super Famoso
Iscritto da
apr, 2012
Messaggi:
7499
Re: Riempimento tabella con dati su Excel
08 lug 2021, 09:55
Sembra che voglio evitare il problema proposto, ma...Si tratta di una operazione da fare spesso?
A) Se Sì...Quanto ti puoi fidare della digitazione nel campo Configurazioni se poco poco l'utente iniziale non è stato coerente con virgole, punti-virgole ecc...? Quindi, dopo l'assestamento della prima volta (non fa niente che è lento), non sarebbe meglio prevedere 4 colonne apposite?
B) Se No...Lo fai una volta, poi aggiusti meglio l'organigramma generale in modo da non inciampare nei possibili errori che dicevo prima.
T
TuZa
Pochi posts
Iscritto da
gen, 2021
Messaggi:
14
Re: Riempimento tabella con dati su Excel
08 lug 2021, 09:56
Hai ragione sull'ID della tab_Secondaria (in effetti c'è, ma non l'ho riportato) e sull'atomicità del campo motore.

Quello che non mi è chiaro è come riuscire a concatenare l'istruzione INSERT sul cambiamento di ID (ad esempio)...posso chiederti un esempio? Grazie.
T
TuZa
Pochi posts
Iscritto da
gen, 2021
Messaggi:
14
Re: Riempimento tabella con dati su Excel
08 lug 2021, 09:59
OsvaldoLaviosa ha scritto:
Sembra che voglio evitare il problema proposto, ma...Si tratta di una operazione da fare spesso?
A) Se Sì...Quanto ti puoi fidare della digitazione nel campo Configurazioni se poco poco l'utente iniziale non è stato coerente con virgole, punti-virgole ecc...? Quindi, dopo l'assestamento della prima volta (non fa niente che è lento), non sarebbe meglio prevedere 4 colonne apposite?
B) Se No...Lo fai una volta, poi aggiusti meglio l'organigramma generale in modo da non inciampare nei possibili errori che dicevo prima.
Il fatto è che il foglio Excel mi arriva da un software esterno, quindi non posso gestire la forma e il contenuto dei dati.
Però questo mi assicura che i dati saranno scritti sempre allo stesso modo!
S
Sgrubak
Molto attivo
Iscritto da
feb, 2021
Messaggi:
507
Re: Riempimento tabella con dati su Excel
08 lug 2021, 10:41
TuZa ha scritto:
Quello che non mi è chiaro è come riuscire a concatenare l'istruzione INSERT sul cambiamento di ID (ad esempio)...posso chiederti un esempio? Grazie.
Pensavo a qualcosa tipo:
```
While objRecordset.EOF = False
    allData = objRecordset.Fields.Item(1) 'campo Configurazioni
    singleData = Split(allData, ";") 'Inserisco ogni record delimitato da ";" in un array
    sqlInsert2 = INSERT INTO tbl_Secondaria (ID, Versione, Motore, Trazione, Cavalli) VALUES "    
    
    For a = LBound(singleData) To UBound(singleData)
        singleValue = Split(singleData(a), ",") 'per ogni record nell'array, prendo i valori delimitati da "," e li inserisco in un array
        
        'Accodo i valori alla query
        sqlInsert2 = sqlInsert2 & "('" & objRecordset.Fields.Item(0) & "' ,'" & singleValue(0) & "' ,'" & singleValue(1) & "' ,'" & singleValue(2) & "' ,'" & singleValue(3) & "'),"
    Next
    slqInsert2 = Left(sqlinsert2, Len(sqlInsert2)-1) 'Tolgo l'ultima virgola.
    DoCmd.RunSQL sqlInsert2
    objRecordset.MoveNext
Wend
```
Riassegno la [sqlInser2] nel ciclo esterno, così la resetto ad ogni cambio. Nel ciclo For accodo solo i valori da inserire. Li si conosce grazie allo split su [singleData]. Terminato l'accodamento tolgo l'ultima virgola, per evitare errori di sintassi (eventualmente si può solo sostituire col punto e virgola ma non ho provato). Prima di passare al record successivo, effettuo l'insert. Fa un bel debug e verifica che, prima del [DoCmd.RunSQL sqlInsert2] la variabile sia corretta. dovresti avere un'istruzione del tipo:
```
INSERT INTO table1 (First, Last)
VALUES
    ('Fred', 'Smith'),
    ('John', 'Smith'),
    ('Michael', 'Smith'),
    ('Robert', 'Smith');
```
(esempio preso da questa discussione che è su SqlServer, ma la INSERT INTO funziona sempre uguale).

Probabilmente esistono metodi più eleganti, ma già così dovresti ridurre gli inserimenti di parecchio e vedere un miglioramento delle prestazioni.
T
TuZa
Pochi posts
Iscritto da
gen, 2021
Messaggi:
14
Re: Riempimento tabella con dati su Excel
08 lug 2021, 16:17
Grazie per l'esempio, molto utile anche la discussione che mi hai linkato.

Però c'è un problema che non riesco a risolvere.

In corrispondenza di
```
DoCmd.RunSQL sqlInsert2
```
il codice si blocca con un messaggio: "Errore di run-time '3137': punto e virgola (;) mancante alla fine dell'istruzione SQL.

Ho apportato la modifica al tuo esempio nella porzione di codice che segue:
```
sqlInsert2 = Left(sqlInsert2, Len(sqlInsert2) - 1) & ";" 'Tolgo l'ultima virgola.
```
Ma il problema persiste...
Ho fatto anche un debug.print della query ed è giusta, termina con una parentesi chiesa e poi il punto e virgola.
Dove sbaglio?
@Alex
Super Famoso
Iscritto da
feb, 2012
Messaggi:
8740
Re: Riempimento tabella con dati su Excel
08 lug 2021, 16:32
Ma abbiamo fatto un DEBUG.PRINT della stringa prima di mandarla in Esecuzione con RunSQL...?
T
TuZa
Pochi posts
Iscritto da
gen, 2021
Messaggi:
14
Re: Riempimento tabella con dati su Excel
08 lug 2021, 18:06
Si, certo.

Come dicevo, con il DEBUG.PRINT mi sono riletto tutta la stringa e non ci sono errori e per di più termina con parentesi di chiusura dell'ultimo record e il punto e virgola...
@Alex
Super Famoso
Iscritto da
feb, 2012
Messaggi:
8740
Re: Riempimento tabella con dati su Excel
08 lug 2021, 19:02
Puoi pubblicarlo... oppure è un segreto....?
Se dobbiamo fidarci che funziona tutto... è difficile aiutarti non avendo sotto mano il docude da debuggare.

TuZa

Pochi posts

Iscritto da: gen, 2021

Messaggi:: 14

Re: Riempimento tabella con dati su Excel

09 lug 2021, 08:01

Nessun problema a postarla...

INSERT INTO tbl_Secondaria (ID, Item1, Item2, Item3, Item4, Item5, Item6, Item7, Item8, Item9) VALUES ('001','B.','L','','','','16','16','5','2'),('001','S.','L','','','','4','4','1','1'),('001','D.','L','VU','2','','16','16','10','3'),('001','C.','L','','','','16','16','4','2'),('001','W.','A','','','','16','16','3','2'),('001','E.','A','VU','4','','16','16','1','1'),('001','L.','L','','','','4','8','6','2'),('001','N.','L','','','','4','16','3','2'),('001','E.','L','','','','1','1','1','1');

Come dicevo, la query sembra terminare in modo corretto...però magari occhi più esperti ne carpiscono il dettaglio mancante!

Grazie ancora.
TZ

@Alex
Super Famoso
Iscritto da
feb, 2012
Messaggi:
8740
Re: Riempimento tabella con dati su Excel
09 lug 2021, 10:16
Sono TUTTI campi di tipo Testo in Tabella...?
Sei certo...?

Devi accedere o registrarti per scrivere nel forum

21 risposte