none
[Excel] Running a SQL Query to delete rows RRS feed

  • Question

  • Hello Experts,

    Background: I am attempting to use a dba of my companies time keeping system and implement it with Power BI tools. Given the file size restrictions within Power Bi itself I need to lower my file size by removing all time logs from inactive employees.

    I have a question regarding whether or not you can use a sql query to delete rows in excel. I have roughly 200,000+ rows in my excel spreadsheet. I am attempting to delete all rows where an employee equals inactive. I have attempted to delete these rows by sorting them and doing a bulk delete and clear contents, but it seems to crash my excel every time.  My thought process is that using a query that does a timed delete might put less of a burden on deleting the massive amount of data.

    I would like to use this: DELETE * FROM [Table_(...)_Actual$] WHERE [Current] = "Inactive" (Will add more once I know it is possible to use sql queries in Excel.

    Any information on whether or not this is possible would be appreciated.

    Regards,

    Link

    Monday, April 28, 2014 5:23 PM

Answers

  • Running SQL Query in Excel is possible, however, the delete query is not supported in Excel.
    You are more restricted in deleting Excel data than   data from a relational data source. In a relational database, "row" has no   meaning or existence apart from "record"; in an Excel worksheet, this is not   true. You can delete values in fields (cells). Please see:http://support.microsoft.com/kb/257819/en-us
    One workaround : Use update query to set the rows as null, then use select query.
    e.g. 

    SQL = "update [sheet2$A1:B5] set name=null,age=null where name='andy'"
    cnn.Execute SQL
    SQL = "select name,age from [sheet2$A1:B5] where name is not null"

    Wind Zhang
    TechNet Community Support

    Tuesday, April 29, 2014 3:25 AM
    Moderator

All replies

  • Running SQL Query in Excel is possible, however, the delete query is not supported in Excel.
    You are more restricted in deleting Excel data than   data from a relational data source. In a relational database, "row" has no   meaning or existence apart from "record"; in an Excel worksheet, this is not   true. You can delete values in fields (cells). Please see:http://support.microsoft.com/kb/257819/en-us
    One workaround : Use update query to set the rows as null, then use select query.
    e.g. 

    SQL = "update [sheet2$A1:B5] set name=null,age=null where name='andy'"
    cnn.Execute SQL
    SQL = "select name,age from [sheet2$A1:B5] where name is not null"

    Wind Zhang
    TechNet Community Support

    Tuesday, April 29, 2014 3:25 AM
    Moderator
  • Thank you for getting back to me on my question. I will look over your suggestions.

    -Link

    Tuesday, April 29, 2014 1:17 PM
  • You may try this:

    Create another table (Worksheet) on the same file and move the records needed

    SELECT * INTO Table2

    FROM Table1 WHERE Employee <> 'Inactive'

    Let me know if that works for you

    Sunday, July 22, 2018 3:04 PM