- Microsoft Office
- Microsoft Access
- Microsoft Excel
Importing Records - NULL Issue, Scrub data?
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).
Here are the properties on the DATE field (this might not be important either):
![Date field properties]()
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 & "!"
Here are the properties on the DATE field (this might not be important either):
You could scrub the data in Excel using the Power Query (Get & Transform) before saving the file for import. If I understand the problem you wouldn't want a record to be imported where the Date field is NULL. In Power Query I believe you could easily replace all the null date fields (or whatever the junk is) with a blank which should import.
Hi Tom, thanks for the response. To answer your question:
< If I understand the problem you wouldn't want a record to be imported where the Date field is NULL.
I do want to import but the issue was that for all records, the date was NULL in the imported table and the excel records had dates in the DATE field. I think scrubbing is what I need to do. I remember that using text to columns is a way to scrub data. I am not sure about that though.
Since you have several cases ...the best way is to dump the auto import and work with Excel Automation via VBA
Open the Excel from Access ...read cell by cell and decide on each case what to import and "how".
It would be some extra work but at least you will have full control
Start from here : https://www.access-programmers.co.uk/forums/threads/import-specific-cell-from-excel-to-access.212031/
Tom: the tables appear to be ok. The dates are there in tables. When I import on my side, the dates are null in the table but when I press delete key on each one of those null values in the excel file and reimport, the dates import fine. If you use the control and down arrow key the cursor doesnt stop but goes all the way to the bottom and skips those cells that appear blank/null because there is invisible garbage in the cells (probably not the correct term).
I get an error message this this:
John: I have to import in Access to keep the history. I can not link to it if that is what you mean.
I duplicated your problem of import/append with the Excel file. If you take the file into Power Query and change the date column to date, then import the table created from Power Query I found it would append correctly. Does that make sense?
So I think the problem is not with the initial import to the table defining Date as a date in the import. It is when you attempt to append more data to the table where you don't have the opportunity to define what the Date field is. Access (in my opinion) should look at the existing table and use those data types to determine how to import the new data. I don't think this happens, as Access determines the data types based upon (I think) the first several rows of the data. If that is the case, somehow the data change in Power Query seems to override the append import. Another approach could be to ensure the first several lines of the append import have dates in the row. This might fool Access into thinking that field should be defined as a Date field.
There are other Access people out here that probably know a lot more about how to overcome the issue. But I do think the issue resolves around Access attempting to re-define the data types on append-imports.
There are other Access people out here that probably know a lot more about how to overcome the issue. But I do think the issue resolves around Access attempting to re-define the data types on append-imports.
Ok Tom...thanks for the info. I am going to keep it open a little bit and see who else chimes in. I still am under the impression that I need to scrub the data first.
Sure, the more thoughts the better for all our understanding, and I think Power Query can do that. If you are not familiar about that, let me know.
You can import data via csv file. Look at sample
Special thanks to Daniel Pineault for xls2csv function
There is an error in row 2 of sample file, where date and text is mixed in one column. If it is important, you can create import specification and add it to TransferText
The other scrub you can try is to highlight the Date field in Excel, go to Text-To-Columns, click through to step three where you format the field as a date "MDY" and then finish. Then do the import-append.
Hi Tom, I remember something about that text to columns trick. I tried it but didnt choose the MDY, which was a crucial part of the step. Thank you for that!
als, that was really neat. It imported clean data fine but when I replaced the clean data on the DATE column in excel with that dirty data then after import access gave me an error that it couldnt append all data but the dates imported. I didnt see an import error file either. Very nice though I just dont know why I would get that message though. thank you and special thanks to Daniel Pineault
Linking is not what I meant.
If you read the link I posted and subsequently Google the Excel Automation you will see that instead of relying on a "black box" which is the automatic import functionality of Access, you can instead "open" Excel via Automation and read each and every cell and in each case establish rules...e.g for example if the value is NULL substitute with "N.A" or similar
Explore More ContentExplore courses, solutions, and other research materials related to this topic.
-
![]()
received a Solution
Importing data from certain cells to a table in access -
![]()
wrote an Article
![]()
Synchronizing subforms - What record is displayed on top of a continuous subform after using scrollbar?
-
![]()
created a Video
![]()
import an Excel spreadsheet into an Access database
-
Explore Cloud Class® ![]()
Certification: MOS Microsoft Office Specialist - Access 2016 Part 3
Premium Content
You need an Expert Office subscription to comment.Start Free Trial