none
Excel Query Issues RRS feed

  • Question

  • Helloo,

    Please help me to solve my issues regarding excel queries...

    I have applied the below query to retrieve whole records from excel sheet..

    It contains total 100 records in that sheet, but when executing the query it gets only 73 records..

    Here is my query..

    Select * From [Sheet1$] wHERE SLNO IS NOT NULL AND BARCODE IS NOT NULL..

    Actually i found the issues, its due to some of the records in BARCODE  field is numeric and some fields are string

    A screenshot attached here for more reference,

    in the below image , the records which are marked in yellow color is not getting when we executing the above query..

    Please help me to solve the issues...

    Thanks in advance...

    Thursday, May 24, 2018 11:03 AM

All replies

  • Hello raj.sibin,

    Since it is due to different data type in same field, you could try to format all the cells in the field as string.

    If you do not want to change your original sheet. You could try to copy the whole sheet1 to a temporary sheet and then do all your operation to the temporary sheet. At last, you could delete the temporary sheet.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 25, 2018 2:19 AM
  • Thanks for your valuable information...

    Is there any option to handle this in query?

    Is there any option to retrieve whole data in same data type( string)..?

    I have to solve this in code behind, actually am expecting code behind solutions...

    Friday, May 25, 2018 4:39 AM
  • Hello raj.sibon,

    Are you using ADO.net to connect to Excel to query data? If so, please try to set IMEX=1 in your connection string, that will help you  retrieve data for mixed data columns.

    Just like.

    objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Users\admin\Desktop\Book1.xlsx;" & _
            "Extended Properties=""Excel 12.0;HDR=Yes; IMEX=1;"";"

    Best regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 25, 2018 5:44 AM
  • Dear Sir,

    Thanks for your reply...

    This is my current connection string which i applied in c# code...

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Watches.xls;Extended Properties='Excel 8.0;HDR=YES;';

    and as per your instructions , I have applied your code in my connection string as below

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Watches.xls;Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';

    But there is no other change, it still remains the same issues...

    Please suggest me any other methods if you knows...

    I hope , you can help me..

    Friday, May 25, 2018 6:44 AM
  • Hello raj.sibin,

    I'm wondering why IMEX does not work for you. It does work for me.

    What application are you developing? I would suggest you share a simply project and the source workbook so we could use them to reproduce your issue.

    For sharing the file, you could share it via cloud storage, such as One Drive, and then put the line address here.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 25, 2018 9:16 AM
  • Dear Sir,

    Am developing the application in C# code and here am attaching the link for downloading the source code

    including excel file also..

    https://www.sendspace.com/file/cn6lkg

    Please try this and let me know if you feel any difficulties in downloading the files..

    Saturday, May 26, 2018 4:37 AM
  • Hello raj.sibin,

    I could reproduce your issue now. It is strange that I could correctly return both text data and number data in my own workbook but failed to return them from your shared workbook.

    I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.

    Sorry for any inconvenience and have a nice day! 

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 28, 2018 9:05 AM
  • Thanks sir,

    I will wait , I don't have any other options to solve this issues. I have tried many methods but i didn't find any solutions

    Thanks in advance and i hope you can help me..

    Monday, May 28, 2018 11:13 AM
  • Hi raj.sibin,

    The file format in your scenario is an old format(xls), after I change it to the xlsx, setting IMEX=1 works for me well. I also suggest that you update the Office to the latest version.

    Please let me know if you still have the problem.

    Regards & Fei


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Tuesday, May 29, 2018 5:51 AM
    Moderator
  • I have tried the method which you mentioned above..

    Created new excel in latest version and copy all the content from previous excel file..

    updated the connection string as below...

    String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                        txtfile.Text +
                                         ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';";

    but same issues remains, there is no other change, am getting partial records only...

    Tuesday, May 29, 2018 9:16 AM
  • Hi Raj.sibin,

    Thanks for sharing this info. I can fix this issue by using the connection string like below:

     String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Watches.xlsx;Extended Properties='Excel 12.0 xml;HDR=YES;IMEX=1;';";

    And modify the registry entry TypeGuessRows to a large value(100) then the index of rows appear text in the spreadsheet you provided. This path of registry entry is depend on which version of ACE engine you installed.  For example, I installed 32-bit version Microsoft Access Database Engine 2016 Redistributable, then the path should be like:

    Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows

    Another workaround is that you can modify the first top 8 rows of barcode to add ' char before the value to made the column recognized as text column instead of numeric.

    Please feel free to let me know if you still have the problem.

    Regards & Fei


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 30, 2018 5:47 AM
    Moderator