6 Replies - 18988 Views - Last Post: 10 July 2012 - 10:43 PM Rate Topic: -----

#1 suri2suresh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 21-November 08

How to transfer data from excel to DB tables

Posted 22 November 2008 - 12:01 AM

Hi,
I would like to explain about my project.
My application is going to provide some study meterial (Web based e-learning) and self assessment. All the information will be stored in DB tables. That means the pages will be dynamic not static. The Information is in the form of tables,word file, picture[.jpeg, .bmp] and excel. Under topic the actual information will be stored. The information will be displaying section wise. [under section modules, under module topics, under each topic the actual information will be stored]

Also i need to develope one search utility for the entire web site which can search through all my DB tables based on the search string.

Now the following are the questions i have

1) I have huge data[thousands of records] in excel sheet. Is there any way to copy the data from excel to DB table other then manually entering the data.
2) DB tables will be having some thousands of records. Is there any way for faster retrieval of information.
3) Is there any better way to store and retrieve all my word file, excel file, and pictures from Databse.

Thanks in advance....

Is This A Good Question/Topic? 0
  • +

Replies To: How to transfer data from excel to DB tables

#2 Martyr2  Icon User is online

  • Programming Theoretician
  • member icon

Reputation: 4188
  • View blog
  • Posts: 11,857
  • Joined: 18-April 07

Re: How to transfer data from excel to DB tables

Posted 22 November 2008 - 12:51 AM

1) Yes, most databases have an import feature which will allow you to import excel files directly into the database. SQL Server does this very easily through the import data wizard in enterprise manager. If it doesn't directly support importing excel files, you can often save excel sheets as comma or tab delimited files which can then be imported. Look up your specific database for information on importing these files.

2) Yes, there are many ways to speed up retrieval of information from how you structure the records, to how you organize them to even the hardware of the database server your database is on. I would start by first looking up information on setting up keys and how to setup indexes on fields of a table. Through a combination of good keys, normalized tables and indexes on the proper fields, databases can retrieval records very quickly. Also take a look at how you structure your queries and optimize them to take advantage of your index values and cut down on multiple joins and deep nesting of queries... you can avoid a lot of those mistakes through normalization of tables (look up 3rd normal forms for more information). And lastly hardware on a server can make a difference. Databases on RAID 5 drives for instance have slight advantages for reading data compared to RAID 1 configurations.

3) Typically I advise people building databases to never store big bulky items in database records like images, video, or big binary objects. Instead store them on the computer and only store a reference to that file. This is usually in the form of a file path. The reason to do this is that A.) it makes retrieval faster because the database doesn't have to process such big chunks of info as it is looking for something specific B.) You may often want to make filenames searchable and it is great to be able to conduct a simple word search on a string representing the path than trying to handle something icky like a blob data type.

Hope these answer your questions. :)
Was This Post Helpful? 0
  • +
  • -

#3 suri2suresh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 21-November 08

Re: How to transfer data from excel to DB tables

Posted 23 November 2008 - 12:05 AM

Thank you Martyr2,

The information is prety much useful to me.
I will start doing it. I can get back if i have any questions.

Thanks once again :-)
Was This Post Helpful? 0
  • +
  • -

#4 brigzy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-October 09

Re: How to transfer data from excel to DB tables

Posted 17 May 2012 - 11:57 PM

Hi there,

I have been developing a utility 'Excel to Database' that I believe will be just what you need - working with SQL Server, MySQL, Oracle, Progress, DB2, FoxProm Dbase etc.

Please see my project page:

My link

I am still in development but apreciate any support with testing and ideas.


Richard
Was This Post Helpful? 0
  • +
  • -

#5 brigzy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-October 09

Re: How to transfer data from excel to DB tables

Posted 21 May 2012 - 03:57 AM

Correct link for Excel-to-database is :
http://leansoftware.net/forum

View Postbrigzy, on 18 May 2012 - 12:57 AM, said:

Hi there,

I have been developing a utility 'Excel to Database' that I believe will be just what you need - working with SQL Server, MySQL, Oracle, Progress, DB2, FoxProm Dbase etc.

Please see my project page:

My link

I am still in development but apreciate any support with testing and ideas.


Richard

Was This Post Helpful? 0
  • +
  • -

#6 busyme12srv  Icon User is offline

  • New D.I.C Head

Reputation: -5
  • View blog
  • Posts: 44
  • Joined: 18-June 12

Re: How to transfer data from excel to DB tables

Posted 21 June 2012 - 01:33 AM

You have not specified which database software you are using.
Therefore,I am specifying two methods to import msexcel data in to oracle 10g database.

(I)SQL*Loader (sqlldr) is the utility to use for high performance data loads. The data can be loaded from any text file and inserted into the database.

Steps:

1. Create a text-file (.txt) file eg. datafile.dat from MSEXCEL database file. Steps are:
(i) Save the msexcel file as datafile.txt by choosing .txt option for the format in the 'Save As' option.
in msexcel.

2. Replace the field delimiters by ','(comma)

3. Start 'Run SQL Command Line' option in Oracle Database software.

4. At the Sql> command prompt in Oracle, using CREATE TABLE command to create an oracle table with the respective fields.

5. At the Oracle C:/> prompt create file datafile.CTL using the following command:
LOAD DATA INFILE datafile.dat INTO TABLE NEWDATA replace fields terminated by ',' ( Sno,Name,Address,Designation)
6. At the C:/> again apply the SQLLDR command to create the database file in oracle.
7.SQLLDR username/password DATA=datafile.dat CONTROL=datafile.CTL LOG=datafile.LOG
After you have executed the above command SQL Loader will shows you the output describing how many rows it has loaded.

The LOG option of sqlldr specifies where the log file of this sql loader session should be created. The log file contains all actions which SQL loader has performed i.e. how many rows were loaded, how many were rejected and how much time is taken to load the rows and etc. You have to view this file for any errors encountered while running SQL Loader.


(II) Importing MSEXCEL data into the Oracle 10g database:

Start Oracle 10g database->Goto database Homepage->Click onUtilities
->Change to Data Load/Unload->Choose Spreadsheet option

In this window you can load several different formats of data including Text, Spread Sheet and XML. We will be loading the Employees.XLS we created from the Northwind database, therefore click on Load SpreadSheet Data icon.
You will be asked to enter the Name of Database file, Primary key etc. thru the wizard. Follow the wizard in Oracle 10g,till FINISH option.
It will display the number of rows of data entered in the database file.
Was This Post Helpful? 0
  • +
  • -

#7 sivatendulkar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 10-July 12

Re: How to transfer data from excel to DB tables

Posted 10 July 2012 - 10:43 PM

View Postsuri2suresh, on 22 November 2008 - 12:01 AM, said:

Hi,
I would like to explain about my project.
My application is going to provide some study meterial (Web based e-learning) and self assessment. All the information will be stored in DB tables. That means the pages will be dynamic not static. The Information is in the form of tables,word file, picture[.jpeg, .bmp] and excel. Under topic the actual information will be stored. The information will be displaying section wise. [under section modules, under module topics, under each topic the actual information will be stored]

Also i need to develope one search utility for the entire web site which can search through all my DB tables based on the search string.

Now the following are the questions i have

1) I have huge data[thousands of records] in excel sheet. Is there any way to copy the data from excel to DB table other then manually entering the data.
2) DB tables will be having some thousands of records. Is there any way for faster retrieval of information.
3) Is there any better way to store and retrieve all my word file, excel file, and pictures from Databse.

Thanks in advance....


By using ssis tool u can easily add your data from xl sheets(textfile or word file ect) ssis is one of the ELT too
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1