2 giugno 2009 13:45

Leggere con ADO.NET colonne Excel miste numeri e testo

Fin dai tempi dei primi DTS (Sql Server 7.0) ho sempre avuto rogne quando dovevo importare in Sql Server dati da Excel in cui nella stessa colonna ci fossero dati numerici e alfanumerici. L'esempio classico è quello del CAP in cui ci sono numeri (39100) ma anche informazioni testuali ("00100"). Se Excel pensa che la colonna sia di tipo numerico, tutti i dati di tipo stringa che poi lui incontrerà saranno considerati come "vuoti".

La stessa cosa succede se aprite un file excel in Ado.Net tramite un OleDb Provider per riempire una datatable e scorrerla (il problema non ho capito se sia di ado.net o di Jet). In questi giorni dovevo fare un programmino ad un amico importando una colonna "Taglia" che aveva la maggior parte delle informazioni numeriche (38, 39, 40, ecc) ma ogni tanto un "S", "XL", ecc. Ebbene quelle taglie di tipo stringa non c'era verso di fargliele leggere.

Il comportamento (non ho capito se di Jet o del provider ado.net) è infatti quello di scorrere le prime (per default 8) righe del foglio excel e cercare di desumere da quelle il tipo del campo. Uno penserà "metto in cima le taglie alfanumeriche e sono a posto..." nossignori, perchè viene fatta comunque una statistica sulla maggioranza dei dati, e quindi se la maggior parte dei dati sono numeri, lui si impunta a considerare la colonna come numerica.

Il primo workaround che si trova googlando è di aggiungere un IMEX=1 alle Extended Proprieties della stringa di connessione quando creiamo la OleDbConnection. Innanzittutto occhio a mettere tra apici (singoli o doppi) tutte la parte di extended properties, perchè altrimenti vi beccate una bella eccezione "Could not find installable ISAM" (e già capire la causa di questo ti fa perdere il paradiso)

Quindi:
string conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Pippo.xls;Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
oppure
string conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Pippo.xls;Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""";

Purtroppo però molto spesso questo non basta. Per aumentare le possibilità di avere il comportamento che ci aspettiamo (nel mio caso considerare la colonna di tipo stringa), dobbiamo cambiare due chiavi di registro e più precisamente:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes 

che può avere i valori "Majority Type" (che è il default e desume il tipo dalla maggioranza dei dati) o "Text" che è quello da settare se si vuole forzare il tipo.

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows

invece è un numero da 0 a 16 che indica quante righe all'inizio scansionare per determinare il tipo (quel 8 di cui parlavo all'inizio); mettendo 0 si indica di considerare le prime 16384 righe dove si spera che troveremo il primo dato testuale. Se per sfiga la prima taglia "XL" la trovassimo dopo 16385 righe sarei stato fregato.

Riassumendo; con queste impostazioni ho risolto i miei problemi
IMEX=1
ImportMixedTypes = Text
TypeGuessRows=0

Se peró volessi mettere la mia pagina che importa i dati su uno shared host ?? Non penso che Aruba (tanto per fare un nome) sia molto propensa a cambiare delle chiavi di registro solo per me...

(fonte: Joe Morrison ; potevo solo linkare il post ma non si sa mai che scompaia...)