Month: February 2007

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

~ chriseyre2000 ~ Leave a comment

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… 

Code Sample for Datasets and DataGridViews

~ chriseyre2000 ~ Leave a comment
        public Form1()
        {
            InitializeComponent();
            SqlConnection cnt = new SqlConnection(GetConnectionString());

            cnt.Open();
            SqlCommand cmd = cnt.CreateCommand();
            cmd.CommandText = “select * from Orders; select * from [Order Details]”;
            northwind.Load( cmd.ExecuteReader(), LoadOption.OverwriteChanges, “Orders”,”Order Details”);
            cnt.Close();
        }

        static private string GetConnectionString()
        {
            // To avoid storing the connection string in your code,
            // you can retrieve it from a configuration file, using the
            // System.Configuration.ConfigurationSettings.AppSettings property
            return “Data Source=(local);Initial Catalog=Northwind;”
                + “Integrated Security=SSPI;”;
        }

        private void dataGridView1_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            string s = dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString();
            orderDetailsBindingSource.Filter = “OrderId=” + s;
        }

Software Requirement Specifications

~ chriseyre2000 ~ Leave a comment

I have been working on a number of SRS’ lately. These do help in the developement of the application. At least you have a formal definition of what the project is trying to do.

A SRS helps in the development of the test plan.

Recently we have found one slight flaw. If the customer does not read the SRS it’s value decreases significantly. If the customer is not interested in reading a large formal document then a few small technical memos would have a greater chance to be read.