none
writing sql in Excel RRS feed

  • Question

  • Hi Team,

    I have an excel file which contains nearly 100,000 rows and 122 columns of data,  I am finding it difficult to produce required data with said number of conditions, I am trying to write sql to retrieve data based on said conditions and send the data to another sheet.  I have written code to validate data and do accordingly, but I thought, it will be easier to do with SQL than writing lengthy code.

    Can any one please help me as to how, I can write Sql statement in Excel to get data based on said conditions.
    I have been searching this, long since, but couldnot get right link.

    If you have any links, please do help me. Thanking you in advance.

    With regards
    Repath Athyala


    Love the Love that Loves the Love and Hate the Love that Loves the Hate.

    Wednesday, September 30, 2015 5:40 AM

All replies

  • I have posted a few links.  I have used this method a lot.  It is pretty straight forward and you can make the sql very complex like you can do subqueries.  The only problem I have had is it infers the data type from the first few rows so I have had to create dummy rows so it will infer the correct type.

    http://www.fontstuff.com/ebooks/free/fsADOConnectExcel.pdf
    https://support.microsoft.com/en-us/kb/257819
    https://support.microsoft.com/en-us/kb/247412
    https://social.msdn.microsoft.com/Forums/en-US/50c36efb-32cd-4402-80cd-eb92721a70a1/update-excel-table-with-sql?forum=isvvba

    I have found managing a lot of data in Excel to be cumbersome.  You might want to consider using an Access database to be easier and more reliable.

    Here is a sample connection string for Excel 2007/2010.  I think the links are 2003.

        conStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                     "Data Source=" & dataWkBk & ";" & _
                     "Extended Properties=""Excel 12.0;HDR=YES;"""
    

    • Edited by mogulman52 Wednesday, September 30, 2015 2:02 PM
    Wednesday, September 30, 2015 1:56 PM
  • That's a lot of data.  Excel is probably choking on that volume of data.  Can you dump everything into Access and pull what you need into Excel?  I think you'll see a huge improvement in performance!!  Please see these links for some ideas of how to proceed.

    http://www.erlandsendata.no/english/index.php?d=envbadacimportado

    http://www.erlandsendata.no/english/index.php?d=envbadacimportdao

    http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

    http://translate.google.pl/translate?js=n&prev=_t&hl=pl&ie=UTF-8&layout=2&eotf=1&sl=pl&tl=en&u=http%3A%2F%2Fafin.net%2FKsiazkaSQLwExcelu%2FGraficznyEdytorZapytanSqlNaPrzykladzieMsQuery.htm

    Keep in mind, there are MANY ways to move data back and forth between Access and excel.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, October 10, 2015 2:03 PM
  • I used the SQL technique on Excel data for an optimization algorithm so I was doing queries over and over again on the data.  I only had about 20,000 to 30,000 records and about 64 columns of data.  It was amazingly fast and consumed a very small percent of the time.  In my case it didn't make sense to use Access since the data was very dynamic during the optimization process.  Working with a lot of data in Excel can be cumbersome and prone to errors and crashes.  Plus sharing data is difficult.  Using a regular database is a much better solution.  If you plan to use the web to interact with the data you should look at SQL Server or MySQL.  Access will work and I am using it in one application but it really wasn't designed for the web.
    Sunday, October 11, 2015 12:44 PM