Method and apparatus for loading data from a spreadsheet to a relational database table

• US 20070162504A1
• Filed: 01/10/2006
• Published: 07/12/2007
• Est. Priority Date: 01/10/2006
• Status: Active Grant

• Current Assignee: X Corp. (f/k/a Twitter, Inc.) (X Holdings Corp.)
• Original Assignee: International Business Machines Corporation
• Inventors: Chivukula, Balakrishna, Rajendran, Kuppayi
• Granted Patent: US 7,685,152 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

A method and apparatus is provided for loading data from a spreadsheet into a dynamically created relational database table, based on conditions provided by a reference table. Alternative types of transmission links, such as E-mail, FTP and Internet web interface, may be used to send a spreadsheet to a location for processing, and security measures are provided for each type of transmission. One useful embodiment is directed to a method for transferring data from a specified spreadsheet, having a name, rows and columns, to a relational database table. The method includes the steps of receiving the spreadsheet at a given location, assigning a name to the relational database table that is derived from the spreadsheet name, and creating names for columns of the table from the contents of a first row of the spreadsheet. Data types for respective columns of the table are derived from the contents of a second row of the spreadsheet. After creation of the table, data is loaded into locations of the table from respectively corresponding cells of the specified spreadsheet.

Claims

1. A computer implemented method for transferring data from a specified spreadsheet having a name, rows and columns to a relational database table, said method comprising the steps of:

receiving said specified spreadsheet at a given location;

assigning a name to said relational database table that is derived from said spreadsheet name;

creating names for respective columns of said table from the contents of a first row of said specified spreadsheet;

deriving data types for respective columns of said table from the contents of a second row of said specified spreadsheet; and

loading data into locations of said table from respectively corresponding cells of said specified spreadsheet.

2. The method of claim 1, wherein:

said transfer of data requires completion of an authentication procedure to affirm that said specified spreadsheet may be uploaded into a repository at said location that is associated with said relational database table.

3. The method of claim 2, wherein:

said specified spreadsheet contains identification information for use in said authentication procedure.

4. The method of claim 2, wherein:

said method includes the step of collecting said spreadsheet name and said contents of said first and second rows of said spreadsheet, after said specified spreadsheet has been uploaded into said repository, for use in constructing said relational database table.

5. The method of claim 2, wherein:

information pertaining to said specified spreadsheet is stored in a reference table linked to said repository, for subsequent use in connection with spreadsheets uploaded into said repository after said specified spreadsheet has been uploaded thereinto.

6. The method of claim 2, wherein:

said specified spreadsheet is uploaded to said repository by using a transmission link selected from a group that includes at least transmission links using FTP, E-mail, and an Internet web page.

7. The method of claim 2, wherein:

said specified spreadsheet has a file type that is queried when said specified spreadsheet is uploaded into said repository; and

if said file type is determined not to be a particular pre-selected file type, said specified spreadsheet is converted to said particular file type.

8. The method of claim 1, wherein:

said data loaded into locations of said relational database table is selectively combined in a document with other information at said given location that has not been contained in a spreadsheet.

9. The method of claim 1, wherein:

after said step of loading data into locations of said table, E-mail notification is provided to the entity that sent said specified spreadsheet to said given location.

10. A computer program product in a computer readable medium for transferring data from a specified spreadsheet having a name, rows and columns to a relational database table, said computer program product comprising:

first instructions for receiving said specified spreadsheet at a given location;

second instructions for assigning a name to said relational database table that is derived from said spreadsheet name;

third instructions for creating names for respective columns of said table from the contents of a first row of said specified spreadsheet;

fourth instructions for deriving data types for respective columns of said table from the contents of a second row of said specified spreadsheet; and

fifth instructions for loading data into locations of said table from respectively corresponding cells of said specified spreadsheet.

11. The computer program product of claim 10, wherein:

said transfer of data requires completion of an authentication procedure to affirm that said specified spreadsheet may be uploaded into a repository at said location that is associated with said relational database table.

12. The computer program product of claim 11, wherein:

information pertaining to said specified spreadsheet is stored in a reference table linked to said repository, for subsequent use in connection with spreadsheets uploaded into said repository after said specified spreadsheet has been uploaded thereinto.

13. The computer program product of claim 11, wherein:

said specified spreadsheet is uploaded to said repository by using a transmission link selected from a group that includes at least transmission links using FTP, E-mail, and an Internet web page.

14. The computer program product of claim 11, wherein:

said specified spreadsheet has a file type that is queried when said specified spreadsheet is uploaded into said repository; and

if said file type is determined not to be a particular pre-selected file type, said specified spreadsheet is converted to said particular file type.

15. The computer program product of claim 11, wherein:

said data loaded into locations of said relational database table is selectively combined in a document with other information at said given location that has not been contained in a spreadsheet.

16. Apparatus for transferring data from a specified spreadsheet having a name, rows and columns to a relational database table, said apparatus comprising:

a repository at a given location for receiving and uploading said specified spreadsheet;

a processor device contained in said repository, for assigning a name to said relational database table that is derived from said spreadsheet name, for creating names for respective columns of said table from the contents of a first row of said specified spreadsheet, and for deriving data types for respective columns of said table from the contents of a second row of said specified spreadsheet; and

a mechanism associated with said processor device for loading data into locations of said table from respectively corresponding cells of said specified spreadsheet.

17. The apparatus of claim 16, wherein:

said repository is provided with means for authenticating said specified spreadsheet, to affirm that said specified spreadsheet may be uploaded into said repository.

18. The apparatus of claim 17, wherein:

said apparatus includes a reference table linked to said repository for storing information pertaining to said specified spreadsheet, for use in connection with spreadsheets uploaded into said repository after said specified spreadsheet has been uploaded thereinto.

19. The apparatus of claim 18, wherein:

said specified spreadsheet is transmitted to said repository by using a transmission link selected from a group that includes at least transmission links using FTP, E-mail, and an Internet web page.

20. The apparatus of claim 19, wherein:

said data loaded into locations of said relational database table is selectively combined in a document with other information at said given location that has not been contained in a spreadsheet.