Forum Discussion
Importing Data From Excel Via Code Giving Unreliable Outcomes
I have had a look at several forum discussions, however I cant see a work around. Weekly we are importing time cards for my remote staff. The data is captured online via a third party tool. We ...
Thats not it.
I have a work around. If I insert 9 rows of nonsense containing 500 characters or so this then forces Access to import the field correctly.
However this is a horrid work around and not something I really want to do every week. There has to be a better way.
I have a work around. If I insert 9 rows of nonsense containing 500 characters or so this then forces Access to import the field correctly.
However this is a horrid work around and not something I really want to do every week. There has to be a better way.
I second John's recommendation. You can't have it both ways, "Little effort" is not compatible with "Perfect results".
The most common way to resolve data problems during import from Excel is to import all of the data as text into a staging table in Access. Don't try to second-guess datatypes. Make sure everything is imported into a field with the same name as the source column in Excel (assuming that vendor's product provides column titles you can use). Then, in a series of append and update queries in Access you can manipulate the data into proper datatypes, using CDate(), CLng(), CDbl(), etc.and put it in the proper final target table(s).
You can automate this to run from a macro and use Window's Task Scheduler to open the accdb and run the macro at the appropriate time during the night,
The most common way to resolve data problems during import from Excel is to import all of the data as text into a staging table in Access. Don't try to second-guess datatypes. Make sure everything is imported into a field with the same name as the source column in Excel (assuming that vendor's product provides column titles you can use). Then, in a series of append and update queries in Access you can manipulate the data into proper datatypes, using CDate(), CLng(), CDbl(), etc.and put it in the proper final target table(s).
You can automate this to run from a macro and use Window's Task Scheduler to open the accdb and run the macro at the appropriate time during the night,
