Importing changing data from Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mattymoo - 12 Jun 2004 22:22 GMT

I am working on a project where I will get a daily export of data from a website into excel. It's a full export every day with new records plus changing fields to existing records as the website is updated. For example the status of a record could change from 'no business' to 'sale made'.

I need to import this data into access then add fields to it to allow me to analyze the sales results & monitor salesman activity.

I cannot figure out how I can keep importing the same data but override existing fields.

I'm pretty new to access so please can any replies be relayed in simple terms with specific actions so I can understand it

I'm using access & excel 2003

Help!!

Reply to this Message

Ken Snell - 12 Jun 2004 22:42 GMT

Assuming that the data in EXCEL's rows have a unique identifier for them,
which then matches the primary key that you're using in the ACCESS table for
that record, you could use a combination of an update query followed by an
append query to do what you want.

Import the data into a temporary table.

Create an update query that joins (INNER) your permanent table to the
temporary table using the primary key field. Select the appropriate fields
from the temporary table as the "Update To:" values for the matching fields.
You'll need to use a syntax similar to this for this:
[TemporaryTableName].[FieldName]

Then create an append query that is based on the temporary table and the
permanent table. Make the target table (the table to which the records will
be appended) be the permanent table. Join the two using the primary key
field. Make it a LEFT or RIGHT join (right click on the join line and select
the option that gives all records from temporary table and just the matching
records from the permanent table). Put all the fields from the temporary
table onto the grid, and be sure that they match up to the appropriate
fields in the pemanent table. Put the primary key field from the permanent
table on the grid, delete any field name in the "Append To:" cell, and type
this in the "Criteria:" cell under this field:
Is Null

Run the update query. Then run the append query.

Signature

Ken Snell
<MS ACCESS MVP>

> I am working on a project where I will get a daily export of data from a website into excel. It's a full export every day with new records plus

changing fields to existing records as the website is updated. For example
the status of a record could change from 'no business' to 'sale made'.

> I need to import this data into access then add fields to it to allow me to analyze the sales results & monitor salesman activity.
>
[quoted text clipped - 5 lines]
>
> Help!!

Reply to this Message

Mattymoo - 13 Jun 2004 15:54 GMT

I thought I was there on this and the info given was really helpful, but when I try and run the update query, it asks me to enter parameter values for each field? Also when I try and run the append query (without having run the update query) I get an error that says 'cannot insert data with action query' - I've obviously gone a bit wrong somewhere - help?!

I assume I have to delete the data from the temp file before each new import? It will only let me import if I have all the data types set to text - is this a problem with the data in the excel spreadsheet?

Finally - I did proceed to run the update query by just clicking on ok for each request for a parameter value, I then had another error that told me I had 14 key violations in my table - oh dear

As you can see I am a complete novice, but determined to learn

Any help appreciated

> Assuming that the data in EXCEL's rows have a unique identifier for them,
> which then matches the primary key that you're using in the ACCESS table for
[quoted text clipped - 39 lines]
> >
> > Help!!

Reply to this Message

Ken Snell - 13 Jun 2004 18:40 GMT

As I cannot see the queries that you have created, nor know the names of
your tables, etc., you'll need to give me some more info so that we can work
through this.

The fact that you're being asked for parameter values indicates that the
names of the criteria or update values do not match any names in the query's
source tables.

Please post the names and structure of the temporary and permanent tables.
Then post the SQL statement of each query (open the query in design view,
then click on icon in top left on toolbar (small dropdown arrow) to select
the SQL view; copy the entire statement and paste into your post).

Let's see what you have. And yes, you would need to delete all records from
the temporary table after you've run the update and apped queries (you use a
delete query to do this) so that the temporary table is empty for the next
import.

Signature

Ken Snell
<MS ACCESS MVP>

> I thought I was there on this and the info given was really helpful, but when I try and run the update query, it asks me to enter parameter values

for each field? Also when I try and run the append query (without having
run the update query) I get an error that says 'cannot insert data with
action query' - I've obviously gone a bit wrong somewhere - help?!

> I assume I have to delete the data from the temp file before each new import? It will only let me import if I have all the data types set to

text - is this a problem with the data in the excel spreadsheet?

> Finally - I did proceed to run the update query by just clicking on ok for each request for a parameter value, I then had another error that told

me I had 14 key violations in my table - oh dear

> As you can see I am a complete novice, but determined to learn
>
[quoted text clipped - 43 lines]
> > >
> > > Help!!

