Rookie Needs Help Creating Table From Spreadsheet

[DennisM]

Hi,

Please forgive this beginners request for help.

I have very little knowledge of MySQL and SQLyog. I have a MySQL database (named: OESCA) on my hosting account. I can access it with SQLyog.

I have been given our clubs membership database on an Excel spreadsheet (40 fields, 600 records). If possible, can someone please give me the steps I need to take to create a new table from this spreadsheet. I have not used SQLyog to create a table or database, only to view & edit them.

Thank You,

Dennis

Edit: I attached a sample of the spreadsheet

[peterlaursen]

There are 2 different ways to import an Excel spreadshet.

1) From Excel save the data as a .csv file. Now you can create an empty table with SQLyog. Right-click the database in the Object Browser, create … , create table. However with this option you will have to specify datatypes for each column yourself. Once the table is create, right-click on the table and select Import external data .. Import using LOAD DATA. This option is available in every SQLyog version.

2) You can import using ODBC. This option requires ENTERPRISE or ULTIMATE versions of SQLyog (or the TRIAL). But the advantage is that SQLyog will create the table and you will not need to do yourself. Here you right-click the database, select import .. import external data. The Data Import wizaard will start. In the second screen you can select 'Excel' as an option. However the ODBC-driver for Excel is not always working too well. If you face problems when importing directly from the Excel-file you may try to open the Excel-file in Access and save as an Access database (.mdb) and select the 'Access' option instead.

Please try to go ahead and if you get stuck in the process please tell where you get stuck and we will try to help from there!

[DennisM]

Thank you Peter,

I'm doing that now. I have another question, does the varchar datatype have a 255 length limit? The MySQL manual makes my head spin. If it does have the 255 length limit is there a datatype that has a larger one (for lengthy notes)I suppose 2000 would be plenty? The MySql Server version: 5.0.91-log

Thanks

Dennis

[peterlaursen]

The length of varchar was increased from 255 to 65.000+ in MySQL 5.0.

http://dev.mysql.com/doc/refman/5.0/en/char.html: Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. Note that the max-lenghd is *in bytes* – so if you use a multibyte character set (like UTF8) it is not *in characters*.

Try

Code:

CREATE TABLE vctest(vc VARCHAR(20000));
SHOW CREATE TABLE vctest;

.. it works with any charset for the table (except utf8mb4 – what can be up to 4 bytes – introduced in MySQL 5.5).

But if you

CREATE TABLE vc2test(vc VARCHAR(30000));

.. (while using UTF8) you will get

— Note Code : 1246

— Converting column 'vc' from VARCHAR to TEXT

.. because in UTF8 character may be 3 bytes each and 3*30000 = 90000 bytes is too much for a varchar.

Also note that only varchar() and not char() can exceed 256 bytes.

[DennisM]

Hi Peter,

Because I purchased the Professional version, I spent quite a while trying to get your 1) suggestion to work. But I kept getting error 1148 trying to do the import once a created the table.

Then I decided to try your 2) suggestion, so I downloaded the trial and gave it a go. Everything worked perfectly.

Thank you very much, also thanks for the great explanation of varchar, especially the note that the size is in bytes.

Dennis

[Author]

Posts