Error when importing excel (1 Viewer)

Happyz · Dec 10, 2017

Hi all, I faced this error when i import my excel file into access by using VBA Code. As you can see in the image i attached below, it shows that when i import excel file into access, some of my date are blank which is not the case in my excel file where all the date are fill up. How can i fix that?
Below is the VBA code i use for importing Excel file:

Code:

Private Sub cmdImport_Click()
Dim filepath As String
Dim user As String
user = Environ("username")
filepath = "C:\Users\" & user & "\Desktop\FinalData.xlsx"
DoCmd.TransferSpreadsheet acImport, , "InvestmentData", filepath, True
End Sub

isladogs · Dec 10, 2017

Several questions about your data:

What is the data type for the field in both Excel and Access?
Did you specify Date/Time data type in your import specification?
What does the import errors table show?

Also you need to change the field name as Date is a reserved word in Access and this will cause problems
Suggest InvestmentDate or InvDate or similar

arnelgp · Dec 10, 2017

the Date Column in your
excel file is in Text format, basing
on the image you sent (left justified).
you must convert it to real date before
importing.

JHB · Dec 10, 2017

The problem is the way MS-Access handle the dates by default, (US-format is mm/dd/yyyy), when you look at the dates you got imported, everything is fine until "13/9/2017". So you need to specify the date format in the specification.

isladogs · Dec 10, 2017

Well spotted Jorn - obvious as soon as you mentioned it!

arnelgp · Dec 10, 2017

you cannot specify the date format
or any format of the column when
you are Inserting to an Existing
Table. The same fieldname, in
the same order, must exists on the
worksheet you are importing from.

you can only specify the type when
you are creating New Table.

so date format is ruled out.

JHB · Dec 10, 2017

Yes you're right, I mixed up the import for a text file.

Happyz · Dec 13, 2017

ridders said:
Several questions about your data:

What is the data type for the field in both Excel and Access?
Did you specify Date/Time data type in your import specification?
What does the import errors table show?

Also you need to change the field name as Date is a reserved word in Access and this will cause problems
Suggest InvestmentDate or InvDate or similar

The import error show type conversion error

Happyz · Dec 13, 2017

arnelgp said:
the Date Column in your
excel file is in Text format, basing
on the image you sent (left justified).
you must convert it to real date before
importing.

I changed the file format to date in excel and try convert it but it still does not work. I realize that, the excel file i import into access does not import the date range 13-31. For example for September date, the date shows from 01/09/17 - 12/09/17. the cell for the date is left blank from 13/09/2017-30/09/2017.
This happen for all the other month as well

isladogs · Dec 13, 2017

Your first answer explains why it didn't work but doesn't give enough info to provide the solution.
Your second answer shows an issue with date format
You will need to use US date format mm/dd/yyyy to fix the issue with 13-31

Please answer the other questions
Suggest you also upload a stripped down copy of your db

jleach · Dec 13, 2017

Try setting up an import specification for this. As part of the import wizard, you can specify the details of each field. You can also try saving the Excel report as CSV and using schema.ini to specify the format.

With that said, I've always had trouble with this type of stuff and ultimately end up importing manually via VBA, grabbing the field as a text string, then scrubbing it to a date after the import is complete. MS tries to make far too many assumptions on what I want to do for my taste. DoCmd.Transfer* is particularly notorious for this.

Happyz · Dec 13, 2017

ridders said:
Your first answer explains why it didn't work but doesn't give enough info to provide the solution.
Your second answer shows an issue with date format
You will need to use US date format mm/dd/yyyy to fix the issue with 13-31

Please answer the other questions
Suggest you also upload a stripped down copy of your db

Hi, I have attached my database

isladogs · Dec 13, 2017

Your problem is that the dates are in a mixed format
I believe you are trying to use UK type date format dd/mm/yyyy

Rows 1 to 81 appear to be in format dd/mm/yy for 1 Sept 2017 - 12 Sept 2017
Rows 82 onwards appear to be in format dd/m/yyyy for 13 Sept 2017 onwards

BUT when I checked the format its actually in US date format for all rows

Rows 1 to 81 are imported successfully in dd/mm/yyyy format
Rows 82 onwards not imported as Access can't identify the dates correctly

So your first task is to correctly set all your dates in Excel in the correct format
Once you've done that, try importing again

If it still doesn't work, upload your corrected spreadsheet again

Happyz · Dec 14, 2017

ridders said:
Your problem is that the dates are in a mixed format
I believe you are trying to use UK type date format dd/mm/yyyy

Rows 1 to 81 appear to be in format dd/mm/yy for 1 Sept 2017 - 12 Sept 2017
Rows 82 onwards appear to be in format dd/m/yyyy for 13 Sept 2017 onwards

BUT when I checked the format its actually in US date format for all rows

Rows 1 to 81 are imported successfully in dd/mm/yyyy format
Rows 82 onwards not imported as Access can't identify the dates correctly

So your first task is to correctly set all your dates in Excel in the correct format
Once you've done that, try importing again

If it still doesn't work, upload your corrected spreadsheet again

I have tried changing the format, and it still wont work.

JHB · Dec 14, 2017

Have you tried to save the Excel sheet as a CSV file and done an import?
The problem with it is, that what you see in the sheet isn't actually what it is.
As ridders mention, the dates looks like dd-mm-yyyy, but it is actually mm-dd-yyyy.

isladogs · Dec 14, 2017

What have you changed? The Excel dates look identical to the original file with the same mixture of formats

Note that I'm looking at it on a tablet and haven't checked the format

Happyz · Dec 14, 2017

JHB said:
Have you tried to save the Excel sheet as a CSV file and done an import?
The problem with it is, that what you see in the sheet isn't actually what it is.
As ridders mention, the dates looks like dd-mm-yyyy, but it is actually mm-dd-yyyy.

I have tried saving, but it giving me error" Potential loss of data, save it as excel"

Happyz · Dec 14, 2017

ridders said:
What have you changed? The Excel dates look identical to the original file with the same mixture of formats

Note that I'm looking at it on a tablet and haven't checked the format

I formatted the excel file but still cannot work

isladogs · Dec 14, 2017

I couldn't see any difference to the original Excel file ....

I've spent about 30 minutes trying different ways of fixing your dates:
See below & attached file

Nothing I tried worked.
I'm not saying its impossible to fix but I've spent too long on it already

My suggestion is to abandon trying to fix it & re-enter the dates
Insert an extra column in Excel, format it as UK date e.g. dd/mm/yyyy then re-enter the data correctly
Once that is done, delete the original data in column B

JHB · Dec 15, 2017

The only solution I can come up with just now is Copy and Paste method.
All other solutions I've look at could save one problem but then another problem occurs.
Click the "Copy from Excel" button, the result is saved in Book2 table.

Error when importing excel (1 Viewer)

Registered User.

Attachments

MVP / VIP

..forever waiting... waiting for jellybean!

Have been here a while

MVP / VIP

..forever waiting... waiting for jellybean!

Have been here a while

Registered User.

Registered User.

MVP / VIP

Registered User.

Registered User.

Attachments

MVP / VIP

Registered User.

Attachments

Have been here a while

MVP / VIP

Registered User.

Registered User.

MVP / VIP

Attachments

Have been here a while

Attachments

Similar threads

Users who are viewing this thread