Changing Excel cells from empty to null for importing into Access

[carmenlisa]

I am stuck on an issue that revolves around the difference between a null cell versus an empty cell in Excel and Access.

The scenario is that we have people doing data entry in many tabs in an
Excel file, data which will then be imported into Access. In many of the tabs, we do not know in advance how many rows of data will be entered. The people are gathering information about buildings and every building is obviously different.

I must deal with a situation where the data input tabs are laid out in a manner that does not allow for importing into Access. So I added a somewhat matching set of tabs that rearrange the data in a way that will allow Access to read the data. That is, the data must have field names across the top row with the data in rows below it. And, of course, I often do not know exactly how many rows of data there will be.

I did this with formulas. In essence, I get data that in some cases needs to be transposed from vertical to horizontal layout. In other cases I need to get the data from a specific place in a tab where it is located in the middle of a lot of other clutter.

I have no options that allow me to change the layout in the data input tabs. And I have no option to arrange for the data to be input directly into Access, which would be my first choice.

I must deal with the data as it is currently laid out in the data input tabs in Excel.

So, here is what I have done thus far. I created a second set of tabs in Excel, using formulas that draw from the data input tabs, but which lay out the data in the required format of a header row for field names and rows of data below it. That is, the result is that the data looks like an Access table. I enclosed every formula in an IF statement that leaves the cells blank if the originating cell in the input tab was blank. The formula, roughly, is: =IF(OriginatingCell = “”,””, OriginatingCell).

I then run a macro that converts all formulas to values, leaving any unneeded/unused cells empty.

The problem in my process is that these cells that I designated as “” and then converted to values are not null and Access wants to import them all as blank entries rather than ignoring them. And we end up with lots of empty rows of data in Access. (We need to allow for receiving maybe as many as 20 rows of data in a tab, but in most cases end up with only a couple rows of data).

If I were doing this as a one time thing, I would just go into each tab, find the last row of data and deleted about 50 rows directly below the last line of data and be done with the problem. But there are too many spreadsheets and too many tabs per spreadsheet for this to be feasible, doing it all manually. Finding the last row of data with a macro is too prone to error. If there were a column where I absolutely positively knew would never be empty for a record, I assume that I could do the equivalent of End-DownArrow and deleted 50 rows below that. But in my experience, any field that “always” must have an entry, sooner or later, does not have an entry for what seemed like a valid reason to the guy making the entries, and then we wipe out a whole lot of data that we needed.

So, is there a way to convert cells that are empty into null cells? I would love, in code, to do the equivalent of:

For cells A1100, IF cell is empty, then cell becomes null, else do nothing.

I am self-taught in VBA and only so-so in it. I understand loops and IF statements, so I can do a lot with very limited knowledge, but am clueless with the less obvious and less common aspects of VBA.

Is there a way to do the above (making a cell be null) in Excel? Does anyone know of any other options that might work? Remembering that I have absolutely no options in terms of changing the data entry tabs or switching to Access for the data entry?

I understand that I could create queries in Access to delete all blank records, but that can be pretty clumsy and seems like not a great way to fix the problem.

I want to thank all in advance for any advice/solutions I am given.

 

[SkipVought]

hi,

we do not know in advance how many rows of data will be entered.

You appear to be using FORMULAS to consolidate data for the purpose of importing data into MS Access, and this kind of approch is very inadequate as you have already discovered.

I must deal with a situation where the data input tabs are laid out in a manner that does not allow for importing into Access

Please explain what it is that "does not allow for importing into Access"? I know that you seem to have unnormalized data that needs to be row/column transformed, but are there any other issues?

However, it seems that the underlying issue is a poorly designed data capturing process. You ought to consult a database expert who can perhaps make a front end in MS Access for data entry.

Short of that, we need more specific information about the current data structure(s), in order to venture cogent solutions or even tips.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

The only thing that occurs to me, given that your hands are tied, would be to abandon merely importing the spreadsheets into Access, but instead set up some vba in Access to open the Excel spreadsheets, read the relevant cells, and create records if you have a set of cells containing values. This way you won't create a record unless your vba has identified something to put in the record, and if you have fields within the record that you would prefer to leave as NULL when the corresponding Excel cells are "", you can simply not set them. But I'm sure there's a better solution.

 

[carmenlisa]

Skip,
I agree with everything you say, but they are just not willing to do work in anything but Excel with the people in the field. My job is to make it work, to get the data into Access.

My real issue is the fact that Excel cells, even when emptied, are no long null cells for Access, they are blank cells, which Access is designed to see as having relevance.

If anyone knows, in Excel, how to make blank cells (cells that once had data in them but no longer do) into null cells, all else will work. And, to be honest, no matter how I change the spreadsheet, this issue would come up because people would inevitably make entries into cells for their own reasons and delete it back out again, not knowing that these cells are no longer null.

Ultimately, my problem is empty rows coming into Access due to cells that are empty but not null. If there is no way to make a cells null once again, I can deal with the problem in Access by setting up queries to delete what are essentially empty rows in all tables. But knowingly letting Access bring in a lot of empty records, and then searching for them to remove them, seems clumsy and just not right. If at all possible, I would like to deal with the issue while still in Excel.

lionelhill,
When I say importing into Access, what I am doing is linking the tabs in Excel to Access. Then Access sees them as just another Access table. Finally, Access queries copy this linked data into Access.

But when the Access queries that copy data from these linked Excel tables see an empty but not null cell in the Excel table, the query assumes we want that line to be copied.

Again, thank you both for helping, and I thank anyone else who may jump in.

 

[SkipVought]

fact that Excel cells, even when emptied

Cells containing formulas are not EMPTY or NULL or BLANK. A formula returns SOMETHING to the cell, even "" which is a ZERO LENGTH STRING or even worse in most cases " " which is a SPACE.

This is what happens when you pre-fill rows with formulas that seem to return nothing -- they are returning SOMETHING.

We still do not have a clear picture of the structure of your sheet(s). The problem may be as simple as using the proper delete method. Simply selecting cells or rows or columns and hitting the [DELETE] Key, clears VALUES but leaves other DATA. Rather Select > Right-click > Delete.

If the issue is related to pre-filled formulas, you're up the creek with that method of populating your table. I'd much rather use MS Query or even a macro that does a series of COPY 'N' PASTE. But the devil is in the details, as usual.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

I agree with all f the above and how you get around thsi problem could be different depending on how the worksheets are imported into Access

I must just get on my pedant horse to 1st correct 1 statement - a cell in excel can never be null - a null value is acomplete absence of not only dtaa but placeholder for data as well - NULL is a concet that just does not apply to Excel

Having said that, I know what you mean - this may or may not work - I have not doe much with excl > Access for a whle now but it may be worth a shot

FWIW I'm no sure you can import from ecelto access and end up with NULL records whether you have filled by formula or not. If you can then the following may do the trick...

Sub Nuller(strSheetName as string)
dim c as range
for each c in sheets(strSheetName).usedrange.cells
if c.text = "" then
    c.clearcontents
end if
next
end sub

This ill physicallyremoveany data from cells which currently have a zero length string in them (""). If this doesn;t work then you are going to have to look at a new way of importing to access for which we will need to know how the sheets are currently imported

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[lionelhill]

xlbo, you've put your finger on the fundamental problem: Because Excel has no such thing as NULL, while Access uses NULL as a very important tool, every empty Excel cell that Access encounters asks it a question it cannot answer: "Did someone actually type a blank here, or was this cell never actually typed at all?"

carmenlisa, how about going through your process as you've described it, and then adding a DELETE query in Access which deletes all records where every field is either null or ""?