UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Removing Blank Data Records From An Excel Imported File, Access 2010    
 
   
BuzyG
post Apr 30 2013, 09:33 AM
Post#1



Posts: 268
Joined: 20-September 12
From: Cornwall UK


I've been having fun for the past few hours trying to solve one of those realy simple looking problems. I suspect many on here will have seen this before. Much Googling has not come up with a simple solution yet.
I have an table imported from Excel, that may contain many blank records. what is the simplest way to:- not import / delete the Blanks?
I've set the field to required, makes no difference.
At present, after the import, I'm converting the data, from the input field, to a string variable. this generates a null error, for any blank data, which I am error trapping. Then I Delete the record and resume were I left off. This works but it's a horrible looking piece of code.
Looking for elegant, VBA coded solution, from the distiguished forum guru's
Go to the top of the page
 
dflak
post Apr 30 2013, 09:55 AM
Post#2


Utter Access VIP
Posts: 5,972
Joined: 22-June 04
From: North Carolina


Just throwing this out there, but can't you solve this on the Excel side? Sorting the rows will put all blank rows on the bottom where the import mechanizm should see them as end of file.
Go to the top of the page
 
doctor9
post Apr 30 2013, 10:15 AM
Post#3


UtterAccess Editor
Posts: 17,102
Joined: 29-March 05
From: Wisconsin


BuzyG,
f, after import, your table includes several blank records, why not just run a simple Delete query on that table to remove all of the blanks. No error trapping, just "if this required field is blank, delete the record" sort of logic.
Hope this helps,
Dennis
Go to the top of the page
 
BuzyG
post Apr 30 2013, 04:07 PM
Post#4



Posts: 268
Joined: 20-September 12
From: Cornwall UK


Dan, I do this when I have control of the data. On this occasion I will have little to do with this particular database once I have finished witing it, so better to automate things where possible to help the end users.
Go to the top of the page
 
BuzyG
post Apr 30 2013, 04:27 PM
Post#5



Posts: 268
Joined: 20-September 12
From: Cornwall UK


Dennis I guess I like writing code to much. The thing I don't understand is why I can't read that the field is empty, it's very odd. testing for Null or "" doesn't register anything. simply steps passed the code.
Go to the top of the page
 
doctor9
post Apr 30 2013, 04:40 PM
Post#6


UtterAccess Editor
Posts: 17,102
Joined: 29-March 05
From: Wisconsin


BuzyG,
eah, I'm a big fan of code, too. But you can use VBA to run a query. Something like this:
CODE
        strSQL = "DELETE * FROM tblImport WHERE Nz([strCompanyName],"")='';"
        CurrentDb.Execute strSQL, dbFailOnError

In this example, strCompanyName is the name of a field that you know will be blank if the whole record is blank. You know, some fields are ALWAYS filled in, while others may or may not be. Choose one of the former. <
If this doesn't work, maybe check to see if you're importing "blank" fields as maybe a single space or something like that.
Hope this helps,
Dennis
Go to the top of the page
 
BuzyG
post May 1 2013, 03:56 AM
Post#7



Posts: 268
Joined: 20-September 12
From: Cornwall UK


I like this method, much better than my error trapping routine. I can't get the syntax correct though. I've tried a few ways to write it, but I keep getting syntax errors. What's the Nz for? If you would kindly correct the syntax in the code bellow, I think that will do it.
!--c1-->
CODE
strSQL = "DELETE * FROM tblImportITTData WHERE(tblImportData.NIIN,"")='';"
   CurrentDb.Execute strSQL, dbFailOnError
Go to the top of the page
 
doctor9
post May 1 2013, 08:46 AM
Post#8


UtterAccess Editor
Posts: 17,102
Joined: 29-March 05
From: Wisconsin


BuzyG,
I'm using the Nz() function to convert any Null values into empty strings.
For example, if you create a new record, and just skip over a field, it will contain a Null value. However, if you were to type "ABC Company" into a field, save the record, then come back and delete the "ABC Company" text from the field (without deleting the whole record), then that field would contain an empty string. These two values are not the same, even though they appear to be.
Odon't know if your database is importing data as Null or empty strings, so I'm using the Nz() function to combine the two possible values together. The basic logic is this: If this field is Null OR an empty string, select it for deletion. So, if the value is Null, it's converted to an empty string, and if it's already an empty string, it doesn't get converted, because it isn't Null.
Therefore, here's what I'd try:
CODE
strSQL = "DELETE * FROM tblImportITTData WHERE Nz([NIIN],"""")="""";""

Quotation marks are a little tricky in VBA. If you want the SQL to have a quotation mark, you need two consecutive quotation marks in your VBA (or use CHR(34) instead). Hopefully this will work for you.
Hope this helps,
Dennis
Go to the top of the page
 
BuzyG
post May 3 2013, 03:27 AM
Post#9



Posts: 268
Joined: 20-September 12
From: Cornwall UK


Thanks I knocked a quote of that, as per original and it works fine.
I'm still unsure exactly what that line of code does though. It seems to process the entire record set in one go. However without the while loop shown, it generated another error for multipule user access at the same time. So I put the while loop back in, but this is slow,therefore I would love to remove it again.
CODE
While Not rst.EOF
   strSQL = "DELETE * FROM tblITTDataImport WHERE Nz([NIIN],"""")="""";"
   CurrentDb.Execute strSQL, dbFailOnError
   rst.MoveNext
Wend
Go to the top of the page
 
doctor9
post May 3 2013, 08:09 AM
Post#10


UtterAccess Editor
Posts: 17,102
Joined: 29-March 05
From: Wisconsin


BuzyG,
The two lines of code I provided are all you should need. They are intended to REPLACE your While loop entirely.
They are running a Delete Query.
Hope this helps,
Dennis
Go to the top of the page
 
BuzyG
post May 3 2013, 09:13 AM
Post#11



Posts: 268
Joined: 20-September 12
From: Cornwall UK


I saw that when I stepped through the code. The entire query executed in one go on that line.
It also generated an error though. saying there were multiple users trying to access the data at the same time. So I put the loop back in as that stops the error message comming up.
I'm not getting much time on this project at present, plus we have a bank Holiday, so it will be next Tue before I see the code again. If you have any ideas in the mean time please post them up.
Thanks again for your help.
BuzyG
Go to the top of the page
 
doctor9
post May 3 2013, 09:31 AM
Post#12


UtterAccess Editor
Posts: 17,102
Joined: 29-March 05
From: Wisconsin


BuzyG,
I was under the impression that this was some sort of "import" table - multiple users shouldn't be accessing this table, right?
Do you already have a recordset reading this table when you are running this query? That might be the problem.
Dennis
Go to the top of the page
 
BuzyG
post May 3 2013, 10:07 AM
Post#13



Posts: 268
Joined: 20-September 12
From: Cornwall UK


Yes I have this record set open already. It occured to me, that might be the cause, but I'm at home now so can't try stuff out. I'll close the record set and post up the outcome, when I get back to the office on Tue.
Cheers BuzyG
Go to the top of the page
 
BuzyG
post May 7 2013, 09:58 AM
Post#14



Posts: 268
Joined: 20-September 12
From: Cornwall UK


Move code to run straight after the import & before opening the table record set to do other things.
!--c1-->
CODE
strSQL = "DELETE * FROM tblImportITTData WHERE Nz([NIIN],"""")="""";"
CurrentDb.Execute strSQL, dbFailOnError

Now works perfectly.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    29th May 2017 - 08:13 AM