Computer Programming web Web programming Tips



How to display data in Excel spreadsheet from MySQL. Part 1

By Sergey Skudaev

Happy Amazon Prime Day July 12 - Exclusive Deals for Prime Members - Start Free 30-Day Trial

Here you find an example of using Visual Basic for Application (VBA) to  connect Excell to MySQL. Also you will learn how to create table in MySQL and how to write SQL query.

First you must to install MySQL ODBC driver in PC where MS Excel is installed. It may be the same PC where your web server is running or PC connected to Web server machine. Follow the link to download MySQL ODBC driver:






Use http:// dev.mysql.com/ downloads/connector /odbc/3.51.html link.


Use http:// dev.mysql.com/ doc/mysql/en/ odbc-connector.html link to find instruction for Installation

Create in MySQL a table from Data Model and Database free tutorial.


create table credit_card(

cardid INT NOT NULL AUTO_INCREMENT,

name varchar(20),

type varchar(20),

expired date,

card_num INT,

credit float,

phone varchar(12),

address varchar(100),

city varchar(20),

state varchar(2),

zip varchar(10),

PRIMARY KEY(cardid)

);

Read One hour MySQL tutorial to learn how to use command prompt to start MySQL server and client, and create database and table

Open command prompt and go to MySQL user interface.
Type: Create database credit; and Press enter. Database is created. Hover mouse over thumbnail to see large picture

Excel MySQL: create database

Use this picture as example.

To select database, type use credit; Database changes.

Excel MySQL: Edit

Create credit_card table in credit database. Copy script for credit_card table, paste it in notepad and save file as card.sql in C:\mysql\bin directory. Then type source card.sql and press enter. See picture below. Table created.

Excel MySQL: sql language select query





Fill the credit_card table with data.

Open notepad and paste in it the insert query for three records:

Insert into credit_card (cardid, name, type, expired, card_num, credit, phone, address, city, state, zip)

values (0, 'CHASE', 'Visa', '2006-01-01', 123456789, 20000.00, '123-123-1234',

'12 West 13th Street', 'New York', 'NY', '12345-4321');

Insert into credit_card (cardid, name,type, expired, card_num, credit, phone, address, city, state, zip)

values (0, 'Bank One', 'MasterCard', '2006-01-07', 987654321, 10000.00, '123-123-9876',

'16 West 17th Street', 'New York', 'NY', '12345-4321');

Insert into credit_card (cardid, name, type, expired, card_num, credit, phone, address, city, state, zip)

values (0, 'Chase Platinum', 'MasterCard', '2006-09-07', 999996789, 15000.00, '123-876-9876',

'19 West 19th Street', 'New York', 'NY', '12999-4321');

Save file in MySQL\bin directory as insert.sql.

To insert records, type source insert.sql as in example below

Excel MySQL: Insert query

Record is inserted in db. To continue go to the next page: >>Part 2>>

Subscribe to our mail list to receive free code examples.

Comments

No comments yet.

Add Comment

* Required information
(never displayed)
 
Smile Sad Huh Laugh Mad Tongue Crying Grin Wink Scared Cool Sleep Blush Unsure Shocked
 
1000
Enter the word table backwards.
 
Enter answer:
Captcha
Refresh
 
Enter code:
 
Notify me of new comments via email.
 
Remember my form inputs on this computer.
 
I have read and understand the privacy policy. *
 
I have read and agree to the terms and conditions. *
 
 
Powered by Commentics

My eBooks on Amazon.com

Learn C++ Programming By Examples

Learn SQL Programming By Examples

Learn PHP Programming by Examples

Visual Basic Programming By Examples

How to Build Your Own Web Site from Scratch