Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1811
  • Last Modified:

XLS to MySQL by selecting Columns

Hi experts. I have an XLS file (Excel) which has 90 columns. I want to import into a MySQL database the columns 0,1,2,30,65,68,90. How can I do that?
1 Solution

As long as there are no comma characters in your data, save the excel spreadsheet as a CSV (Comma Delimited) file.  This is a text version of your excel spreadsheet.

Then you can use PHP code to read the csv file one line at a time, splitting the line on the comma character, and reading only the columns you want, then writing these columns into your MySQL database.

However, although the code is simple, it's still a couple of hours work, so I recommend you use an excellent free tool at http://www.phpcsvimporter.co.uk/.  This is a simple PHP script that you just configure to connect to your database, and away you go.

If this doesn't work for you, post back and I'll write some example code for you, or look at alternative solutions.
Steve BinkCommented:
Even easier.

Export the spreadsheet as a delimited file.  Try to use a delimiting character that does not appear in any of the text, but delimited quoted strings is fine, too.  Copy the file to the MySQL server, and import it using the LOAD DATA INFILE syntax (http://dev.mysql.com/doc/refman/5.0/en/load-data.html).  Once the data is in a table, simply delete any columns you don't want.

15 minutes of work if you know what you're doing.  Maybe a little longer to look up syntax if you need to.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now