RATE THIS ANSWER
0
Click to Vote:
- 0
0
i used to convert my csv flat file into spreadsheet and import from sql server. But first I had to re-design my sql table from the flat file data and normalise it. The flat file normally contains all the data in one flat format with each field delimited by some char (eg comma). for eample, a payroll flat file will contain the following:
payno, staffid, name, dateofbirth, payrate, hoursworked, salaryamt, department, gender etc
the sql table design will ne something like this
Employee :empid, name, dob,deptid, payrate
Department: departmentid, name
Payroll ; payrollno, empid, hoursworked, Salaryamt
Employee, Department and Payroll are tables with their corresponding fields.
Each of the table can be represented as a sheet in the spreadsheet worksheet. Sql has features to import the data from spreadsheet and I'm sure Oracle have this feature as well.
When you do this type of conversion, it is importand to bear in mind the requirement for referential integrity of the database tables. That is the the mandatory fields must have data. Check out the foreign keys in each table and make sure that they have data. Sometimes the csv files dont have these kind of data and that will be a problem when you create database tables. Csv files is notorious for missing data, this is because most are a result of reports which are actually generated out of database tables themselves.
For example, in my csv example, there is no empid,deptid, departmentid fields. These fields must be created in your spreadsheet and populated before conversion into database tables.
You can also create a small program that reads from the csv file and write directly to the database tables.
Last Answered:
Jun 25 2009 1:51 AM GMT by Twlp123 
130 pts.
