in reply to From excel to mysql...

Am I on the right track? Is there perhaps a more direct conversion path from Excel to mysql?

There is. Just save the data as a tab separated txt file from within Excel. This is a standard save as option. Then do this in mysql client after you have created the table (which you can also do in mysql client): 

mysql> LOAD DATA LOCAL INFILE '/path/to/datafile.txt' INTO TABLE table
+_name;

[download]

It will load the data at a rate > 10,000 records a second so it should not take too long. The only reason to do it the slow way using DBI and DBD::mysql is if you need to do some on the fly data munging in transit.

You would create the table in the mysql client like: 

mysql> CREATE TABLE jokes ( id int not null, name char(32), timestamp 
+date, joker char(32) );

[download]

You can use ALTER TABLE to add keys after the import or create the keys with the table. If your fields are longer than the allocated space they will be uncerimoniously truncated to fit so allocate enough room. Note char() is faster than varchar() per se but uses more space.

cheers

tachyon

Replies are listed 'Best First'.
Re: Re: From excel to mysql...
by kiat (Vicar) on Apr 13, 2004 at 15:03 UTC
    Wow, thanks! I've never dreamt it could be so easy :)

    Btw, what if some of the columns have empty values? Would it still work. I'll know it when I try out later...

[reply]

      Welcome to the non M$ world ;-) The is a substantial upside to not rotating around the GUI.

      Anyway with empty fields yes it *should* still work. Excel dumps empty fields as 

      field1\t\tfield3\tfield4

      [download]

      So when mysql splits on \t you get an undef (NULL) value. In that case add "default 'blah'" to the create ie: 

      timestamp date default '1970-01-01',
joker char(32) default 'anon',

      [download]

      and mysql will fill in the blanks.

      If your data is really dirty you may wish to parse the tab sep file with perl, check for whatever then write out all the clean data to a fresh tab sep file before you import. You could flag the errors, write them to a tab sep file and then open and fix it in Excel or just automate it.

      One thing to be aware of is that you probably will want a primary key on id (so id must be not null and must also be unique). The other key/index options you will probably want is an INDEX on some/all of the other fields depending on how you plan to search it.

      cheers

      tachyon

[reply]
[d/l]
[select]

        ++ great information.

        I am not a big fan of Microsoft, but it is also very easy in MS Sql Server. Data Transformation Services (included with Sql Server) can import Excel files without first saving them in another format. I can't say which is easier, that would be opinion.

[reply]
        I experimented with a small set of data and so far the results are great :) I've deliberately left some fields empty in Excel. I didn't specify default values for the columns in the create statement but every value (even null values) are correctly inserted.

        cheers

[reply]
Re: Re: From excel to mysql...
by diotalevi (Canon) on Apr 13, 2004 at 15:20 UTC
    Tab delimited?! Ew! That just makes it difficult to read records that span lines.
[reply]

      Actually impossible from the point of view of an import utility: 

      LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]
    [LINES 
        [STARTING BY '']    
        [TERMINATED BY '\n']
    ]
    [IGNORE number LINES]
    [(col_name,...)]

      [download]

      Embedded newlines make it hard to define what represents a 'row'. Fortunately AFAIK you can't insert newlines into an Excel cell string as it triggers moving you to a new cell.

      cheers

      tachyon

[reply]
[d/l]

        It is possible to enter newlines in an Excel cell, using ALT ENTER. The fields are then escaped in the CSV or TAB file using double quotes. So Hello\nWorld would look like this: 
            "Hello\n
    World"

        [download]

        --
        John.

[reply]
[d/l]

        I regularly work with Excel data with newlines inside a single cell. This is why CSV is preferrable over tab-delimited.

        "Terry","Joan","Sourced from $BAR list
04/03: Send Info
09/04: Info sent","7 - Voting for turnip"

        vs

        Terry	Joan	Sourced from $BAR list\
04/03: Send info\
09/04: Info sent	7 - Voting for turnip
[reply]
Re: Re: From excel to mysql...
by Zaxo (Archbishop) on Apr 14, 2004 at 08:09 UTC

    There is also a command line program called mysqlimport which is delivered with mysql. It is a front end for tachyon's excellent suggestion. It doesn't seem to have a man page, but you can find it in the mysql info pages - info mysql.

    After Compline,
    Zaxo

[reply]

In Section Seekers of Perl Wisdom