Why do blank fields, when exported to Excel appear not blank?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there.

Hopefully someone can help with something that seems pretty strange. I'm
exporting data from access to excel and doing some stuff to the data with
pivot tables.

In the pivot tables however, blank cells don't register as 'blank' unless I
manually go into each cell and hit delete (even though there seems to be
nothing there to delete).

Here's an example of before:

Count of QualityChecked
QualityChecked Total
(blank)
4
Yes 6
Grand Total 10

and after I delete the data in the seemingly blank cells:


Count of QualityChecked
QualityChecked Total
(blank)
Yes 6
Grand Total 6


It's one of those strange things that's driving my insanity to the edge of
oblivion.

Any ideas?

Cheers dudes

Shane
 
Hi there.

Hopefully someone can help with something that seems pretty strange.
I'm exporting data from access to excel and doing some stuff to the
data with pivot tables.

In the pivot tables however, blank cells don't register as 'blank'
unless I manually go into each cell and hit delete (even though there
seems to be nothing there to delete).

Here's an example of before:

Count of QualityChecked
QualityChecked Total
(blank)
4
Yes 6
Grand Total 10

and after I delete the data in the seemingly blank cells:


Count of QualityChecked
QualityChecked Total
(blank)
Yes 6
Grand Total 6


It's one of those strange things that's driving my insanity to the
edge of oblivion.

Any ideas?

Cheers dudes

Shane
Click to expand...

Sometimes people delete contents from a field/cell by slamming the space
bar.
Visually the field seems empty, but then contains a space.
 
Jet 4 converts Null fields to Empty Strings on export to
or import from Excel.

They didn't tell anyone they were going to do that, but it seems
to have been part of a general move in Microsoft away from
Null values (.Net doesn't support Null values like VB did)
I always suspected that it was the influence of Web interfaces.
Web interfaces don't support Null values, so empty fields are
reported as empty strings, rather than as Null values.

It certainly is irritating when dealing with Access and Excel:
both Access and Excel support Null fields, but the import/export
procedures don't.

(david)
 
Thanks for the feedback guys but it doesn't really help with the problem I've
got. Is there a way of getting rid of the empty strings after I've exported
the data to Excel?
 
got. Is there a way of getting rid of the empty strings after I've
exported
the data to Excel?
Click to expand...

Ask in an Excel group for a macro that converts 'empty strings' to
empty cells. You can run the macro from inside Excel, or you can
write code in Access to do the same.

VBA code would start with an Excel Spreadsheet object:
dim obj as object
set obj = createobject("mySS.xls")

(david)
 
david@epsomdotcomdotau said:
Jet 4 converts Null fields to Empty Strings on export to
or import from Excel.
Click to expand...

I don't think Jet 4. Try this

CREATE TABLE Test (
key_col INTEGER,
text_col VARCHAR(255));

INSERT INTO Test VALUES (1, 'One');
INSERT INTO Test VALUES (2, NULL);
INSERT INTO Test VALUES (3, 'Three');
INSERT INTO Test VALUES (4, NULL);
INSERT INTO Test VALUES (5, 'Five');

--Export
SELECT key_col, text_col
INTO
[Excel 8.0;Database=C:\NewBook.xls;].Test
FROM Test;

SELECT COUNT(*) AS count_nulls
FROM
[Excel 8.0;Database=C:\NewBook.xls;].Test
WHERE text_col IS NULL;
-- answer is 2, nulls are exported.
 
Back
Top