Importing Data From Excel Via Code Giving Unreliable Outcomes

Copper Contributor

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 then generate an excel file from this data and link this to our Access tools.

 

In the field data there are many fields where the text exceeds 256 characters, however this is not consistent and varies job to job and week to week.

 

The data forms part of our invoicing.  I had previously noted many records are truncated on the clients invoices, however i put it to one side as something I will look at when I have time.

 

The issue is the information in the spreadsheet is:

site inspection
filter has not been cleaned since handover
recommended to clean asap
remove and inspect level sensor probes all ok
while probes remove ensure auto fill comes on and turns off once refitted into gutter all ok
complete results-
Fac 3.92
Tcc 4.11
pH 7.46
Cys 39
Ta 36
Cah 70
Temp 25.6
TDS 1300
check results against controller all ok no calibration required
suggested increase ch to 300-400 and cys to 50
check heater operating all ok

Which I want to send to this client.

 

The version I am seeing in Access after import is:

site inspection
filter has not been cleaned since handover
recommended to clean asap
remove and inspect level sensor probes all ok
while probes remove ensure auto fill comes on and turns off once refitted into gutter all ok

 

Quite severely trimmed and truncated.  To the point it is changing the meaning to the client.

 

I cant afford to spend the time picking out the specific jobs that this impacts and going back months, as I had to do for this example, to find the correct wording.  From what I read this is something to do with Excel and Access not playing nicely together.  I cant use the Wizard as this process is run overnight and is intended to be complete for me to invoice weekly as the jobs are finally completed.

 

Is there a work around, that doesn't involve manually importing?

 

4 Replies
It's quit difficult to understand the core issue since you are using the third party tool to read data in to Excel & others,,, better contact to the vendor supporting you,, possibly U get some solution !!
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.
If you want to take the matter in your hands you need to use Excel Automation...in this case Access will treat Excel as an Object...read the cells and transfer them to a temp table where you can manipulate them to your liking...in this way you are stopping Access from making guesses on what datatype you have thus giving the confusion
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,