We help IT Professionals succeed at work.
Get Started

Importing Records - NULL Issue, Scrub data?

pdvsa asked
Last Modified: 2020-03-05
Experts, I just had a good lesson in NULLS or at least what appears to be NULL. I will try to explain.

I am importing excel data into Access.
The records import but I just discovered that if there appears to be no data in the DATE field for a record (ie NULL) in excel, then all of the records data in the DATE field are not imported.  Meaning that the entire record set count imports; however, for each one of those records where there is a NULL for the DATE, then ALL of the records display a NULL for the DATE in the access table (XLImportAccess) so the import essentially fails but only on that DATE field.  I know the DATE being NULL in excel is the issue because I manually populated all of the records that showed NULL for the date and replaced with a date and after importing again, all the data in the DATE field imported and matched the data in excel.

How can I append the data and account for these NULLS so that all data imports on the DATE column in excel even if there is a NULL?  

Note : NULL might not be a good description of my issue because I just noticed that the excel cells that appear to be NULL actually seem to have something in the field because I move the cursor to the very top of the data set in the DATE column in excel and use the control plus down arrow key and the cursor doesnt stop on the cell above but instead the cursor skips over the cells that appear to be NULL (it shouldnt do this if they are truly NULL) so I have to say that maybe the cells that appear NULL are in fact not NULL and there is instead some kind of invisible garbage in those cells. I hope that is not confusing but I imagine experts have encountered this type of issue and know what I mean.  Maybe I only need to do a good scrubbing of the data prior to import but how do I do this with many records?  Maybe there is code that scrubs excel data?

I dont think its important but below is the INSERT INTO code I use to append (not in entirety).

Private Sub btnImport_Click_Click()

On Error GoTo EH

CurrentDb.Execute "delete * from [XLImportToAccess]"
    Dim strName As String
    Dim xlApp As Object
    Dim xlWB As Object
    Set xlApp = New Excel.Application
    With xlApp
        .Visible = False

        Set xlWB = .Workbooks.Open("C:\Users\tj\Documents\Work\DB\ImportToAccess.xlsx", , False)

strName = "ToImport"
    End With
    Set xlWB = Nothing
    Set xlApp = Nothing

   DoCmd.TransferSpreadsheet acImport, , "XLImportToAccess", "C:\Users\tj\Documents\Work\DB\ImportToAccess.xlsx", True, strName & "!"

Open in new window

Here are the properties on the DATE field (this might not be important either):
Date field properties
Watch Question
This problem has been solved!
Unlock 1 Answer and 20 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE