Data integration

[rotten69]

Hi there,

I'm supposed to integrate different sources of data into one database. I am given these types of sources:

• .txt
• .csv
• .html
• .xls
• access file as well.

what attributes should I be looking at to construct a global schema because all of them have different attributes? But I can say there are a few things in common between them like name, DOB, Country, CountryName.

A few hints on how to approach it will be good. Any useful website that illustrates or explains well the concepts would be awesome.

Thank you.

[urtrivedi]

All major DBMS provide file import utility. where you can spceify datatypes/ field separator and record seprators.

Look at your all sources, and find all distinct columns and create table in your destination database with all columns you found in all sources

then case by case (file format by file format), import your files to the new table using import tool

if you using oracle as destination table, you can also take help of sqldeveloper (u can download from oracle website for free). It has facility to import several file formats.

[debasisdas]

1. First of all you need to decide on your target Db structure as per your actual requirement.
2. Next you need to create a data mapping document that maps table wise and field wise from heterogenous source system to target system.
3. Load the data from different source sytem into temp tables.
4. Finally move the source data in temp tables to target tables by implementing some business logic.

[Kevin@Bittwist]

Hey, my co-founder and I started Bittwist to make it easier to deal with exactly that type of problem without spending all the time and money for an enterprise data integration vendor. Combining and quality controling data from excel and various text delimited files is our primary inital use case. And if its a job you need to schedule and run we automate that and provide notifications etc.
We are in closed beta right now and would love to have you give our solution a try, for free of course, and give us some feedback on your experience. You can email me directly at kevin.jemison(AT)bittwi.st and i'll set you up, or you can go to the website and sign-up.

Cheers
Kevin J
Co-founder, Bittwist
http://bittwi.st

[rotten69]

Can anyone post links to online tutorials or something that I can read on? cheers

[rotten69]

Thank you for the replies.

One of the files contains about 3000 records or more. What is a possible way of importing them all into the database using SQL command line? Because it is time-consuming to go through each record and add this line "INSERT INTO table_name ........" to it.

[debasisdas]

What is the file format of source data ?

[rotten69]

the file format is as mentioned in the first post. if that's what you're asking?

[debasisdas]

I was asking asking about the file that is containing 3000+ records as mentioned by you.

[rotten69]

it is a text file format (.txt). Primarily, I need to integrate all the file formats mentioned in the first post.

Cheers,

[debasisdas]

You need to convert the file into .CSV format. Then use SQL Loader to upload the data into Oracle table.

It is better to convert all the files into .CSV format and then upload into oracle.