Home | Ciber
Knowledge page of Ciber Netherlands

Import vanuit Excel heeft haken en ogen

Ciber SSIS specialist Frank Reterink heeft eens uitgebreid onderzocht wat nu de haken en ogen zijn bij de import vanuit Excel via SSIS.

Inleiding (uit de “literatuur”):

Bij de import van Excel bestanden maakt de Excel connection manager in Integration Services gebruik van de “Microsoft OLE DB Provider for Jet 4.0” en de ondersteunende “Excel ISAM” driver Deze driver heeft een aantal eigenaardigheden:

- Data bronnen:
Voor het aanroepen van en sheet of benoemd data bereik moet achter de naam een $ geplaatst worden. Om syntax errors te voorkomen moet die complete naar door rechte haken [Sheet$] omgeven worden.

- Missende waarden (mijn grote frustratie ooit):
Standaard kijkt de driver naar de eerste 8 regels ( dit kan veranderd worden in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel waarde TypeGuessRows=8). Indien de driver in die eerste rijen verschillende datatypen tegenkomt, dan wint de meest voorkomende waarbij in geval van gelijkspel het nummer wint. De opmaak in Excel doet er niet toe. Voldoet een waarde niet aan een gekozen datatype, dan wordt de waarde leeg gelaten!
Door aan de “extended properties” in de “connectiestring” de property IMEX=1 toe te voegen wordt dit gedrag anders. In deze modus wordt de registry setting “ImportMixedTypes=Text” gebruikt. Kolommen met verschillende datatypen worden dan als tekst ingelezen.

- Afgekapte tekst
Als in de eerste regels (die 8 stuks standaard) geen tekst langer dan 255 bestaat, dan wordt de tekst met een max lengte van 255 ingelezen. Is er wel een tekst langer dan 255, dan wordt het datatype memo gebruikt.

- Dataypen
De driver kent maar een paar dataypen. Zo worden alle numerieke waarde een double (DT-R8) en alle tekstwaarden (behalve memo) een 255 karakter lange unicode string (DT_WSTR).
Integration Services koppelt de Excel-datatypes als volgt aan de eigen typen:

  • Numeric – double-precision float (DT_R8)
  • Currency – currency (DT_CY)
  • Boolean – Boolean (DT_BOOL)
  • Date/time – datetime (DT_DATE)
  • String – Unicode string, length 255 (DT_WSTR)
  • Memo – Unicode text stream (DT_NTEXT)

- Integration servers zal de Excel-datatypen niet impliciet converteren. Dat moet in de package gebouwd worden.

Mijn advies bij het importeren van een Excel vanuit Microsoft producten of producten die de Microsoft tussenlaag gebruiken:

- Lees Headers ook in en gebruik de import modus (HDR=NO;IMEX=1)

- Zorg dat de headers alfanumeriek zijn.

- Zorg dat lange teksten (>255 karakters) binnen de “sample set” zitten, door deze bovenin te zetten of de “sample set” voldoende groot te maken.

Mijn bronnen:
http://msdn.microsoft.com/en-us/library/ms141683.aspx

http://support.microsoft.com/kb/257819

http://support.microsoft.com/kb/194124

http://www.connectionstrings.com/excel-2007

Groet,

 

Frank Reterink

No comments yet. Be the first.

Leave a reply