Excel > Excel import via dataquery - content type probleem

[MauriceSmit]

Heren (en dames!),

Ik zit met een lastige kwestie, ik maak gebruik van een excel dataquery om gegevens van werkboek (a) (codenaam: wksNew) te importeren in werkboek (b).
Een soort 'import tabel' wordt via een macro omgezet in een sSql statament (codenaam: sSql), en zodoende kan ik redelijk onafhankelijk van het format van wksNew toch razendsnel data overzetten.
Nu stuiter ik echter op een issue, mbt content types, waarbij Excel (of preciezer: microsoft dataquery) zelf bepaald welk type tekst er in een kolom staat (integer / string / etc). Volgens mij doet hij dit op basis van het aantal keren dat een bepaald type voorkomt in de reeks.

Nu heb ik (helaas) vaak het geval dat de content van zo'n reeks wel eens wil wisselen, en eigenlijk wil ik zelf bepalen wat de contenttype per kolom is; want op dit moment kiest de dataquery ervoor om mijn kolom 'niveau' te bestempelen als numeriek, waarbij hij de string-waardes omzet naar NULL waarden.

Ik heb uit mijn main-model een aantal stukken code gerepliceerd en vereenvoudigd om het probleem uit te zetten hier.
Er zijn twee bestanden bijgevoegd, een 'consolidated model', hierin zit de code voor het importeren van data.
En het bestand wat een voorbeeld bevat voor importdata ('data to import'). Dit is eveneens een sterk vereenvoudigd subset, want reguliere importdata is zo'n 5.000 x 170 (rij/kolom).

De kolom 'Grade' in het 'data to import' komt dus niet goed mee als je de import macro uitvoert in het 'consolidated model', en mijn vraag is dus: hoe fixen we dat?

 

[snb]

1. verwijder eerst lege rijen (rij 1) en kolommen (A)

Sub M_snb()
    ' referentie naar microsoft ActiveX Data object library 20.
    
    For Each cl In Workbooks("data_to_import.xlsx").Sheets("Source").Columns(3).SpecialCells(2)
        cl.Value = Format(cl.Value)
    Next

    With New Recordset
        .Open "SELECT * FROM `SOURCE$`", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\OF\data_to_import.xlsx;Extended Properties=""Excel 8.0;HDR=Yes;"""
        ThisWorkbook.Sheets("Sheet1").Cells(1).CopyFromRecordset .DataSource
     End With
End Sub

 

[MauriceSmit]

Hey snb, thx voor de reactie. De for-each loop truck had ik ook ontdekt. Het issue ontstaat immers dat bij het uitvullen van de importsheet, sommige 'grades' numerieke waarden zijn en Excel dit een 'number' type meegeeft, en bij de rest een 'tekst' type. Deze loop brengt bij grote imports (5.000 regels+) toch enige vertraging in het process, plus dat het op meerdere kolommen kan voorkomen.. Daarmee zou ik dan veiligheidshalve per kolom door elk item moeten gaan en het tekstformat forceren.. Ik had gehoopt dat er een soort query mogelijk was waarin de gebruiker het datatype van de kolommen bepaald :s..

 

[MauriceSmit]

Ik had overigens ook de 'IMEX=1' optie gevonden, voor extended properties.. Echter ook niet zonder issues, want de maanddata (M1 - M16 --> en tot M120 in mijn files); worden dan ook allemaal tekst (denk ik, nog niet getest).

 

[MauriceSmit]

Hmm ik heb een fix gevonden door mijn registry aan te passen.. Niet de meest 'schone' oplossing, maar het werkt.
Voor de geïnteresseerden, de MS Jet engine bepaald het datatype van een kolom by default, afhankelijk van de content van de eerste 8 entries.
Zijn alle 8 entries van het type integer, dan bepaald JETje dat alles integer is, en vervallen alle string waarden in NULL values.

In je register kun je zoeken op 'TYPEGUESSROWS' en zul je in een submap van JETje deze tegenkomen en een hex value van 8 zien.
Deze kun je aanpassen:
0 - betekend dat de gehele kolom wordt gescanned voor datatypes
8 - default value
En je kunt hem natuurlijk ook simpelweg ophogen.

Ik heb er voor gekozen hem op 100 te zetten (ik vind 0 namelijk vrij bruut).