Importing Data from Excel with Biml

Importing Data from Excel with Biml

Diesen Artikel finden Sie hier auch in deutscher Sprache.

Did you know, that you could call GetDatabaseSchema on Excel files? You can!

Just define an ExcelConnection first:

<Biml xmlns="">
        <ExcelConnection Name="MyExcel" ConnectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Flatfiles\XLS\MyExcel.xlsx;Extended Properties=&quot;Excel 12.0 XML;HDR=YES&quot;;" />
		<OleDbConnection Name="Target" ConnectionString="Data Source=localhost;initial catalog=MySimpleBiml_Destination;provider=SQLNCLI11;integrated security=SSPI"></OleDbConnection>
		<Database Name="MySimpleBiml_Destination" ConnectionName="Target"></Database>
		<Schema Name="dbo" DatabaseName="MySimpleBiml_Destination"></Schema>

You can call GetDatabaseSchema on that connection and loop through the tables just like any regular database. You will just need to add a schema, as that obviously doesn’t come with Excel:

<#@ template language="VB" #>
<Biml xmlns="">
	<# Dim XLS as Astdbconnectionnode = rootnode.connections("MyExcel")
	   dim ImportResult as Importresults = xls.getdatabaseschema
	   for each tbl as asttablenode in ImportResult.tablenodes 
	   tbl.schema = rootnode.schemas(0)#>
	<#= tbl.getbiml #>
	<# next #></Tables>

After that, you got perfectly valid metadata which you can use to create target tables, dataloads etc.:

<#@ template language="VB" #>
<Biml xmlns="">
		<Package Name="Build_Staging">
				<# for each tbl as asttablenode in rootnode.tables #>
					<ExecuteSQL Name="Create < #>" ConnectionName="Target">
						<DirectInput><#= tbl.getdropandcreateddl #></DirectInput>
				<# next #>

As this will basically do the same thing like SSIS, it means you may run into the same issues when it comes to datatype detection, but for those cases, where importing Excel works for you, Biml might make it even easier for you!

Any questions or comments? We’d love to hear from you at!

Further Information on Biml in English can be found on our English Biml Page.

Happy Biml’ing!