locked
What is the connection string to write for importing mixed data from excel sheet to data grid RRS feed

  • Question

  • Please help me about this issue, so that I ll be glad to u.I used IMEX=1.but it is working for some limited rows
    Tuesday, October 24, 2006 3:38 AM

Answers

  • Hi,

    check out if this works

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
    "HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
    "IMEX=1;" tells the driver to always read "intermixed" data columns as text. Note that this option might affect excel sheet write access negative.
    TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.
    TIP! Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance.
    Important note! The two double quota ("") in the string are escaped quotas (VB syntax), you may have to change this to your language specific escape syntax (ex. \") or maybe single quota (').

    Best Regards

    Tuesday, October 24, 2006 3:42 AM
  • Hi Luis,

    Thanks a lot for ur response.actually my client wants to not to modify the registry key  TypeGuessRows value due to some security purpose.Is there any other way for overriding TypeGuessRows.I also referred Microsoft msdn and used MaxScanRows =0 in extended property to scan all the rows but it has no effect .Have u any idea about this .

     

    Thanks & Regards,

    Manoj

    Tuesday, October 24, 2006 4:27 AM
  • Hi Manoj,

    Maybe this link will explain better

    best regards

    Tuesday, October 24, 2006 5:00 AM
  • Hi Luis,

    Thanks a lot for giving valuable tips.

    Regards,

    Manoj

    Tuesday, October 24, 2006 6:22 AM
  • Just an addition. You should be careful in acase if Excel file contains some dates. If you use IMAX=1, then dates could be returned as double-precirion numbers, not as a sting representation of the dates. There is no workaround for this in Jet
    Tuesday, October 24, 2006 10:20 AM
  • Hi Mazur,

    Thanks for ur response.My problem is, I have rows like

    1

    2

    3

    4

    5

    6

    7

    8

    9

    a

    b

    c

    d

    in excel sheet.But when I imported these rows only 1-9 imported succesfully but a-d ignored. I used IMEX=1,MaxScanRows=0 in Extended property. but no effect.Can u plz help me about this issue if u have any other idea with out changing the value of TypeGuessRows registry key from 8 to 0.

    With Regards,

    Manoj

    Tuesday, October 24, 2006 10:55 AM

  • I can't reproduce this problem. When I use IMEX in the connection string the mixed mode columns display just fine. Could you post your code?
    Tuesday, October 24, 2006 1:04 PM
  • Hi Paul,

    IMEX=1 is working fine if column of excel sheet contains as below

    1

    2

    3

    4

    5

    6

    7

    8

    a

    b

    c

    d

    here imported successfully bcz upto 8 rows ,only numeric value.But problem occurs when only numeric values present in more than 8 rows as below

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    a

    b

    c

    d

    Here 1-10 are imported successfully but a-d are ignored.This problem occurs bcz the default machine value of registry key TypeGuessrows=8.If i change this value to 0 means no problem.But my client wants to not to change the registry key value.

    Please see my sample code as below:-

    private const string EXCEL_CONNECTION_STRING_1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";

    private const string EXCEL_CONNECTION_STRING_2 = ";Extended Properties=\"Excel 8.0;;HDR=No;IMEX=1;MaxScanRows=0\"";

    private String[] GetExcelSheetNames(string excelFile)

    {

    OleDbConnection objConn = null;

    System.Data.DataTable dt = null;

    try

    {

    // Connection String.

    // Change the excel file to the file you will search.

    String connString = EXCEL_CONNECTION_STRING_1 + excelFile + EXCEL_CONNECTION_STRING_2;

    // Create connection object by using the preceding connection string.

    objConn = new OleDbConnection(connString);

    // Open connection with the database.

    objConn.Open();

    // Get the data table containg the schema guid.

    dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

    if (dt == null) { return null; }

    String[] excelSheets = new String[dt.Rows.Count];

    int i = 0;

    // Add the sheet name to the string array.

    foreach (DataRow row in dt.Rows)

    {

    excelSheetsIdea = row[TABLE_NAME].ToString(); i++;

    }

    return excelSheets;

    }

    catch (Exception)

    {

    return null;

    }

    finally

    {

    // Clean up.

    if (objConn != null)

    {

    objConn.Close();

    objConn.Dispose();

    }

    if (dt != null)

    {

    dt.Dispose();

    }

    }

    }

    private DataSet GetImportedData(string fileName)

    {

    try

    {

    string[] sheetNames = GetExcelSheetNames(fileName);

    if (sheetNames == null || sheetNames.Length == 0)

    {

    _alertMgr.ShowError(this.FindForm(), MessageLibrary.CouldNotImportFromCorruptedExcelFile);

    return null;

    }

    DataSet importedData = new DataSet();

    // Construct the connection string for the oledb connection . . .

    string connectionString = EXCEL_CONNECTION_STRING_1 + fileName + EXCEL_CONNECTION_STRING_2;

    OleDbConnection connection = new OleDbConnection(connectionString);

    // Select the data from Sheet1 of the workbook . . .

    OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + sheetNames[0] + "]", connection);

    adapter.Fill(importedData);

    return importedData;

    }

    catch (OleDbException exp)

    {

    _alertMgr.ShowError(this.FindForm(), exp.Message);

    return null;

    }

    catch (Exception exp)

    {

    _alertMgr.ShowError(this.FindForm(), exp.Message);

    return null;

    }

    }

    Thanks & Regards,

    Manoj

    Tuesday, October 24, 2006 1:18 PM

  • OK, I was able to repro the problem after changing TypeGuessRows in my Registry to a value of 8.

    From what I have been able to determine, if all of the first eight rows (TypeGuessRows) are numeric then IMEX and ImportMixedTypes (Text) are ignored. The column data type will be determined to be numeric and any non-numeric values are converted to Null.

    The only way I've been able to work around this issue (w/o changing TypeGuessRows in the Registry) is to put an alphanumeric value in the column of one of the first eight rows, or format the column in one of the first eight rows, using Excel, as Text (number stored as text) and then enter the value. If you format the column as Text (Format...Cells...Text) and then enter the value, it appears left justified with a comment indicating that it is a number stored as text.

    Tuesday, October 24, 2006 5:58 PM
  • Hi Paul,

    Many many thanks for helping about this issue.If u have any other idea without formating the cells of excel sheet to text ,then please let me know.

    thanks & Regards,

    Manoj

    Wednesday, October 25, 2006 4:52 AM

  • Formatting a column would require Excel (and automation). Here's some code I generated with the VBA macro recorder that will change the format of a column to "number stored as text":

    strCellValue = Range("A1").Value
    Range("A1").NumberFormat = "@"
    Range("A1").Value = CStr(strCellValue)

    A1 refers to the first column in the first row. CStr is a VB function but you can replace it with Convert.ToString if you're using C#. You can find C# automation examples for Excel in the below links:

    How to automate Microsoft Excel from Microsoft Visual C# .NET
    How to automate Excel by using Visual C# to fill or to obtain data in a range by using arrays

    Wednesday, October 25, 2006 12:40 PM
  • Hi Paul,

    Many many thanks.I ll check this tomoroow.Now just office work finished.Again thanks for ur cooperation

    with regards,

    Manoj

    Wednesday, October 25, 2006 12:56 PM
  • Hi Paul,

    Have u any idea about how to use MaxScanRows in code .actually in a msdn article , it has specified that ,with out changing the registry key typeGuessRows value ,we can use MaxScanRows=0 in extended Property to override TypeGuessRows.accordingly,I also used MaxScanRows=0.But there is no effect by using this

    Regards,

    Manoj

    Thursday, October 26, 2006 5:32 AM
  • I believe if you specify MaxScanRows=0 inside of the Extended Properties of the connection string, then it suppose to work. Microsoft suggests to do it that way in next KB

    http://support.microsoft.com/kb/278973/en-us

    Thursday, October 26, 2006 10:21 AM
  • Hi VMazur,

    I also use MaxScanRows=0 in my Extended property but I think it is not working due to my Excel version.i m using Excel 2003.is it so?

    Regards,

    Manoj

    Thursday, October 26, 2006 10:44 AM

  • AFAIK MaxScanRows is ignored if specified in the connection string.
    Thursday, October 26, 2006 11:56 AM
  • Hi Paul,

    Please see my below statements,where I used MaxScanrows.But it didn't work

    private const string EXCEL_CONNECTION_STRING_1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";

    private const string EXCEL_CONNECTION_STRING_2 = ";Extended Properties=\"Excel 8.0;;HDR=No;IMEX=1;MaxScanRows=0\"";

    with regards,

    Manoj

    Thursday, October 26, 2006 1:00 PM
  •  I got the same problem did you found the solution? If please send me the solution

    Thanks in Advance
    Wednesday, January 21, 2009 7:18 AM
  • As I mentioned previously I don't believe that MaxScanRows will work in the connection string. You probably need to change TypeGuessRows in the Registry to 0 (if your Worksheet doesn't contain a lot of data).

    http://support.microsoft.com/default.aspx/kb/189897
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, January 21, 2009 2:24 PM

