Unsupported Screen Size: The viewport size is too small for the theme to render properly.

Rookie Needs Help Creating Table From Spreadsheet

forums forums SQLyog Using SQLyog Rookie Needs Help Creating Table From Spreadsheet

  • This topic is empty.
Viewing 4 reply threads
  • Author
    Posts
    • #12244
      DennisM
      Participant

      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

    • #31911
      peterlaursen
      Participant

      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!

    • #31912
      DennisM
      Participant

      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

    • #31913
      peterlaursen
      Participant

      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.

    • #31914
      DennisM
      Participant

      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

Viewing 4 reply threads
  • You must be logged in to reply to this topic.