Solved Query to delete rows from import with no data

[thedivvy]

Hi, I have a shed load of tables to import from Excel because a user has seriously overused Excel to do a database's job (yet again ). The individual sheets all have every date as place holders for the formulas so the rows with only a date are of course irrelevant in access. I need to identify rows in the tables created with only Key ID and Date and no other data so I can delete them and retain only "real" data. Can someone help please as I'm running into issues, probably I guess because of blank fields (Nulls?)
Oh, and each sheet has 86 columns!
Many thanks

 

[theDBguy]

Hi, I have a shed load of tables to import from Excel because a user has seriously overused Excel to do a database's job (yet again ). The individual sheets all have every date as place holders for the formulas so the rows with only a date are of course irrelevant in access. I need to identify rows in the tables created with only Key ID and Date and no other data so I can delete them and retain only "real" data. Can someone help please as I'm running into issues, probably I guess because of blank fields (Nulls?)
Oh, and each sheet has 86 columns!
Many thanks

Hi. Not sure I follow. Are you able to post sample spreadsheet?

 

[Uncle Gizmo]

Hi. Not sure I follow. Are you able to post sample spreadsheet?

I agree with DBguy, more info required...

 

[vba_php]

I need to identify rows in the tables created with only Key ID and Date and no other data so I can delete them and retain only "real" data.

if all of your tables have a pattern to them whereby you KNOW that all other fields other than key id and date are blank if FIELD 'X' is blank, you can do this:

DELETE * FROM TABLE WHERE

[KEY ID] IS NOT NULL AND [DATE] IS NOT NULL AND 

(([FIELD 'X'] IS NULL OR [FIELD 'X'] = ""))

if you can't recognize a pattern like that, more than likely you will have to code the solution because the query builder isn't sophisticated enough.

 

[arnelgp]

you can create a query, don't include the ID and the Date field:

delete from * from yourTable where (field1 + field2 + field3 + field4 + …) Is Null

or better yet create a UDF or sub that will do the deletion:

Private sub test_324234()
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim strSQL As String
Dim strCriteria As String

strSQL = "Delete * From yourTable Where "
Set rs = Currentdb.OpenRecordSet("Select * From yourTable Where (1=0);")
For Each fld In rs.Fields
    If fld.Name <> "ID" And fld.Name <> "Date" Then
        strCriteria = " + " & fld.Name & strCriteria
    End If
Next
rs.Close
Set rs = Nothing
strCriteria = Mid(strCriteria, 4)
strSQL = strSQL & "(" & strCriteria & ") Is Null;"
DoCmd.RunSQL strSQL
End Sub

 

[The_Doc_Man]

One issue to consider is that you might want to do the operation in stages.

First, import one of those sheets to a temporary table.

Next, now that it has been imported, move the original to a holding area so that you know it is done.

Then, examine the table to determine the pattern you get that would let you delete the "empty" rows.

Then, delete the rows identified in the pattern, whatever it is.

Then, append what is left into the table where you want to keep everything.

Finally, delete the remnants of the staging table.

Rinse, lather, repeat.

After you have repeated the first three steps a number of times, you will build up an appreciation of the identifying pattern and will be able to do this faster. Depending on the precise meaning of "shed load" you might then be able to write code to look for the remainder of the files and fully automate this process. If this is something you want to do, you need to look up

FileSystemObject object

Office VBA reference topic

docs.microsoft.com

Files collection

Office VBA reference topic

docs.microsoft.com

The reason I didn't point you towards a method of removing the unwanted rows directly from Excel is that it would be harder to do that. You would have to open an Excel Application Object because if you mapped the worksheet as though it were a table, I don't think you can alter the contents of the worksheet (depending perhaps on your version of Office.)

Note also that when you are done with this, you will need to do a compact & repair on the database because deleting wholesale amounts of records like that leads to something called "bloat" - which isn't nice.

 

[thedivvy]

Hi folks, thank you for your replies, I'm attaching an Excel sheet exported from a table I created and tidied up that needs the "no data" lines deleting. As you will see the dates are contiguous and naturally include weekends which are highly likely to not have data. Naturally, customers don't order every day so depending on the customer (this sheet is for only one customer) there may be an order every week day or perhaps only a few a month, or anything in between. There's nominally 7 years of data for each customer which will allow reports showing trends, cycles, etc. which will be useful although I expect to set up an archiving system to retain a max 5 year period.

@arnelgp thanks, I'll give your suggestions a try later, although I might need to pick your brains a bit to do so if that's ok?

@The_Doc_Man thanks, although I'm starting with Excel imports I am already doing as you suggest (stages) as I agree it's easier in Access. Thanks for the reminder about repairing, I'd forgotten about that. It's been 9 years since I did anything on databases so back on a steep learning/remembering curve

Regarding any "pattern" if folk mean any kind of uniform repeat in the data I'm certain it will not exist so I'm looking for a method that will cover all variations but specifically only delete a date row if there are no numbers in the columns D, DH, etc. (They're types of product with quantity ordered so always integers)

I have 45 sheets (customers) to do, each customer has 3 worksheets (1 per ~2 year period) (total 135 sheets). I'm currently importing the 3 sheets for one customer at a time, integrating them and then creating one table per customer meaning approx 2,600 records with 86 data columns per customer, then deleting redundant tables/queries and on to the next...

Once I have finished sanitising the incoming data I will create a table that will include the data from all the customers e.g.

OrderNo
LineNo
DateID
CustomerID
ProductID
Quantity

With OrderNo and LineNo a combined Primary Key

I really appreciate everyone's input
Many thanks

 

[ebs17]

Power Query has been available in excel itself since version 2010. There is a convenient unpivot functionality with which you can refurbish the worksheets before you import them or link them for data transfer.

The data model should then be ready in the database itself before you think of any data transfer.

This could help later:
Dissolve import table in many-to-many relationship

 

[isladogs]

I would also suggest importing to a temporary staging table.
However I would omit the delete part of the process. Its unnecessary.
Instead just append those records in the temporary table whose records match the specified criteria.
The remaining records can just be ignored.
Once the import has been completed, delete the temp table

 

[The_Doc_Man]

True enough, Colin. My only reason for the separate DELETE step was to allow determination of the pattern for the deletion. You could also just do a SELECT query to see if you got only good data and use that select to become the back half of an INSERT INTO sequence.

 

[arnelgp]

on the sample there is a user-define function areFieldsNull() which is
called from the Delete Query (query1) to check if all fields (excluding ID and Date field)
are Null.

check Query1 on Design View.

 

[thedivvy]

on the sample there is a user-define function areFieldsNull() which is
called from the Delete Query (query1) to check if all fields (excluding ID and Date field)
are Null.

check Query1 on Design View.

Hi arnelgp,
Thank you so much for this, your solution is exactly what I wanted and done so elegantly, I can easily use this method with confidence as my data will not risk corruption via human error.

To everyone else who has contributed a big thank you for your time.