Reply to this Message

Mattymoo - 13 Jun 2004 21:16 GMT

The name of the temporary file is fullexporttemp and has the following fields

Case No (primary field as this is a urn)
Client_Name
Date Lead Received
Date of Birth
Email
Postcode
Loan Amount Required
Valuation
Term
Telephone
Best Time
Time of Day
Supporting Information
Status
Client Contacted
Appointment Date
Follow Up Date
Anticipated Completion
Advise On
Comments
Appointment Booked
Mortgage fee
Mortgage value
Mortgage date
LC fee
LC value
LC date
ASU fee
ASU value
ASU date
BC fee
BC value
BC date
Broker fee
Broker value
Broker date
other fee
other value
other date

These fields mirror the excel spreadsheet exactly, but the data types in access are all set to text otherwise I couldn't import the table.

The permanent table id called 'lead details' and currently has exactly the same fields (I need to add more as I will use this table to extract cases for QC purposes and record the results, but I wanted to get the import/ update process right before I added more fields)

THis is SQL view od the update query
UPDATE [Lead details] INNER JOIN [full exporttemp] ON [Lead details].[Case No] = [full exporttemp].[Case No] SET [Lead details].[Case No] = fullexporttemp.[Case No], [Lead details].Client_Name = fullexporttemp.Client_Name, [Lead details].[Date Lead Received] = fullexporttemp.[Date Lead Received], [Lead details].[Date of Birth] = fullexporttemp.[Date of Birth], [Lead details].Email = fullexporttemp.Email, [Lead details].Postcode = fullexporttemp.Postcode, [Lead details].[Loan Amount Required] = fullexporttemp.[Loan Amount Required], [Lead details].Valuation = fullexporttemp.Valuation, [Lead details].Term = fullexporttemp.Term, [Lead details].Telephone = fullexporttemp.Telephone, [Lead details].[Best Time] = fullexporttemp.[Best Time], [Lead details].[Time of Day] = fullexporttemp.[Time of Day], [Lead details].[Supporting Information] = fullexporttemp.[Supporting Information], [Lead details].Status = fullexporttemp.Status, [Lead details].[Client Contacted] = fullexporttemp.[Client Contacted], [Lead details].[Appointment Date] = fullexporttemp.[Appointment Date], [Lead details].[Follow Up Date] = fullexporttemp.[Follow Up Date], [Lead details].[Anticipated Completion] = fullexporttemp.[Anticipated Completion], [Lead details].Comments = fullexport.fullexporttemp, [Lead details].[Appointment Booked] = fullexporttemp.[Appointment Booked], [Lead details].[Mortgage fee] = fullexporttemp.[Mortgage fee], [Lead details].[Mortgage value] = fullexporttemp.[Mortgage value], [Lead details].[Mortgage date] = fullexporttemp.[Mortgage date], [Lead details].[LC fee] = fullexporttemp.[LC fee], [Lead details].[LC value] = fullexporttemp.[Lead details], [Lead details].[LC date] = fullexporttemp.[Lead details], [Lead details].[ASU fee] = fullexporttemp.[ASU fee], [Lead details].[ASU value] = fullexporttemp.[ASU value], [Lead details].[ASU date] = fullexporttemp.[ASU date], [Lead details].[BC fee] = fullexporttemp.[BC fee], [Lead details].[BC value] = fullexporttemp.[BC value], [Lead details].[BC date] = fullexporttemp.[BC date], [Lead details].[Broker fee] = fullexporttemp.[Broker fee], [Lead details].[Broker value] = "f[fullexporttemp].[Broker value]", [Lead details].[Broker date] = fullexporttemp.[Broker date], [Lead details].[other fee] = fullexporttemp.[other fee], [Lead details].[other value] = fullexporttemp.[other value], [Lead details].[other date] = fullexporttemp.[other date];

And this is the SQL view of the append query

INSERT INTO [Lead details append query]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full exporttemp].[Case No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));

There are only 14 records in my excel spreadsheet at the moment and they are all in my perm 'lead details' table. I changed some of the details and added two new records before importing into the temp 'fullexporttemp' and that file looks correct

I've just looked at the update query in datasheet view and noticed that it has the orginal 14 records before they were changed, but not the two new ones.

The append query file has only the two new records in it, but none of the original ones.

Hope this makes sense to you? there's a lot of info here

Thanks for your patience

Pauline

