How many nulls in each field?

[Peter]

I am importing large, 60 field 300,000 record csv files and am quickly
overloading Access. I need to query each table, once imported (all fields as
text), to find which field are all or mostly null or ZLS. Then I can delete
these fields. So, I need a query that results in just 2 records. The first
record would all the field names in the table and the second would be the
total number of nulls or ZLS in that field.

I can do it for one filed at a time but not for more than that.

And, pushing my luck, is there a way to control the length of a text field
during the import process? My default text field length is 50 set in
Tools>Options but they still come in at 255 when I import. Although I can
change field type and indexing during the import I cannot see how to change
the text field length.

Please accept my apologies if I am missing the blindingly obvious.

I am using Access 2003 on Vista (with 4GB ram).

As usual thank you in anticipation for your help and time.

Peter

 

[John W. Vinson]

I am importing large, 60 field 300,000 record csv files and am quickly
overloading Access. I need to query each table, once imported (all fields as
text), to find which field are all or mostly null or ZLS. Then I can delete
these fields. So, I need a query that results in just 2 records. The first
record would all the field names in the table and the second would be the
total number of nulls or ZLS in that field.

I can do it for one filed at a time but not for more than that.

And, pushing my luck, is there a way to control the length of a text field
during the import process? My default text field length is 50 set in
Tools>Options but they still come in at 255 when I import. Although I can
change field type and indexing during the import I cannot see how to change
the text field length.

Please accept my apologies if I am missing the blindingly obvious.

I am using Access 2003 on Vista (with 4GB ram).

As usual thank you in anticipation for your help and time.

Peter

A couple of suggestions come to mind. One would be to link to the table rather
than importing it. Another would be to create a table with 60 fields of the
size you want and import into that, rather than into a new table each time.
Yet another would be to use two .mdb files, one the "main" database and
another that you would create (using the CreateDatabase method, see the vba
help) as needed each time you do an import.

That way you could import into a table with a constant name (ImportTable say),
and keep your query the same - just change the data.

 

[Peter]

John,

Thank you very much. The idea of importing to an existing table is what I
will do.

I don't suppose you have any thoughts on the first part of my post about
counting nulls or ZLSs.

Thanks again, Peter

 

[John Spencer]

Counting nulls or ZLS is not too difficult, just tedious to set up the
query.

SELECT Abs(Sum(FieldA is Null Or FieldA = "")) As aNull
, Abs(Sum(FieldB is Null Or FieldB = "")) As bNull
, Abs(Sum(FieldC is Null Or FieldC = "")) As cNull
, Abs(Sum(FieldD is Null Or FieldD = "")) As dNull
FROM [YourTable]

If the field names vary from import to import then I would probably
write myself a vba routine to construct the query string. IF the field
names don't vary, then it will just be a pain to build the query one time.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Peter]

John,

Thanks again. As you say a real pain to set up especially as the names do
vary from import to import. Will have to try the vb route but, although I
have used this in Excel, this would be my first time in Access. Think I'll
have to have a go though as 24 different tables to query and all 60 fields at
least and all different. At least I now know there is not an easier way I've
missed.

Thank you, Peter

Counting nulls or ZLS is not too difficult, just tedious to set up the
query.

SELECT Abs(Sum(FieldA is Null Or FieldA = "")) As aNull
, Abs(Sum(FieldB is Null Or FieldB = "")) As bNull
, Abs(Sum(FieldC is Null Or FieldC = "")) As cNull
, Abs(Sum(FieldD is Null Or FieldD = "")) As dNull
FROM [YourTable]

If the field names vary from import to import then I would probably
write myself a vba routine to construct the query string. IF the field
names don't vary, then it will just be a pain to build the query one time.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

Thank you very much. The idea of importing to an existing table is what I
will do.

I don't suppose you have any thoughts on the first part of my post about
counting nulls or ZLSs.

Thanks again, Peter

 

[John Spencer]

One thing I did not note is that I assumed all the fields were text
types. If you have number fields or date fields or boolean fields then
you will have to change the comparisons. For number and date fields you
would only be looking for nulls. Boolean fields should always have a
value, so there is really no way to count those that don't have a value.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

Thanks again. As you say a real pain to set up especially as the names do
vary from import to import. Will have to try the vb route but, although I
have used this in Excel, this would be my first time in Access. Think I'll
have to have a go though as 24 different tables to query and all 60 fields at
least and all different. At least I now know there is not an easier way I've
missed.

Thank you, Peter

Counting nulls or ZLS is not too difficult, just tedious to set up the
query.

SELECT Abs(Sum(FieldA is Null Or FieldA = "")) As aNull
, Abs(Sum(FieldB is Null Or FieldB = "")) As bNull
, Abs(Sum(FieldC is Null Or FieldC = "")) As cNull
, Abs(Sum(FieldD is Null Or FieldD = "")) As dNull
FROM [YourTable]

If the field names vary from import to import then I would probably
write myself a vba routine to construct the query string. IF the field
names don't vary, then it will just be a pain to build the query one time.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

Thank you very much. The idea of importing to an existing table is what I
will do.

I don't suppose you have any thoughts on the first part of my post about
counting nulls or ZLSs.

Thanks again, Peter

:

I am importing large, 60 field 300,000 record csv files and am quickly
overloading Access. I need to query each table, once imported (all fields as
text), to find which field are all or mostly null or ZLS. Then I can delete
these fields. So, I need a query that results in just 2 records. The first
record would all the field names in the table and the second would be the
total number of nulls or ZLS in that field.

