data cleansing and translation tools for relational databases

Hello All
We have a data migration project. This is basically what we plan to
achieve we have a legacy system which as some data with German long
texts like material texts,etc
We want to take data coming in flat files which as this german texts
and convert this to English text the texts are primarily products
names please advise what tools external or whatever is available to do
this translation to english the texts are about 15 to 20 characters
max.
Next we have data coming from legacy systems that we want to cleanse
like identify duplicates based on things like addresses:
eq: we might have a customer called ABC one at address 118 Main
Street, Nashua and another same customer defined in our another legacy
system called ABC Inc but address at Main Street, Nashua
We want a data cleansing tool or library that works on top of SQL
Server that can do this kind of data pattern identification,etc
Please advice free tools and tools within SQL if I am not aware of
that can do this as well good 3rd party tools that can do this.
Thanks
KarenHi
I don't know of any data cleansing tools that may perform all the functions
that you require. Even if you employed a company to manually clean the data
there is likely to be some degree of error at th e nd of the exercise.
I you carried out the changes yourself it may be possible to clean the data
using a translation table and/or ad-hoc queries.
John
"Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
news:a5fd468a.0409032125.35235ea@.posting.google.com...
> Hello All
> We have a data migration project. This is basically what we plan to
> achieve we have a legacy system which as some data with German long
> texts like material texts,etc
> We want to take data coming in flat files which as this german texts
> and convert this to English text the texts are primarily products
> names please advise what tools external or whatever is available to do
> this translation to english the texts are about 15 to 20 characters
> max.
> Next we have data coming from legacy systems that we want to cleanse
> like identify duplicates based on things like addresses:
> eq: we might have a customer called ABC one at address 118 Main
> Street, Nashua and another same customer defined in our another legacy
> system called ABC Inc but address at Main Street, Nashua
> We want a data cleansing tool or library that works on top of SQL
> Server that can do this kind of data pattern identification,etc
> Please advice free tools and tools within SQL if I am not aware of
> that can do this as well good 3rd party tools that can do this.
> Thanks
> Karen|||There are many companies providing software and services to help clean up
name and address databases. These methods are usually specific to particular
postal systems or geographical areas so I suggest you Google for something
that meets your requirements in the locales of interest to you.
--
David Portas
SQL Server MVP
--|||Try the following link :
http://www.itcg.nl/
From their site :
CLUE
Have you ever experienced problems with duplicate customers,
addresses, products or any other information?
CLUE®, the CLUster Engine from ITCG is a generic component that uses
fuzzy matching logic to match data that is nearly the same. As a generic
component CLUE® is useful in many ways.
Few years ago they were specialising in cleaning data and
merging data from several sources into one database.
They had some fancy algoritms to detect double entries in
the database. They were very convincing in their presentation,
but I have no experience with their range of products.
good luck,
ben brugman
"Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
news:a5fd468a.0409032125.35235ea@.posting.google.com...
> Hello All
> We have a data migration project. This is basically what we plan to
> achieve we have a legacy system which as some data with German long
> texts like material texts,etc
> We want to take data coming in flat files which as this german texts
> and convert this to English text the texts are primarily products
> names please advise what tools external or whatever is available to do
> this translation to english the texts are about 15 to 20 characters
> max.
> Next we have data coming from legacy systems that we want to cleanse
> like identify duplicates based on things like addresses:
> eq: we might have a customer called ABC one at address 118 Main
> Street, Nashua and another same customer defined in our another legacy
> system called ABC Inc but address at Main Street, Nashua
> We want a data cleansing tool or library that works on top of SQL
> Server that can do this kind of data pattern identification,etc
> Please advice free tools and tools within SQL if I am not aware of
> that can do this as well good 3rd party tools that can do this.
> Thanks
> Karen|||Another thing you might look at for address cleaning is CASS certification.
This is a standard set by the US Postal service, and there is much software
around to help identify bad addresses, but it does not address your other
data quality problems.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
news:a5fd468a.0409032125.35235ea@.posting.google.com...
> Hello All
> We have a data migration project. This is basically what we plan to
> achieve we have a legacy system which as some data with German long
> texts like material texts,etc
> We want to take data coming in flat files which as this german texts
> and convert this to English text the texts are primarily products
> names please advise what tools external or whatever is available to do
> this translation to english the texts are about 15 to 20 characters
> max.
> Next we have data coming from legacy systems that we want to cleanse
> like identify duplicates based on things like addresses:
> eq: we might have a customer called ABC one at address 118 Main
> Street, Nashua and another same customer defined in our another legacy
> system called ABC Inc but address at Main Street, Nashua
> We want a data cleansing tool or library that works on top of SQL
> Server that can do this kind of data pattern identification,etc
> Please advice free tools and tools within SQL if I am not aware of
> that can do this as well good 3rd party tools that can do this.
> Thanks
> Karen|||I would suggest calling a company like www.listadvantage.com and have
the names cleaned and cass certified.
they r self serve and very easy to work with
"ben brugman" <ben@.niethier.nl> wrote in message news:<OlCP4zAlEHA.536@.TK2MSFTNGP11.phx.gbl>...
> Try the following link :
> http://www.itcg.nl/
> From their site :
> CLUE
> Have you ever experienced problems with duplicate customers,
> addresses, products or any other information?
> CLUE®, the CLUster Engine from ITCG is a generic component that uses
> fuzzy matching logic to match data that is nearly the same. As a generic
> component CLUE® is useful in many ways.
>
> Few years ago they were specialising in cleaning data and
> merging data from several sources into one database.
> They had some fancy algoritms to detect double entries in
> the database. They were very convincing in their presentation,
> but I have no experience with their range of products.
> good luck,
> ben brugman
>
> "Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
> news:a5fd468a.0409032125.35235ea@.posting.google.com...
> > Hello All
> >
> > We have a data migration project. This is basically what we plan to
> > achieve we have a legacy system which as some data with German long
> > texts like material texts,etc
> >
> > We want to take data coming in flat files which as this german texts
> > and convert this to English text the texts are primarily products
> > names please advise what tools external or whatever is available to do
> > this translation to english the texts are about 15 to 20 characters
> > max.
> >
> > Next we have data coming from legacy systems that we want to cleanse
> > like identify duplicates based on things like addresses:
> >
> > eq: we might have a customer called ABC one at address 118 Main
> > Street, Nashua and another same customer defined in our another legacy
> > system called ABC Inc but address at Main Street, Nashua
> >
> > We want a data cleansing tool or library that works on top of SQL
> > Server that can do this kind of data pattern identification,etc
> >
> > Please advice free tools and tools within SQL if I am not aware of
> > that can do this as well good 3rd party tools that can do this.
> >
> > Thanks
> > Karen

