CODEWITHSUNDEEP
mysqlexcel
user1366451
user1366451

Reputation: 41

How can I make a script that will dump bulk info from an .xlsx (excel) file?

  1. I need to make a script that will grab my excel file which contains a table. "Industry Partners" .

  2. This table in my database contains 6 columns "id", "name", "address", "lat", "lng", "phoneNumber"

  3. I'm trying to figure out how I can dump the table from the excel file which contains only 3 columns: "name", "address", "phoneNumber". I did not put "ID" because it's (AI) that should be part of the script.

How can I get started in making a script to do this?

Upvotes: 0

Views: 222

Answers (2)

Captain Payalytic
Captain Payalytic

Reputation: 1071

Regarding your comment. I think you mean 'their "industry partner" table' rather than 'there "industry partner" table'. If you make that sort of mistake when programming, the consequences can be disastrous!

Anyway, if you need something repeatable, then you need something of the sort that I recently wrote for a client. There is an excellent library called PHPExcel. Using this you can write a php script to:

  1. Upload the Excel workbook
  2. Read the table of inserts
  3. Insert each row into the mysql table

For the application I did for my client, they wanted to manage the updates totally in Excel, so I included a last_update date/time field in the mysql table, then after inserting/updating all the rows from the spreadsheet, I deleted any that had not been uploaded in this latest run. Obviously, since you are not including a primary key in your Excel file, you will not be able to perform updates.

Upvotes: 1

Captain Payalytic
Captain Payalytic

Reputation: 1071

If this is a one off load, then I tend to just write an Excel formula to put at the end of each row that just builds a mysql INSERT statement. You copy the formula down for all the rows, then paste the results into something like phpmyadmin. Assuming that your data is in columns A, B, C then the formula will look something like:

="INSERT INTO table (name, address, phoneNumber) VALUES ('"&A2"','"&B2&"','"&C2&"');"

The above formula assumes that you have headers in row 1.

Upvotes: 0

Related Questions