Excel to MYSQL

[seb87]

howzit guys,

i want to be able to put my whole database structure in excel like:

table,fieldname,field_type,field_null OR not_null,etc.
and be able to generate a CREATE table statements from excel into a text document

is there any applicationto do this?? i looked for hours on the net already.

thanks

 

[dequadin]

Maybe a CASE tool can do this for you. Look on SourceForge there are lots of free ones...

 

[seb87]

case tool?

 

[Drake2007]

You will have to use VBA scripting within Excel, start with a form button on_click event to trigger writting the statement.

 

[Drake2007]

CASE tool? I too am intrigued.

 

[dequadin]

Yes a CASE Tool

 

[Dark Agent]

Yes a CASE Tool

thought something Like CASE

One you use to program
:

 

[dmsoft]

Hello,

Take a look at the tool at http://dbconvert.com

 

[murraybiscuit]

you can also download the mysql odbc connector from mysql website. this will allow you to add a data connection in excel and sync between the two. I did it in access. Don't see why it shouldn't work in excel. if this is what you're wanting to do. the op is a bit unclear.

 

[Nod]

Convert your data to csv files.
You can then load the csv file directly into your MySQL DB by using something like:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n';

Line termination will be '\r\n' if you're using windows.

You will also have to create the table, before importing the CSV, obviously.
If you have headings in your csv, you can use that to create your tables.

More info here.

Importing 500000 lines should take not more than 5 seconds or so.

 

[dequadin]

My understanding of what OP wants to do is something like this:

Have the following in excel

tblMyTable, Field_1, varchar(32), NOT NULL,
tblMyTable, Field_2, varchar(16), NULL,
tblMyTable, Field_3, int, NULL,
tblMyTable, Field_4, int, NOT NULL,

And then generate the following SQL (this is SQL Server 2005)

USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblMyTable](
	[Field_1] [varchar](32) NOT NULL,
	[Field_2] [varchar](16) NULL,
	[Field_3] [int] NULL,
	[Field_4] [int] NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

I don't think he's trying to do bulk imports/sync actual data between excel and MySQL... Is there any other option other than some custom VBA code to do this?

 

[greggpb]

If you keep a sheet per table its pretty simple, no vba ...

You cant do it all on one sheet with excel if the records are sorted.. if they are not you have to move to a vba macro


do you want to build a crept per table or one giant script ?

 

[greggpb]

basically done it in Excel, send me an email address..