daily transfer from oracle schema to similar SQL-Server

I have some small (<5000 records) Oracle tables that I need to transfer daily to SQL-Server.

The old legacy standalone Oracle system here in Hong Kong must still be used to enter property data, so I cannot change it. The idea is to get this data in every day into an SQL-Server. I am sure they can be connected over the company's LAN if not so already. The aim is to then put together a basic front-end for this data. Both systems although fairly similar (property web site data), their schemas are fixed and the SQL-Server schema is in use by another branch of the company for a U.K. property site. The Hong Kong team want me to get their legacy data in to this new system every day and then do a similar web site for their HK data.

Is there a way where I can, say automate DTS to run every day on the SQL-Server machine, get the Oracle tables over the LAN to SQL-Server, then write scheduled stored procedures that massage the data from one schema to the other (I will have to lose a bit of data, since they are not exactly the same tables and fields, though with property data, it is all pretty much the same).

I once did this using ASP (after getting the data into a CSV file from an ACCESS database). The script looped through each line of data and put it into the relevant table.

Anyone got any ideas? Much appreciated. Itry using a linked server to transfer the data from the oracle server. It is much more faster . Write a stored procedure for the same and then schedule it as a job|||Originally posted by Enigma
try using a linked server to transfer the data from the oracle server. It is much more faster . Write a stored procedure for the same and then schedule it as a job

Thanks. I will meet their IT admin guy next week. So, I will have an Oracle DB and a SQL-Server DB. I am familiar with Enterprise manager, I can add remote SQL-Server groups. How would I go about setting up a 'Link Server'. In my main job, we use Lotus Enterprise Integrator (LEI), a handy tool that transfers data from Lotus Notes to anything else, but not sure of a tool for my needs.

I'm more from a programming background. Can you explain the Link Server setup. The scheduled SP's I've done before.

Many Thanks|||You can look in books online and search for "linked server". Also, in the enterprise manager under the security directory are the linked servers visible.|||Originally posted by jora
You can look in books online and search for "linked server". Also, in the enterprise manager under the security directory are the linked servers visible.

Thanks guys, I've just trawled though a few of these pages. Seems DTS is a safe option. I've just started on Enterprise manager, trying to create a package in the meantime to transfer data every day from some test SQL table from one DB to another DB on our sql-server. Looks like I should go over to the client next week, and setup a DTS package on their SQL-Server, try and connect to their Oracle tables and go from there.

I'll check ot the LINK server, I'll need to connect to their legacy Server

Cheers