[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Dynamically create table and insert data at runtime

Posted on 2010-04-09
7
Medium Priority
?
604 Views
Last Modified: 2012-05-09
Hi, I am developing an Java standalone application that  allows user to upload an EXCEL table into MySQL.  The Excel table contains contact information. The problem is the EXCEL table may have different column names and different column number. This requres the application can create table and insert data dynamicaly at run time.  How to solve this?
0
Comment
Question by:James_Zhan
  • 4
6 Comments
 
LVL 6

Assisted Solution

by:Ioannis Anifantakis
Ioannis Anifantakis earned 336 total points
ID: 30217644
Firstly, you have to transform your EXCEL file to some text file - tab delimited or coma delimited, or whatever you like.

Thats easy to do it.  Just "save as" from excel and follow the options

then here's some code for this
LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3); 

Open in new window

0
 
LVL 3

Assisted Solution

by:eicheled
eicheled earned 164 total points
ID: 30219159
If I read this right, you want to build a table with the column names as they exist in the Excel file? If that is the case, you may have to dynamically build your CREATE TABLE statement on the fly based on the column names in the file (you will have to extract the column names separate from the data). Then to load the data you can use something like ioannisa suggested.

However, if the column names don't match any existing table, how are you going to determine the data types? And if you have a mismatch of column names and number of columns, how will you load the data into an existing table (if that is your plan)?
0
 

Author Comment

by:James_Zhan
ID: 30303968
The application's user interface  is written in Swing and  access DB by JDBC technology. The applicaiton provide a dialog to allow user to navigate to the EXECL file , and  read the EXECL file, and create table based on the column names on the fly and move data from EXECL to  table.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
LVL 6

Accepted Solution

by:
Ioannis Anifantakis earned 336 total points
ID: 30306396
Ok, so I assume you have the code.

You already know then how to open an excel file and read its contents and produce the necessary CREATE statements - enen the insert statements!

Instead of applying these statements to MS-ACCESS, you produce SQL statements for MySQL and feed your MySQL server with these statements.

So where is your problem!????
0
 
LVL 6

Expert Comment

by:Ioannis Anifantakis
ID: 30306538
Its not different!
You produce SQL Statements.  If you already have that for MS-ACCESS I don't think its difficult to do it for MySQL.

Explain what your problem is exactly.  Because you have the facts to work.  Where are you stuck?
0
 
LVL 6

Expert Comment

by:Ioannis Anifantakis
ID: 31067331
AWAITING RESPONCE PLEASE
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question