in reply to Re: From excel to mysql...
in thread From excel to mysql...

Tab delimited?! Ew! That just makes it difficult to read records that span lines.

Replies are listed 'Best First'.
Re: Re: Re: From excel to mysql...
by tachyon (Chancellor) on Apr 13, 2004 at 15:29 UTC

    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,...)]

    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


      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"

      --
      John.

      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