Updating tables with new/modified Excel data on an ongoing basis

[Ben]

Hi, I'm after some direction with the following issue,

BACKGROUND:
A table was created from an Excel spreadsheet with a number of columns
and hundreds of rows of data collected from the field. A workspace was
made using this table and includes extensive custom labelling and
styling based on the data attributes.

Further data has since been collected in the field and added to the
Excel file. Various attributes of the existing data have also changed
in a number of instances.

PROBLEM:
This new data needs to be merged into the existing mapinfo table and
where the existing data has changed the updates also need to be shown
(replacing the obsolete values).

This will be an ongoing process as data is continually captured and
updated.

What is the best way of updating the table using the new values in
Excel (remembering that custom labels and styles for existing data
need to remain)?

ATTEMPT:
The "Append Rows to Table" tool is not really the answer as it will
only add the entire new table onto the old table. It doesn't have a
search and replace function or an add only new edits function. You
would then have to remove all the old rows and pack the table which
could work except if rows have been added or deleted from within the
old dataset. If this was case then row id's would be different and the
workspace custom labels would be messed up.

I can't see a way of dynamically linking an Excel workspace to a
mapinfo table.

Does anyone have a simple process they use as a solution to this sort
of scenario (short of manually identifying all individual changes and
applying edits) and could recommend?

Thanks
Ben.

[e.j.h.polle]

Hi Ben,

the easiest and the fastest way to solve your problem is to reimport
the spreadsheet into MapInfo, overwriting the exisiting table
definition. All the new records and all the old records will be
visible then - with the most recent attribute data.

If you reopen the workspace with the updated table you will discover
that all the custom labelling and styling is already in place, because
these settings are stored in the WOR file.

This means that you will have to reimport your spreadsheet on a
regular basis - as new records are added and old records are changed.

HTH,

Egge-Jan

[Peter Horsbøll Møller]

Just wanted to add a warning about using custom labels stored in workspace in combination with an Excel that get reimportant occationally.

As custom labels are linked to the records in the table (Excel file) using the rowid, reimporting the Excel file might get a label linked to the wrong record, if records have been deleted or inserted in the Excel file.

This is only a problem if you 1) have changed the positions of the labels manually 2) have deleted labels manually 3) have changed labels manually or 4) have used the Label tool to position the labels manually.

If the Remove Custom labels menu item in the Map menu is enabled, you have custom labels in your map. 

Egge-Jan solution will work well if you only store label and layer settings in the workspace.

Also note that you have to recreate your points when you reimport the Excel file otherwise the new records will not have any points in the map.

If you have unique id's on each of your records in your Excel file, you can compare the id's to those in the existing table, only copy the new records from the Excel file and insert these into your MapInfo table.

Peter Horsbøll Møller
Pitney Bowes Business Insight - MapInfo

2011/8/22 e.j.h.polle <e.j.h...@gmail.com>

--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en

[Ben]

Hi Egge-Jan and Peter,

Thank you for your replies.

Yes I was leaning towards that methodology as described by Egge-Jan
but was aware of the issues outlined by Peter and was hoping for a
solution that easily addresses these issues. As the data will be
supplied from an external source, it is hard to always know if rows
have been switched, omitted, added or values changed. Although
recreating points when reimporting from Excel is generally not a
problem its just all these little things that can cause problems when
a less experienced mapinfo user will need to update the table.

Peter the data "should" have a unique id and this being the case would
you bring the new excel file into mapinfo as table_2.tab and then use
SQL to identify which rows are different to table_1.tab? If so what is
the best command for achieving this and in the case where a row has
been deleted (or added or replaced) how would this be flagged?

Thanks again.
Ben

[Peter Horsbøll Møller]

You can always use a dub select to determine whether records are existing, new or deleted.

Here is a baisc example:

'**Finding existing records in the old dataset (existing in both datasets)

'**Note this does not check if there has been made changes to the records

Select * From OLD_DATASET 

   Where UNIQUE_ID In (Select UNIQUE_ID From NEW_DATASET)

'**Finding existing records in the new dataset (existing in both datasets)

'**Note this does not check if there has been made changes to the records

Select * From NEW_DATASET 

   Where UNIQUE_ID In (Select UNIQUE_ID From OLD_DATASET)

'**Finding new records (only exists in the new dataset and not in the old):

Select * From NEW_DATASET 

   Where Not UNIQUE_ID In (Select UNIQUE_ID From OLD_DATASET)

'**Finding deleted records (only exists in the old dataset and not in the new):

Select * From OLD_DATASET 

   Where Not UNIQUE_ID In (Select UNIQUE_ID From NEW_DATASET)

Hope that helps

Peter Horsbøll Møller
Pitney Bowes Business Insight - MapInfo

2011/8/24 Ben <radm...@gmail.com>

Ben

[Ben]

Thanks Peter,

That helps. I will try your suggestions.

Regards
Ben.