3

I have a table which has approx. 10,000 client records. The table structure look like this:

ClientID | FirstName | LastName | PhoneNumber

I am trying to update the PhoneNumber column with a updated Excel file, which only has two columns, ClientID | PhoneNumber

My cpanel has phpmyadmin installed, but how do I import the excel file into the database and only update the PhoneNumber based on ClientID?

Thank all!

sharonjoys
  • 31
  • 1
  • 2
  • Is this a once off solution? If so then @octern's approach is a good one. If not you can connect to your SQL DB directly from excel using ADO in VBA – Dan Aug 23 '12 at 10:42
  • @Dan it could be good if you make an answer for your approach. – PhoneixS Jun 11 '13 at 11:53
  • possible duplicate of [How i can import a file(csv/excel) with partial data to table in database through phpmyadmin?](http://stackoverflow.com/questions/781127/how-i-can-import-a-filecsv-excel-with-partial-data-to-table-in-database-throug) – Jeremy J Starcher Jun 12 '13 at 03:55

2 Answers2

6

Simple, tricky, and clever solution:

Copy it do datasheet with Columns:

SQL | PhoneNumber | SQL | ClientID | SQL

Sample data:

Column 1: UPDATE clients SET phonenumber = ' (copy it to every row)

Column 2: 12345

Column 3: ' WHERE ClientID= (copy it to every row)

Column 4: 123

Column 5: ; (copy it to every row)

Save it as CSV (space separated, without quotes), import as SQL

Peter
  • 16,453
  • 8
  • 51
  • 77
  • Wouldn't that sql file contain a lot of extraneous commas (or tabs, etc)? – octern Aug 22 '12 at 21:40
  • To be honest i don't what about excel, but in OpenOffice you can save CSV without quotes, and use space as separator. Anyway it's not a problem to remove quotes from file in text editor. – Peter Aug 22 '12 at 21:40
  • I don't believe excel makes that option available normally. Anyway, even if the delimiter were a space, it would still mess with things. e.g., if phonenumber is being handled as a string, it will save it as " 555-5555 " instead of "555-5555". – octern Aug 22 '12 at 21:43
  • it's not a problem to replace all `" "` occurencies in text editor to ` `. Converting, replacing, and importing is work for 2-3 minutes :) – Peter Aug 22 '12 at 21:45
  • Perfect trick! Amazing solution. – Sylvester Nov 14 '22 at 21:32
6

I don't know if that's possible, but you can easily use the excel spreadsheet to generate a series of MySQL commands to update the phone number. If the ID is column A and the phone number is column B, then the function would just be:

concatenate("update yourTableName set phonenumber = '", A1, "' where ID = ", B1, ";")

Then fill down and copy the resulting commands into PHPMyAdmin's SQL tab.

octern
  • 4,825
  • 21
  • 38