All replies

  • Hi,

    check out if this works

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
    "HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
    "IMEX=1;" tells the driver to always read "intermixed" data columns as text. Note that this option might affect excel sheet write access negative.
    TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.
    TIP! Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance.
    Important note! The two double quota ("") in the string are escaped quotas (VB syntax), you may have to change this to your language specific escape syntax (ex. \") or maybe single quota (').

    Best Regards

    Tuesday, October 24, 2006 3:42 AM
  • Hi Luis,

    Thanks a lot for ur response.actually my client wants to not to modify the registry key  TypeGuessRows value due to some security purpose.Is there any other way for overriding TypeGuessRows.I also referred Microsoft msdn and used MaxScanRows =0 in extended property to scan all the rows but it has no effect .Have u any idea about this .

     

    Thanks & Regards,

    Manoj

    Tuesday, October 24, 2006 4:27 AM
  • Hi Manoj,

    Maybe this link will explain better

    best regards

    Tuesday, October 24, 2006 5:00 AM
  • Hi Luis,

    Thanks a lot for giving valuable tips.

    Regards,

    Manoj

    Tuesday, October 24, 2006 6:22 AM
  • Just an addition. You should be careful in acase if Excel file contains some dates. If you use IMAX=1, then dates could be returned as double-precirion numbers, not as a sting representation of the dates. There is no workaround for this in Jet
    Tuesday, October 24, 2006 10:20 AM
  • Hi Mazur,

    Thanks for ur response.My problem is, I have rows like

    1

    2

    3

    4

    5

    6

    7

    8

    9

    a

    b

    c

    d

    in excel sheet.But when I imported these rows only 1-9 imported succesfully but a-d ignored. I used IMEX=1,MaxScanRows=0 in Extended property. but no effect.Can u plz help me about this issue if u have any other idea with out changing the value of TypeGuessRows registry key from 8 to 0.

    With Regards,

    Manoj

    Tuesday, October 24, 2006 10:55 AM

  • I can't reproduce this problem. When I use IMEX in the connection string the mixed mode columns display just fine. Could you post your code?
    Tuesday, October 24, 2006 1:04 PM
  • Hi Paul,

    IMEX=1 is working fine if column of excel sheet contains as below

    1

    2

    3

    4

    5

    6

    7

    8

    a

    b

    c

    d

    here imported successfully bcz upto 8 rows ,only numeric value.But problem occurs when only numeric values present in more than 8 rows as below

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    a

    b

    c

    d

    Here 1-10 are imported successfully but a-d are ignored.This problem occurs bcz the default machine value of registry key TypeGuessrows=8.If i change this value to 0 means no problem.But my client wants to not to change the registry key value.

    Please see my sample code as below:-

    private const string EXCEL_CONNECTION_STRING_1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";

    private const string EXCEL_CONNECTION_STRING_2 = ";Extended Properties=\"Excel 8.0;;HDR=No;IMEX=1;MaxScanRows=0\"";

    private String[] GetExcelSheetNames(string excelFile)

    {

    OleDbConnection objConn = null;

    System.Data.DataTable dt = null;

    try

    {

    // Connection String.

    // Change the excel file to the file you will search.

    String connString = EXCEL_CONNECTION_STRING_1 + excelFile + EXCEL_CONNECTION_STRING_2;

    // Create connection object by using the preceding connection string.

    objConn = new OleDbConnection(connString);

    // Open connection with the database.

    objConn.Open();

    // Get the data table containg the schema guid.

    dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

    if (dt == null) { return null; }

    String[] excelSheets = new String[dt.Rows.Count];

    int i = 0;

    // Add the sheet name to the string array.

    foreach (DataRow row in dt.Rows)

    {

    excelSheetsIdea = row[TABLE_NAME].ToString(); i++;

    }

    return excelSheets;

    }

    catch (Exception)

    {

    return null;

    }

    finally

    {

    // Clean up.

    if (objConn != null)

    {

    objConn.Close();

    objConn.Dispose();

    }

    if (dt != null)

    {

    dt.Dispose();

    }

    }

    }

    private DataSet GetImportedData(string fileName)

    {

    try

    {

    string[] sheetNames = GetExcelSheetNames(fileName);

    if (sheetNames == null || sheetNames.Length == 0)

    {

    _alertMgr.ShowError(this.FindForm(), MessageLibrary.CouldNotImportFromCorruptedExcelFile);

    return null;

    }

    DataSet importedData = new DataSet();

    // Construct the connection string for the oledb connection . . .

    string connectionString = EXCEL_CONNECTION_STRING_1 + fileName + EXCEL_CONNECTION_STRING_2;

    OleDbConnection connection = new OleDbConnection(connectionString);

    // Select the data from Sheet1 of the workbook . . .

    OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + sheetNames[0] + "]", connection);

    adapter.Fill(importedData);

    return importedData;

    }

    catch (OleDbException exp)

    {

    _alertMgr.ShowError(this.FindForm(), exp.Message);

    return null;

    }

    catch (Exception exp)

    {

    _alertMgr.ShowError(this.FindForm(), exp.Message);

    return null;

    }

    }

    Thanks & Regards,

    Manoj

    Tuesday, October 24, 2006 1:18 PM

  • OK, I was able to repro the problem after changing TypeGuessRows in my Registry to a value of 8.

    From what I have been able to determine, if all of the first eight rows (TypeGuessRows) are numeric then IMEX and ImportMixedTypes (Text) are ignored. The column data type will be determined to be numeric and any non-numeric values are converted to Null.

    The only way I've been able to work around this issue (w/o changing TypeGuessRows in the Registry) is to put an alphanumeric value in the column of one of the first eight rows, or format the column in one of the first eight rows, using Excel, as Text (number stored as text) and then enter the value. If you format the column as Text (Format...Cells...Text) and then enter the value, it appears left justified with a comment indicating that it is a number stored as text.

    Tuesday, October 24, 2006 5:58 PM
  • Hi Paul,

    Many many thanks for helping about this issue.If u have any other idea without formating the cells of excel sheet to text ,then please let me know.

    thanks & Regards,

    Manoj

    Wednesday, October 25, 2006 4:52 AM

  • Formatting a column would require Excel (and automation). Here's some code I generated with the VBA macro recorder that will change the format of a column to "number stored as text":

    strCellValue = Range("A1").Value
    Range("A1").NumberFormat = "@"
    Range("A1").Value = CStr(strCellValue)

    A1 refers to the first column in the first row. CStr is a VB function but you can replace it with Convert.ToString if you're using C#. You can find C# automation examples for Excel in the below links:

    How to automate Microsoft Excel from Microsoft Visual C# .NET
    How to automate Excel by using Visual C# to fill or to obtain data in a range by using arrays

    Wednesday, October 25, 2006 12:40 PM
  • Hi Paul,

    Many many thanks.I ll check this tomoroow.Now just office work finished.Again thanks for ur cooperation

    with regards,

    Manoj

    Wednesday, October 25, 2006 12:56 PM
  • Hi Paul,

    Have u any idea about how to use MaxScanRows in code .actually in a msdn article , it has specified that ,with out changing the registry key typeGuessRows value ,we can use MaxScanRows=0 in extended Property to override TypeGuessRows.accordingly,I also used MaxScanRows=0.But there is no effect by using this

    Regards,

    Manoj

    Thursday, October 26, 2006 5:32 AM
  • I believe if you specify MaxScanRows=0 inside of the Extended Properties of the connection string, then it suppose to work. Microsoft suggests to do it that way in next KB

    http://support.microsoft.com/kb/278973/en-us

    Thursday, October 26, 2006 10:21 AM
  • Hi VMazur,

    I also use MaxScanRows=0 in my Extended property but I think it is not working due to my Excel version.i m using Excel 2003.is it so?

    Regards,

    Manoj

    Thursday, October 26, 2006 10:44 AM

  • AFAIK MaxScanRows is ignored if specified in the connection string.
    Thursday, October 26, 2006 11:56 AM
  • Hi Paul,

    Please see my below statements,where I used MaxScanrows.But it didn't work

    private const string EXCEL_CONNECTION_STRING_1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";

    private const string EXCEL_CONNECTION_STRING_2 = ";Extended Properties=\"Excel 8.0;;HDR=No;IMEX=1;MaxScanRows=0\"";

    with regards,

    Manoj

    Thursday, October 26, 2006 1:00 PM
  •  I got the same problem did you found the solution? If please send me the solution

    Thanks in Advance
    Wednesday, January 21, 2009 7:18 AM
  • As I mentioned previously I don't believe that MaxScanRows will work in the connection string. You probably need to change TypeGuessRows in the Registry to 0 (if your Worksheet doesn't contain a lot of data).

    http://support.microsoft.com/default.aspx/kb/189897
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, January 21, 2009 2:24 PM