Why is it so hard to move data from Excel to SQL Server?

Business users love Excel.  It is there prefered format of data.

If you can get report data into Excel then they rarely require other reports.

The problem comes when you want to take data the other way.

I have an application that has it’s business logic implemented as a set of stored procedures.  It is a data transformation application so this is the best place to do this.

We need to source certain data from Excel.  OPENDATASOURCE is the recommended solution as follows:

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:Financeaccount.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

Aside from the drive mapping problems that you can get when the data is not on a local drive on the server there are the following issues:

Data types are implied from the data.  When you let Excel guess it has a better than 50% chance of being wrong.

If there are empty columns for a few rows it assumes the entire row is NULL. 

These two can be complete showstoppers.

The only solution was to move to feeding the data from access… 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s