none
Query in excel sheet for same sheet RRS feed

  • Question

  • Hi

    I have a excel sheet and it has near to 7 Lakh records . I want to write SQL Query to pull the same with some aggregate function and condition so that it reduces to max within 10thousand rows .

    Below is my code for the same . But I only get 65 thousand data into query for the conditions I have used.

    ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
                    & ";Extended Properties=""Excel 12.0 ;HDR=Yes"""
    
       
    
     sSQL = " SELECT Count(*) FROM [RawData$A:AP] "
    
    
       Debug.Print sSQL
        res.Open sSQL, ConString
    
            ThisWorkbook.Worksheets("SheetData").Range("A2").CopyFromRecordset res
    
        res.Close
      Application.StatusBar = False
    

    If someone had faced similar issue , kindly let me know how to get nearly 7 Lakh record queried in excel and get data.


    Thanks and Regards Avinash Desai *** The perfect project plan is possible if one first documents a list of all the unknowns. ***

    Tuesday, October 11, 2016 4:58 AM

Answers

  • Cool

    Tried more and got it solved .. A small error below

    Query I was using

    SELECT Count(*) FROM [RawData$A:AP]

    Modified query

    SELECT Count(*) FROM [RawData$]


    Thanks and Regards Avinash Desai *** The perfect project plan is possible if one first documents a list of all the unknowns. ***

    • Marked as answer by Avinash Desai Tuesday, October 11, 2016 5:53 AM
    Tuesday, October 11, 2016 5:53 AM

All replies

  • Cool

    Tried more and got it solved .. A small error below

    Query I was using

    SELECT Count(*) FROM [RawData$A:AP]

    Modified query

    SELECT Count(*) FROM [RawData$]


    Thanks and Regards Avinash Desai *** The perfect project plan is possible if one first documents a list of all the unknowns. ***

    • Marked as answer by Avinash Desai Tuesday, October 11, 2016 5:53 AM
    Tuesday, October 11, 2016 5:53 AM
  • sSQL = " SELECT Count(*) FROM [RawData$] "

    Above can help you

    It is only allowing rows upto 65536.Below is working normally

    RawData$A1:A65536] "

    It is not even allowing if the range is given "Name".


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Tuesday, October 11, 2016 5:55 AM
    Answerer
  • It is good to see you already solved when I was finding answer. All the best.

    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Tuesday, October 11, 2016 3:19 PM
    Answerer