Pro Tip: Insert records into a SQL database from an Excel spreadsheet

Here’s a process I’ve been using for years to load data into a SQL database from Excel (and possibly do some transformation in the process).
It may seem obvious to some people but I have known software developers who never considered it.

It’s simple:

  1. Write an INSERT SQL statement on each line of the sheet you need to import (do it for the first row, then use Excel’s “fill down” feature).
  2. Copy those SQL statements into your SQL database management tool of choice, and execute.

For example, if we have the following table row (green heading), I can add the column “Location Lookup SQL” column which contains a formula to create SQL string by concatenating values from other columns.

LOCATION CATEGORY RATING FROM TO Location Lookup SQL
NWS A Moderate 2000 7999 =”SELECT ID FROM [LOCATION] WHERE [Name] = ‘”&A2&”‘”

 

If you find copying the “Location Lookup SQL” pastes the formula and not the resultant SQL value into your database manager, then a simple workaround is to copy the whole “Location Lookup SQL” column (all rows), then paste into another column in the spreadsheet using the “Paste Special” option and past only the values.

A sample is always good to illustrate so here’s an adaptation of an Excel file I just worked on: Sample – Generate Insert SQL From Excel

Or, if you prefer a quick look at it in pictures:

Sample - Generate Insert SQL From Excel (Screenshot 1)
Sample – Generate Insert SQL From Excel (Screenshot 1)
Sample - Generate Insert SQL From Excel (Screenshot 2)
Sample – Generate Insert SQL From Excel (Screenshot 2)

 

WordPress Themes & Plugins

Themes

‘X’ Theme or ‘Pro’ Theme
Themeco
https://theme.co/x/
US$55

Divi
Elegant Themes
https://www.elegantthemes.com/
US$89

General

Jetpack
https://jetpack.com/
US$39/99/299/year
– Themes
– Backups
– Monitoring

 

Canonical

Yoast SEO
Free/US$89/year

https://wordpress.org/plugins/tags/canonical/

 

Forms

Contact Form 7
https://contactform7.com/
Free
Comes with X Theme

Gravity Forms
http://www.gravityforms.com/
US$39

Ninja Forms
https://ninjaforms.com/
US$99 (Personal)

 

Security

WordFence
https://www.wordfence.com/
Free or US$99/year

 

Favicon

Built-in with WordPress

Favicon by RealFaviconGenerator
https://wordpress.org/plugins/favicon-by-realfavicongenerator/
Free

 

Schema, Rich Snippets & Social Sharing

  • Schema.org
  • Twitter Cards
  • Open Graph Protocol
  • Microformats

https://wordpress.org/plugins/schema/
https://wordpress.org/plugins/all-in-one-schemaorg-rich-snippets/

Social Sharing
http://www.wpbeginner.com/plugins/best-social-media-plugins-for-wordpress/
https://wordpress.org/plugins/ultimate-social-media-icons/
https://wordpress.org/plugins/ultimate-social-media-plus/

Google Maps

 

e-Commerce

http://www.wpbeginner.com/plugins/best-wordpress-ecommerce-plugins-compared/

WooCommerce
Free

 

Galleries, Sliders and Carousels

Envira Gallery (with X theme)
http://enviragallery.com/
UD$29 (Basic)

Soliloquy (with X theme)
http://soliloquywp.com/
UD$19 (Personal)

 

Spam and Comments

(TBC)

 

Backups, DR, & Monitoring

VaultPress (with JetPack)

BackupBuddy
https://ithemes.com/purchase/backupbuddy/
US$80/year

Uptime

 

Site Usage, Analytics, and Mapping

Sitemap
SEO plug-ins

Google XML Sitemaps
https://en-au.wordpress.org/plugins/google-sitemap-generator/

Google Sitemap by BestWebSoft
https://wordpress.org/plugins/google-sitemap-plugin/

Google Analytics
X Theme

MonsterInsights [for full dashboard]
https://wordpress.org/plugins/google-analytics-for-wordpress/
US$39/year

Robots.txt
(TBC)

Meta and keyword tags (per page)
Yoast SEO

 

SEO

Yoast SEO
https://en-au.wordpress.org/plugins/wordpress-seo/
Free or US$89/year

All in One SEO Pack
https://wordpress.org/plugins/all-in-one-seo-pack/
US$136/year

 

SSL

Migrate or Change website domain
https://wordpress.org/plugins/tags/migrate/
https://wordpress.org/plugins/duplicator/
https://wordpress.org/plugins/all-in-one-wp-migration/

 

Email and Contact Lists

MailChimp
https://mailchimp.com/

https://mc4wp.com/ (MailChimp plug-in)

 

Membership & Subscription

https://wordpress.org/plugins/wp-members/
Untested

 

Active Directory Integration