Import Error (1 Viewer)

Gasman · Jul 17, 2017

Good morning everyone,
I have a small DB that monitors calls.
I import data from two call systems each Monday.
Today I received the error in the attached pic.

As I use macros for the batch import, I stepped through the macros and the fault (well as far as I have got) is in the query qryACRcalls
This takes the exported data from the CR system which is in an Excel sheet linked to the DB and appends it to the table tblCRcalls.

I copied the table (structure only) and ran an amended append query to append to the test table and it ran fine.?

At a loss as to where to look as I have no retsrictions on the table (at least that I am aware of) and the fact that it worked fine with a copy of the table.

DB is split FE & BE, the the copied table was local.?

Any tips as where to look please?

Code:

INSERT INTO tblCRCalls ( Direction, CallingNumber, CalledDay, CalledDate, CalledTime, FromNumber, FromCallerID, ToNumber, ToCallerID, RedirectedFrom, RedirectedTo, Duration, Comments, MD5, Category )
SELECT CR.Direction, CR.Number, CR.Day, CR.Date, CR.Time, CR.[From Number], CR.[From Caller ID], CR.[To Number], CR.[To Caller ID], CR.[Redirected From], CR.[Redirected To], CR.Duration, CR.Comments, CR.MD5, CR.Category
FROM CR;

TIA

Minty · Jul 17, 2017

Sounds like a error in the Excel spreadsheet, may be a field(column) has a weird value in it?

Copy and pasting it might have "hidden" the problem?

Gasman · Jul 17, 2017

Hi Minty,
I only copied and pasted the table structure?
I'll inspect the sheet though.

Thank you.

Gasman · Jul 17, 2017

I think you may have got it Minty.
Looking through the excel sheet there are two numbers that have 'Private' in the column, not an actual number. However the test table has those entries as blank. Not ever seen this 'Private] before?
Puzzled as to why the original table balked, but will now change it and test further.

Gasman · Jul 17, 2017

OK, I've discovered the problem. The linked excel sheet has Private in two places in place of the calling number and codes for caller id in two places in the called number column.

I've changed the destination fields in the table to text, but looking at the sheet as a linked table it shows #Num! for those errant values.

How can I tell Access to treat those columns as text and not numbers anymore?
My process is
Export data from call system as csv (no option for anything else)
Open csv file, rename sheet and save file with name linked in DB.
Run the import to take the data from the linked Excel sheet and append to the table.

TIA

Edit: Solved it for now, by changing the columns in Excel to Text

Import Error (1 Viewer)

Gasman

Enthusiastic Amateur

Attachments

Minty

AWF VIP

Gasman

Enthusiastic Amateur

Gasman

Enthusiastic Amateur

Gasman

Enthusiastic Amateur

Similar threads

Users who are viewing this thread