Dynamically create table and insert data at runtime

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?
James_ZhanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ioannis AnifantakisSoftware EngineerCommented:
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
eicheledCommented:
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
James_ZhanAuthor Commented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Ioannis AnifantakisSoftware EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ioannis AnifantakisSoftware EngineerCommented:
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
Ioannis AnifantakisSoftware EngineerCommented:
AWAITING RESPONCE PLEASE
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.