Importing into MySQL

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Alan M Dunsmuir
    #1

    Importing into MySQL

    A client has sent me a (Windows) Excel Spreadsheet (.xls) file
    containing a block of data he wants included as a table in a MySQL
    database in a (Linux-based) PHP/MySQL Web application I'm developing for
    him.

    I have both Windows and Linux available on my own machine.

    What is the recommended route to get this .xls file into MySQL?
    Tags: None
    • Rik Wasmus
      #2
      Re: Importing into MySQL

      On Tue, 08 Jan 2008 19:02:57 +0100, Alan M Dunsmuir
      <alan@moonrake. demon.co.ukwrot e:
      A client has sent me a (Windows) Excel Spreadsheet (.xls) file
      containing a block of data he wants included as a table in a MySQL
      database in a (Linux-based) PHP/MySQL Web application I'm developing for
      him.
      >
      I have both Windows and Linux available on my own machine.
      >
      What is the recommended route to get this .xls file into MySQL?
      xls -csv -LOAD DATA INFILE
      --
      Rik Wasmus

        Comment

        • NC
          #3
          Re: Importing into MySQL

          On Jan 8, 10:02 am, Alan M Dunsmuir <a...@moonrake. demon.co.ukwrot e:
          >
          A client has sent me a (Windows) Excel Spreadsheet (.xls)
          file containing a block of data he wants included as a table
          in a MySQL database in a (Linux-based) PHP/MySQL Web application
          I'm developing for him.
          >
          I have both Windows and Linux available on my own machine.
          >
          What is the recommended route to get this .xls file into MySQL?
          Option One.

          1. Format all dates in the spreadsheet as yyyy-mm-dd.
          2. Save the spreadsheet as CSV (comma-separated variables) file
          or tab-delimited text file.
          3. Import CSV or text into MySQL using LOAD DATA INFILE query
          (be sure to specify proper delimiters and enclosures).

          Option Two.

          1. In your spreadsheet, construct an INSERT query for the first
          record, something like this:

          = "INSERT INTO myTable SET id =" & A2 & ", description ='" & A3 &
          "';"

          2. Use Copy and Paste to construct similar queries for all other
          records.

          3. Copy the column of queries to Windows Clipboard and paste the
          queries into your MySQL client program. Alternatively, paste
          the queries into Notepad, save the file as, say, mydata.sql and
          run the queries from command line:

          mysql [your usual options] < mydata.sql

          Cheers,
          NC

            Comment

            Previous template Next
            Working...