CSV file to database table

610 pts.
Tags:
CSV format
CSV input file
Hi Experts, I have a question is the csv file can be converted to a database table with same column and data i hav gone through the CSV format tag,not getting idea about this i just would like to know is there any procedure or sysntax to do this thanks for the hlp

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Hi Inprise. You didn't specify what database you are using, but if I remember well, you have been working with Oracle, right ? Could you please provide more details ? How do you want to do this ? what tool are you trying to use ? You could create the table and then import the data using SQL*Loader.
    68,710 pointsBadges:
    report
  • Inprise
    Hi, Thanks for ur info,yes i am into oracle i am not clear on wat possiblities work out jus want to know how to convert flat files to database table (using SQL procedure or command syntax and possibility using tools as well
    610 pointsBadges:
    report
  • Inprise
    i need some script kind to convert the data from CSV format and upload into data base table,is that s possible,can u giv me some suggestion on how to proceed on this ?
    610 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following