How do i locate and import data from an excel spread sheet into my SQL express db?

Michael Sterling
Michael Sterling used Ask the Experts™
on
I'm looking for information (link, sample code etc.) on how to find and import data from an excel spreadsheet into my SQL express db,...any help would be appreciated. Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Michael SterlingWeb Applications Developer

Author

Commented:
the spreadsheet my be in 2003 - 2007 or 2010 excel...
Michael SterlingWeb Applications Developer

Author

Commented:
i need something more "granular". what i mean is, i need to find the excel spreadsheet, and parse the cells of each row. the sheet contains more data than i need, so i only want to process it once, directly into the table within my dagabase. so i need to process each row individually into an object and then add that object, to my table.
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Michael SterlingWeb Applications Developer

Author

Commented:
@disrupt: 1st let me say that i will award you the points come what may of my next question because the link you supplied (the 2nd one) ultimately lead to my solution more or less (still have some tweaking to do but one of the code examples pretty much worked). this is what i ran into though,...for some reason i couldn't get the code (included below) to work for Microsoft Excel 2010. I had to save the spread sheet into a 97 - 2003 format and then it worked. how do i get this to work for microsoft excel 2010, just incase. or for 2007 for that matter? if you look in my connection string the file extension is ".xls" instead of ".xlsx". i will be posting this question as a general question as well just to gather other input. thanks again.
protected void UpLoadFile_Click(object sender, EventArgs e)
    {
        System.Uri uri1 = new Uri(@"C:\src\StudyTimeTrackerPrimeI\Data\QUStudentInfo.xlsx");
        Uri relativeUri = uri1.MakeRelativeUri(uri1);
        OleDbConnection _con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+@"C:\src\StudyTimeTrackerPrimeI\Data\QUStudentInfo.xls;Extended Properties=Excel 12.0");

        try
        {
            _con.Open();
            System.Data.DataTable _tab = new System.Data.DataTable();
            System.Data.DataTable _SheetTable = new System.Data.DataTable();
            _SheetTable = _con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            for (int Sheet = 0; Sheet < _SheetTable.Rows.Count; Sheet++)
            {
                OleDbCommand _com = new OleDbCommand("SELECT * FROM [" + _SheetTable.Rows[Sheet][2].ToString() + "]", _con);
                OleDbDataAdapter _dap = new OleDbDataAdapter(_com);

                _dap.Fill(_tab);

                string _Meesage = string.Empty;
                for (int x = 0; x < _tab.Rows.Count; x++)
                {
                    for (int y = 0; y < _tab.Columns.Count; y++)
                    {
                        _Meesage += _tab.Rows[x][y].ToString() + ",";

                    }
                    _Meesage += "\r\n";

                }
                string _FileName = _SheetTable.Rows[Sheet][2].ToString();
                _FileName = _FileName.Replace("$", "");
                //_log.SuccessLoggingFileExcel(_FileName, _Meesage, Desitnation);
            }
            // _log.SuccessLogging("UFL", "Mesage from the Success Logging From the user");  

            //return "Successessfully Parsed";

        }
        catch (Exception ex)
        {
            //Logging _lo = new Logging();
            //_log.LogErrorLog("Exception", ex);
        }
    }

Open in new window

Commented:
try the top connection string:

http://www.connectionstrings.com/excel-2007
Michael SterlingWeb Applications Developer

Author

Commented:
@disrupt: in the first suggestion (of the last 3, i installed the "2007 Office System Driver: Data Connectivity Components" as suggested and changed my query string to look as it does in the example(s) on the page and now i'm getting an: "External table is not in the expected format." exception...
Michael SterlingWeb Applications Developer

Author

Commented:
@disrupt: never mind that last statement, i got it fixed, i had to try several connection strings and variations but what seemed to work was that I, simply, needed to remove the "Xml;HDR=YES" portion at the end of my connection string for Excel 2010. I apparently had it there each time in all of my attempts and variations...it seems to work fine now...for both ".xls" and ".xlsx" extensions...thanks again for your help.
Michael SterlingWeb Applications Developer

Author

Commented:
thanks for your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial