Solved

EXCEL MYSQL  ODBC Select and display records

Posted on 2014-01-02
7
613 Views
Last Modified: 2014-01-03
Say,
 
I have an ODBC link in place.
How can I select top 10 records from a table and display in an excel Sheet?

Can I use a call statement to an existing query in Mysql which selects the top 10 records, else another method is fine?
tx
0
Comment
Question by:shaunwingin
  • 5
  • 2
7 Comments
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39753408
You use the LIMIT keyword in your query

SELECT * FROM tbl LIMIT 0,9;
0
 

Author Comment

by:shaunwingin
ID: 39753433
Will this select the oldest or newest records? Need newest.
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39753443
Then add the ORDER BY

SELECT * FROM tbl ORDER BY some_column LIMIT 0,9;
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39753444
If you were to sort the table manually, just by looking at it, what column would you sort on and would you sort it ascending or descending?
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39753460
Regarding your other question about calling a query and displaying in excel.  There are two methods that come to mind but would depend on how your users would use the spreadsheet and how the data is displayed.

Method 1: Re-use an existing query (good for large data sets and pivot tables)
This is a bit long winded, but good for when the layout of the query you're returning doesn't change.  The idea is you create a dummy query to your database and then as you want to show new data you change the sql for that query and tell it to refresh.

Method 2: Manually parse each record (good for straight tables)
This has its downfalls, especially when you may be pivoting data but gives you flexibility on where the data is displayed.  eg The query returns a row of data at a time so essentially you could put that data anywhere in the spreadsheet you want.  You are not tied to a table / pivot layout that you would in Method 1.

Basically I would more or less use Method 1 but there are occasions when Method 2 is ok, usually with small datasets ie few records
0
 

Author Comment

by:shaunwingin
ID: 39753489
Method 2 would work.
assume a simple table with fields
date
time
shiftno

how can I select top 10 records and display in excel sheet result cel A1 downwards?
Would use call query to select the data.
0
 
LVL 42

Accepted Solution

by:
Rob Jurd, EE MVE earned 500 total points
ID: 39753509
So taking the code from your previous question:  http:Q_28327033.html#a39750882

Public Sub myodbc_ado_Click(param1 as String, param2 as String)
	Dim conn As ADODB.Connection
	Dim rs As ADODB.Recordset
	Dim fld As ADODB.Field
	Dim sql As String
	
	' Server settings for database
	Dim myserver as String
	Dim mydb as String
	Dim user as String
	Dim pwd as String
	
	' Parameters
	Dim param1 as String
	Dim param2 as String
	
	'connect to MySQL server using Connector/ODBC
	Set conn = New ADODB.Connection
	
	myserver = "localhost"
	mydb = "test"
	user = "venu"
	pwd = "venu"
	
	' get the parameters from the sheet
	Sheet1.Activate
	
	param1 = ActiveSheet.Range("A1").Value
	param2 = ActiveSheet.Range("B1").Value

	conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};"_
	& "SERVER="&myserver&";"_
	& " DATABASE="&mydb&";"_
	& "UID="&user&";PWD="&pwd&"; OPTION=3"
	conn.Open

	sql = "SELECT date, time, shiftno FROM mytable WHERE param1='"+param1+"' AND param2='"+param2+"' ORDER BY date DESC LIMIT 0,9;"
	
	Set rs = conn.Execute(sql)
	
	' output to sheet named "Report"
	Dim i as Long
	i = 2 ' start at row 2 as the headers will be on row 1

	Sheets("Report").Activate
	
	ActiveSheet.Cells(1,1).Value = "Date"
	ActiveSheet.Cells(1,2).Value = "Time"
	ActiveSheet.Cells(1,3).Value = "Shift No."
	
	Do While Not rs.EOF
		With ActiveSheet
			.Cells(i, 1).Value = rs.Fields(0).Value
			.Cells(i, 2).Value = rs.Fields(1).Value
			.Cells(i, 3).Value = rs.Fields(2).Value
		End With
	  	i = i + 1 ' move to the next row
	Loop
	rs.Close

	conn.Close

End Sub

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Freeze Panes Solution 6 28
ActiveX Listbox Multi Select in Excel 2010 8 16
PHP: concatenate query 12 33
Add a range in an Excel graph 5 31
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now