MS-EXCEL convert to mysql dbase?

mutant

Hi Does anyone knows how to convert my MS-Excel file format from microsoft to MySQL dbase in Linux. Do I have to download a certain software apps for Linux to be able to convert this file?

Thanks ahead...!

Anon

There are many way to do this. I usually create the table in mysql and access it through myodbc in MS Access. Then I copy and paste the data from Excel into the table in Access. This way the data goes directly into the mysql database table.

Good luck.

Lucas

Anon

Try to use Access With ODBC.

Other easy way to do this is exporting
the sheet to a text file and then import to mysql

Anon

I normally use access, and import the Excel spreadsheet. Rename the first row in Excel to the field names you want for each column. It takes a bit of work sometimes, especially if the date fields aren't consistent.

Once you have it in access, setup an ODBC connection to MySQL on your PC.

Select Export in Access, and choose ODBC database, and thats it. You may have to change some of the column types.

mutant

After I choose ODBC database, how can I import it to mySQL in linux?

Anon

Have you setup an ODBC connection to your MySQL database on Linux?

Once that is done, when you select export from access, select ODBC Databases.

Give your table a name.

Select the name of the ODBC you created (on the machine tab I think).

Click OK

And the table should be in MySQL (providing the username provided has the access rights to create tables). It works ok in a Windows 98 / NT Server environment.

Anon

You need to atach or link the MySQL table to your Access DB.

-> In Tables, right click the mouse, select Link Tables/Type ODBC Databases and select the apropriate MySQL ODBC Driver or DSN file. The ODBC Driver need to be installed in your machine).

Import your Excel Sheet to your Access Data Base.

-> In "Tables", right click the mouse, select "Import" and then select Type: "Microsoft Excel .xls".

Wait for the wizard and follow the steps until import the Excel file.

A table will be created inside your Access DB.

The next step is creating a query to "append" the records provided by the "Excel imported table" inside the MySQl linked table and this is a easy task to do with Access.

If everything goes wrong, try to export the Excel data to a text file and, using MySQL client utility, import the file to the table.

See MySQL command "LOAD DATA... INFILE..." to know how this works.

Have a nice day.

mutant

Thanks for all your replies, but I think what your trying to say is that I have to link my dbase file in MS-Access format link with mysql in Linux. What I wanted to do in my part is to put the dbase file in MS-Access on MySQL server in Linux box. Can you teach me how to do it? Coz Im having a hard time transferring these file to my linux box. I want to have it stand alone in my Linux Server not dependendable to MS-Window(ODBC). Is there any other way to do this? Please help....again thanks

Anon

You only depend on the MyODBC connection to transfer from Access into MySQL. Once its into MySQL, thats it - You have your standalone version, and don't have to touch your access version again.

Follow the steps in my earlier post and it should work - I've converted loads of Excel speadsheets into MySQL recently. But you MUST create a MyODBC connection. There may be other ways, but this works for me.

Anon

If you have Microsoft Access dbase or Excel spreadShhets and want to put them in your Linux box you need:

First case - withou using MyODBC

1 - Create a table in MySql with the structure necessary to save your MS data;
2 - If you need more tables, create them all;
3 - If you DON'T WANT to use MyODBC, you need to EXPORT the MS data to a text file.
In this case the fields must be separeted by a character (like ';' for example) and you can use another character to enclose de fields ( like " for example).

In some MySQL versions the latest field must be followed by the field separator too.

Example:

"FIELDS #1";"FIELDS #2";"FIELDS #3";...;"FIELDS #N"; 

Observe the ";" after the FIELD #N

MS Access have good tools to export tables or queries to text files. You 
can export them with fixed column width or using text delimiters. And, propably,
Excel have the same tools too.

But what you need to put in your mind is that the text file must reflect the correct 
structure of your MySQL table. Each field in the text file (left to right) must correspond to the MySQL column 
table (up to down or left to right).

4 - After creating the text file, copy it to you Linux box. If you don't use Samba,
try a FTP session or floppy disks;

5 - Use mysql client to import the text file to the MySQL table. You need the privileges, of course, to login the database and to access the target table and to insert data in the table;

6 - See the 'LOAD DATA ... INFILE ' MySQL Command to know how this works.

Second case - Using MyODBC

1 - Create a ODBC connection using the MyODBC driver. The MyODBC must be installed in your machine. See Control Panel/ ODBC data sources. Follow the steps to configure a DSN to link you with your MySQL database;

2 - Open you Access DB;

3 - Select the appropriate table;

4 - Right click the mouse over the table and select Export. In Save as Type, select ODBC database;

5 - The Wizard will ask you to select a DSN (Data Source Name) which will link you with the ODBC database. In this case, select the DSN created with the MySQL ODBC Driver.

6 - And you need to login with a user with privileges to create table and insert data;

7 - After this, the table will be CREATED in your MySQL Database.

third case - Using MyODBC

1 - Create a ODBC connection using the MyODBC driver. The MyODBC must be installed in your machine. See Control Panel/ ODBC data sources. Follow the steps to configure a DSN to link with the you MySQL database;

2 - Open you Access DB;

3 - Right click the mouse over the Tables section and select Link Table. In Type, select ODBC database;

4 - The Wizard will ask you to select a DSN (Data Source Name) which will link you with the ODBC database. In this case, select the DSN created with the MySQL ODBC Driver.

5 - All tables in the database will be visible to you. Select the target table;

6 - After this, the table will be LINKED in your Access Database.

7 - Now append "Microsoft Data" to the linked table. You can use a Append Query, created in Access, to insert data in MySQL table.

INSERT INTO MySQLTable (f1,f2,f3,...,fn) SELECT (f1, f2,f3,...,fn) FROM AccessTable ...

Have a nice day.
Paulo Caldas

mutant

I follow every steps but I cant establish a connection to mySQL server from MYODBC in windows. I just installed myODBC in my winNT workstation and follow every step but cant connect, why? is there a problem?

Anon

The easiest way to do it is to save the excel sheet as a csv file and then import into mysql.