> As I cannot see the queries that you have created, nor know the names of
> your tables, etc., you'll need to give me some more info so that we can work
[quoted text clipped - 90 lines]
> > > >
> > > > Help!!

Reply to this Message

Ken Snell - 13 Jun 2004 23:30 GMT

I do not see anything obviously wrong with your queries. You note that the
temporary table's fields all are text formatted. Are the permanent table's
fields also text formatted?

Which specific parameters are you asked to provide when you run the update
query?

Signature

Ken Snell
<MS ACCESS MVP>

> The name of the temporary file is fullexporttemp and has the following fields
>
[quoted text clipped - 41 lines]
>
> The permanent table id called 'lead details' and currently has exactly the same fields (I need to add more as I will use this table to extract cases

for QC purposes and record the results, but I wanted to get the import/
update process right before I added more fields)

> THis is SQL view od the update query
> UPDATE [Lead details] INNER JOIN [full exporttemp] ON [Lead details].[Case No] = [full exporttemp].[Case No] SET [Lead details].[Case No] =

fullexporttemp.[Case No], [Lead details].Client_Name =
fullexporttemp.Client_Name, [Lead details].[Date Lead Received] =
fullexporttemp.[Date Lead Received], [Lead details].[Date of Birth] =
fullexporttemp.[Date of Birth], [Lead details].Email = fullexporttemp.Email,
[Lead details].Postcode = fullexporttemp.Postcode, [Lead details].[Loan
Amount Required] = fullexporttemp.[Loan Amount Required], [Lead
details].Valuation = fullexporttemp.Valuation, [Lead details].Term =
fullexporttemp.Term, [Lead details].Telephone = fullexporttemp.Telephone,
[Lead details].[Best Time] = fullexporttemp.[Best Time], [Lead
details].[Time of Day] = fullexporttemp.[Time of Day], [Lead
details].[Supporting Information] = fullexporttemp.[Supporting Information],
[Lead details].Status = fullexporttemp.Status, [Lead details].[Client
Contacted] = fullexporttemp.[Client Contacted], [Lead details].[Appointment
Date] = fullexporttemp.[Appointment Date], [Lead details].[Follow Up Date] =
fullexporttemp.[Follow Up Date], [Lead details].[Anticipated Completion] =
fullexporttemp.[Anticipated Completion], [Lead details].Comments =
fullexport.fullexporttemp, [Lead details].[Appointment Booked] =
fullexporttemp.[Appointment Booked], [Lead details].[Mortgage fee] =
fullexporttemp.[Mortgage fee], [Lead details].[Mortgage value] =
fullexporttemp.[Mortgage value], [Lead details].[Mortgage date] =
fullexporttemp.[Mortgage date], [Lead details].[LC fee] = fullexporttemp.[LC
fee], [Lead details].[LC value] = fullexporttemp.[Lead details], [Lead
details].[LC date] = fullexporttemp.[Lead details], [Lead details].[ASU fee]
= fullexporttemp.[ASU fee], [Lead details].[ASU value] = fullexporttemp.[ASU
value], [Lead details].[ASU date] = fullexporttemp.[ASU date], [Lead
details].[BC fee] = fullexporttemp.[BC fee], [Lead details].[BC value] =
fullexporttemp.[BC value], [Lead details].[BC date] = fullexporttemp.[BC
date], [Lead details].[Broker fee] = fullexporttemp.[Broker fee], [Lead
details].[Broker value] = "f[fullexporttemp].[Broker value]", [Lead
details].[Broker date] = fullexporttemp.[Broker date], [Lead details].[other
fee] = fullexporttemp.[other fee], [Lead details].[other value] =
fullexporttemp.[other value], [Lead details].[other date] =
fullexporttemp.[other date];

> And this is the SQL view of the append query
>
[quoted text clipped - 4 lines]
>
> There are only 14 records in my excel spreadsheet at the moment and they are all in my perm 'lead details' table. I changed some of the details and

added two new records before importing into the temp 'fullexporttemp' and
that file looks correct

> I've just looked at the update query in datasheet view and noticed that it has the orginal 14 records before they were changed, but not the two new

ones.

> The append query file has only the two new records in it, but none of the original ones.
>
[quoted text clipped - 3 lines]
>
> Pauline

Reply to this Message

Mattymoo - 14 Jun 2004 06:54 GMT

Yes the temp fields are all text fomratted and I'm asked to provide parameters on all 39 fields (now I've corrected a couple of errors I spotted)

If I ok the parameter request without entering any data. I get the following error.