I can do it for one filed at a time but not for more than that.

And, pushing my luck, is there a way to control the length of a text field
during the import process? My default text field length is 50 set in
Tools>Options but they still come in at 255 when I import. Although I can
change field type and indexing during the import I cannot see how to change
the text field length.

Please accept my apologies if I am missing the blindingly obvious.

I am using Access 2003 on Vista (with 4GB ram).

As usual thank you in anticipation for your help and time.

Peter
A couple of suggestions come to mind. One would be to link to the table rather
than importing it. Another would be to create a table with 60 fields of the
size you want and import into that, rather than into a new table each time.
Yet another would be to use two .mdb files, one the "main" database and
another that you would create (using the CreateDatabase method, see the vba
help) as needed each time you do an import.

That way you could import into a table with a constant name (ImportTable say),
and keep your query the same - just change the data.

 

[Peter]

John,

Your assumption was correct they are all text fields. As a matter of
interest I copied and pasted the field names into Excel and then used a
string formula to assemble the individual part of the query. Then I selected
all the 60 plus cells containing the individual parts of the query and copy
and pasted back into SQL view. Much to my surprise it worked! Not the best
way but it got me out of a fix and as I hope not to have to do the same thing
again its better than me fighting with vb in Access while under pressure.

Thank you very much for your help, Peter

One thing I did not note is that I assumed all the fields were text
types. If you have number fields or date fields or boolean fields then
you will have to change the comparisons. For number and date fields you
would only be looking for nulls. Boolean fields should always have a
value, so there is really no way to count those that don't have a value.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

Thanks again. As you say a real pain to set up especially as the names do
vary from import to import. Will have to try the vb route but, although I
have used this in Excel, this would be my first time in Access. Think I'll
have to have a go though as 24 different tables to query and all 60 fields at
least and all different. At least I now know there is not an easier way I've
missed.

Thank you, Peter

Counting nulls or ZLS is not too difficult, just tedious to set up the
query.

SELECT Abs(Sum(FieldA is Null Or FieldA = "")) As aNull
, Abs(Sum(FieldB is Null Or FieldB = "")) As bNull
, Abs(Sum(FieldC is Null Or FieldC = "")) As cNull
, Abs(Sum(FieldD is Null Or FieldD = "")) As dNull
FROM [YourTable]

If the field names vary from import to import then I would probably
write myself a vba routine to construct the query string. IF the field
names don't vary, then it will just be a pain to build the query one time.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Peter wrote:
John,

Thank you very much. The idea of importing to an existing table is what I
will do.

I don't suppose you have any thoughts on the first part of my post about
counting nulls or ZLSs.

Thanks again, Peter

:

I am importing large, 60 field 300,000 record csv files and am quickly
overloading Access. I need to query each table, once imported (all fields as
text), to find which field are all or mostly null or ZLS. Then I can delete
these fields. So, I need a query that results in just 2 records. The first
record would all the field names in the table and the second would be the
total number of nulls or ZLS in that field.

I can do it for one filed at a time but not for more than that.

And, pushing my luck, is there a way to control the length of a text field
during the import process? My default text field length is 50 set in
Tools>Options but they still come in at 255 when I import. Although I can
change field type and indexing during the import I cannot see how to change
the text field length.

Please accept my apologies if I am missing the blindingly obvious.

I am using Access 2003 on Vista (with 4GB ram).

As usual thank you in anticipation for your help and time.

Peter
A couple of suggestions come to mind. One would be to link to the table rather
than importing it. Another would be to create a table with 60 fields of the
size you want and import into that, rather than into a new table each time.
Yet another would be to use two .mdb files, one the "main" database and
another that you would create (using the CreateDatabase method, see the vba
help) as needed each time you do an import.

That way you could import into a table with a constant name (ImportTable say),
and keep your query the same - just change the data.

 

[John W. Vinson]

I am importing large, 60 field 300,000 record csv files and am quickly
overloading Access. I need to query each table, once imported (all fields as
text), to find which field are all or mostly null or ZLS. Then I can delete
these fields. So, I need a query that results in just 2 records. The first
record would all the field names in the table and the second would be the
total number of nulls or ZLS in that field.

I'd really do this in VBA code. You could use TransferText to link (not
import!) to the file; create a Recordset based on it; and build an array of
all its fieldnames using the Fields collection of the recordset object. You
could then loop through the recordset counting nulls (or non-nulls if you
prefer) in the array. I've got to run shortly but I might be able to throw
together some sample code later this weekend.

 

[Peter]

John,

I can feel how this offends your sensibilities <g> and I do realise what a
fudge it is but it works and for me at least it is quicker than vb with my
level of skill.
Thank you for your offer to "throw something together" but I am nearly done
now and would hope to be finished very soon - I would hate to waste your time.

Promise I will look at vba in Access when I am not under such immediate time
constraints.

Thanks, Peter

 

[John W. Vinson]

I can feel how this offends your sensibilities <g> and I do realise what a
fudge it is but it works and for me at least it is quicker than vb with my
level of skill.
Thank you for your offer to "throw something together" but I am nearly done
now and would hope to be finished very soon - I would hate to waste your time.

Promise I will look at vba in Access when I am not under such immediate time
constraints.

Good luck and best wishes! If I'm offended at all (I'm not), it would be with
the irksome situation someone has put you into.

 

[Peter]

John,

Thanks for your support.

Peter