Microsoft access can't update all the records in the update query:
MAS didn't update 0 fields due to a type conversion failure, 14 records due to key violations and 0 records due to record violations. To ignore click yes etc.

> I do not see anything obviously wrong with your queries. You note that the
> temporary table's fields all are text formatted. Are the permanent table's
[quoted text clipped - 116 lines]
> >
> > Pauline

Reply to this Message

Jim/Chris - 14 Jun 2004 17:14 GMT

sisnce it is a complete Excel file why not delete the
records in your table and inport all of the new records
from excel.

Jim

>-----Original Message-----
>Yes the temp fields are all text fomratted and I'm asked to provide parameters on all 39 fields (now I've corrected

a couple of errors I spotted)

>If I ok the parameter request without entering any data. I get the following error.
>
>Microsoft access can't update all the records in the update query:
>MAS didn't update 0 fields due to a type conversion failure, 14 records due to key violations and 0 records due

to record violations. To ignore click yes etc.

>> I do not see anything obviously wrong with your queries. You note that the
>> temporary table's fields all are text formatted. Are the permanent table's
[quoted text clipped - 118 lines]
>>
>.

Reply to this Message

Mattymoo - 14 Jun 2004 20:54 GMT

I need to add fields to the imported data to monitor salesmen activity, so presumably I can't delete the imported records, but maintain the additional fields - can i? The data should come in the same order every day with new records added at the bottom and exisitng fields updated

This is confusing me no end!!

Pauline

> sisnce it is a complete Excel file why not delete the
> records in your table and inport all of the new records
[quoted text clipped - 187 lines]
> >>
> >.

Reply to this Message

Mattymoo - 14 Jun 2004 21:12 GMT

I need to add fields to the imported data to monitor salesman activity. Can I delete the imported data, but maintain the additonal fields nad insert the new daily download?

The daily export should be in the same order each day, with new records added at the bottom and updated fields within the exiting data

I'm really confused!!

> sisnce it is a complete Excel file why not delete the
> records in your table and inport all of the new records
[quoted text clipped - 187 lines]
> >>
> >.

Reply to this Message

Mattymoo - 14 Jun 2004 21:24 GMT

> sisnce it is a complete Excel file why not delete the
> records in your table and inport all of the new records
[quoted text clipped - 187 lines]
> >>
> >.

Reply to this Message

Ken Snell - 15 Jun 2004 02:44 GMT

Now that I'm more awake, do you see the same typo in your query that I see:

Initially, you use this as the name of the temporary table:
full exporttemp

Later, you use this:
fullexporttemp

I'm guessing that the second one is wrong, and that is why the query is
asking for the parameter values for each field.

Signature

Ken Snell
<MS ACCESS MVP>

> Yes the temp fields are all text fomratted and I'm asked to provide parameters on all 39 fields (now I've corrected a couple of errors I

spotted)

> If I ok the parameter request without entering any data. I get the following error.
>
> Microsoft access can't update all the records in the update query:
> MAS didn't update 0 fields due to a type conversion failure, 14 records due to key violations and 0 records due to record violations. To ignore

click yes etc.

> > I do not see anything obviously wrong with your queries. You note that the
> > temporary table's fields all are text formatted. Are the permanent table's
[quoted text clipped - 116 lines]
> > >
> > > Pauline

Reply to this Message

Mattymoo - 15 Jun 2004 13:17 GMT

I saw it, corrected it and a couple of other minor orrors and it's working just fine thanks.

My append query is not though - it says 'Cannot insert data with action query query'.

The sql view is as below. Can you tell what's wrong here

Thanks for your continued help

Pauline

> Now that I'm more awake, do you see the same typo in your query that I see:
>
[quoted text clipped - 162 lines]
> > > >
> > > > Pauline

Reply to this Message

Ken Snell - 15 Jun 2004 14:13 GMT

Typo again? You have
Lead details append query
as the target table (the table to get the appended records), but I believe
it should be
Lead details

INSERT INTO [Lead details]
SELECT [full exporttemp].*
FROM [full exporttemp] LEFT JOIN [Lead details] ON [full
exporttemp].[Case No] = [Lead details].[Case No]
WHERE ((([Lead details].[Case No]) Is Null));

Signature

Ken Snell
<MS ACCESS MVP>

> I saw it, corrected it and a couple of other minor orrors and it's working just fine thanks.
>
[quoted text clipped - 172 lines]
> > > > >
> > > > > Pauline

Reply to this Message