Do you have a video on how to extract tables from web site that you cant select with the check mark in excel? I want to do a dynamic query to pull in these tables.
+Craig Hatmaker Thanks Craig! I have MS XL 2010 and the PowerQuery add-on. I will try it. Let me know if you have any vids on PowerQuery as well. Thank you.
Hi Nadun, Thanks for the nice comment. This video is very old. MS query, while still working, has been replaced by Power Query (see th-cam.com/video/P9cUYpXIKsU/w-d-xo.html ) But to your question - I have not tried ROW_NUMBER in MS Query but I believe we can write any SQL statement that is supported by the ODBC driver on our PC. MS Query gives whatever we write in the SQL window to the driver and if the driver can't handle it, MS Query reports the error. But you question indicates you are tech saavy and so I'd recommend ADO which I have used with DB2's ROWNUM and ROW_NUMBER. See: sites.google.com/site/beyondexcel/project-updates/introtoado
@@CraigHatmakerBXL Thanks Mate. surely i will try the link you share. tbw power query (M Query) is prohibitively slow as compared to Microsoft Query SQL syntaxes. errr.
Can you please!, just give us a video on Executing Microsoft Excel Queries and No TH-camrs has given a single video on it. So please It's my Humble REQUEST and you have done a part of it . THAMKING you for this Video.
If all you want is the latest date then no need to sort. Use WHERE clause to select MyDateField=Max(MyDateField). See stackoverflow.com/questions/19432913/select-info-from-table-where-row-has-max-date
Great video! But i got stuck at one place where i can not extract "time" data as it is available in the source data. It is always in the wrong format. Could you help me out?
Hi A S, Dates and times can be tricky. That said, this is an old video. If you have a newer version of Excel, I highly recommend using Power Query instead. If you don't, the best way to handle bad source data is to either bring it in to Excel and fix it there (easier), or write an SQL query (harder) which MS Query will accept.
I am wondering if you can help me, because I can't find much information on Microsoft query. I get an error when including a specific column because a field contains non numeric data. I assume the datatype is being set as numeric as the first records retrieved just so happen to be numeric. If I were able to use SQL server, I could get around the issue by using SELECT convert(varchar, ponumber) as ponumber ...but that syntax doesn't work. Is there a command in SQL syntax I can use to define the returned datatype as non-numeric to avoid getting this type of error?
If I understand the problem, SQL provides CAST and CONVERT (depending on the data source) functions to address this. See www.smallsql.de/doc/sql-functions/system/convert.html
hi, great video. can you teach me how to automatically get data from a specific appplication or program (which can save as excel data) & then use the data on my excel file that i use everyday. Thanks in advance.. your such a great help!
j935989 You can use this video's technique if you can identify the database that your application uses. If the app is a "web app" it is possible to control the application from Excel - but that is a completely different discussion.
j935989 VBA - If you are familiar with VBA but not familiar with using it with web apps, Google "VBA Browser Object" and see www.w3schools.com/jsref/dom_obj_document.asp. Also check out the BXL website in about a week. It will have an XL workbook that scrapes Employee's who are listed on LinkedIn and work for a specific company. It will provide an example of using the Browser Object and reading a web page. The BXL Website is sites.google.com/site/beyondexcel/home
If the question is "Can you bring data into Excel using an hand generated SQL w/o MS Query?" The answer is - Yes! Some examples can be seen at my blog: sites.google.com/site/beyondexcel/ Check out the recent post titled: "Using ADO with ListObject's QueryTable"
Hi - this was great. Question: How do I combine two fields into one parameter. Basic example: I want the user to input Blue and Green for a parameter in the excel sheet. . In my query I have Color is one of [], which is returning all colors. I know I can filter in here but I want the user to enter a filter on the excel sheet. I cant seem to be able to define a parameter that grabs both fields in one. So when I define my parameter 1 and point to the get value cell this is where I don't know how to combine the two color fields blue and green.
+Sandy A You are on the right track! In MS Query set the filter to: Color is one of [Color1], [Color2] Then set up one cell as the input for [Color1] and another for [Color2]
Slai20's answer is a good one if you want the entire used range of a worksheet. If you want a section of the worksheet name the range in Excel and use the name instead of [Sheet1$]
This looks a lot easier that I thought. Thanks for this.
Thanks for sharing this video. Thumbs up...
Do you have a video on how to extract tables from web site that you cant select with the check mark in excel? I want to do a dynamic query to pull in these tables.
This is the closest I have to that: th-cam.com/video/mxZBVMfcUKc/w-d-xo.html Hope that helps.
thank you sir
Thank you. Very easy to follow....and straight to the point!
+donaldcc Thanks. As you can see, this video is a bit old. If you have XL 2013, try PowerQuery. Its free. MS has positioned it to replace MS Query.
+Craig Hatmaker Thanks Craig! I have MS XL 2010 and the PowerQuery add-on. I will try it. Let me know if you have any vids on PowerQuery as well. Thank you.
Good Video. What are the SQL functions we can use in Microsoft Query. I was trying to use ROW_NUMBER and its not working.
Hi Nadun, Thanks for the nice comment. This video is very old. MS query, while still working, has been replaced by Power Query (see th-cam.com/video/P9cUYpXIKsU/w-d-xo.html ) But to your question - I have not tried ROW_NUMBER in MS Query but I believe we can write any SQL statement that is supported by the ODBC driver on our PC. MS Query gives whatever we write in the SQL window to the driver and if the driver can't handle it, MS Query reports the error. But you question indicates you are tech saavy and so I'd recommend ADO which I have used with DB2's ROWNUM and ROW_NUMBER. See: sites.google.com/site/beyondexcel/project-updates/introtoado
@@CraigHatmakerBXL Thanks Mate. surely i will try the link you share. tbw power query (M Query) is prohibitively slow as compared to Microsoft Query SQL syntaxes. errr.
@@NDJAYA2000 Yes. M can be slow. For that reason I only use SQL & ADO for production work.
Can you please!, just give us a video on Executing Microsoft Excel Queries and No TH-camrs has given a single video on it. So please It's my Humble REQUEST and you have done a part of it . THAMKING you for this Video.
If I sort my data by date, Is there a way to pull only the latest date from the db?
If all you want is the latest date then no need to sort. Use WHERE clause to select MyDateField=Max(MyDateField). See stackoverflow.com/questions/19432913/select-info-from-table-where-row-has-max-date
Great video! But i got stuck at one place where i can not extract "time" data as it is available in the source data. It is always in the wrong format. Could you help me out?
Hi A S, Dates and times can be tricky. That said, this is an old video. If you have a newer version of Excel, I highly recommend using Power Query instead. If you don't, the best way to handle bad source data is to either bring it in to Excel and fix it there (easier), or write an SQL query (harder) which MS Query will accept.
I am wondering if you can help me, because I can't find much information on Microsoft query. I get an error when including a specific column because a field contains non numeric data. I assume the datatype is being set as numeric as the first records retrieved just so happen to be numeric.
If I were able to use SQL server, I could get around the issue by using
SELECT convert(varchar, ponumber) as ponumber
...but that syntax doesn't work. Is there a command in SQL syntax I can use to define the returned datatype as non-numeric to avoid getting this type of error?
If I understand the problem, SQL provides CAST and CONVERT (depending on the data source) functions to address this. See www.smallsql.de/doc/sql-functions/system/convert.html
hi, great video. can you teach me how to automatically get data from a specific appplication or program (which can save as excel data) & then use the data on my excel file that i use everyday. Thanks in advance.. your such a great help!
j935989 You can use this video's technique if you can identify the database that your application uses. If the app is a "web app" it is possible to control the application from Excel - but that is a completely different discussion.
Thanks! I did try your recommendation & was able to do your steps. I'm more into the web apps that we use as a source of our data. How do you do that?
j935989 VBA - If you are familiar with VBA but not familiar with using it with web apps, Google "VBA Browser Object" and see www.w3schools.com/jsref/dom_obj_document.asp. Also check out the BXL website in about a week. It will have an XL workbook that scrapes Employee's who are listed on LinkedIn and work for a specific company. It will provide an example of using the Browser Object and reading a web page. The BXL Website is sites.google.com/site/beyondexcel/home
If the question is "Can you bring data into Excel using an hand generated SQL w/o MS Query?" The answer is - Yes!
Some examples can be seen at my blog: sites.google.com/site/beyondexcel/
Check out the recent post titled: "Using ADO with ListObject's QueryTable"
Hi - this was great. Question: How do I combine two fields into one parameter. Basic example: I want the user to input Blue and Green for a parameter in the excel sheet. . In my query I have Color is one of [], which is returning all colors. I know I can filter in here but I want the user to enter a filter on the excel sheet. I cant seem to be able to define a parameter that grabs both fields in one. So when I define my parameter 1 and point to the get value cell this is where I don't know how to combine the two color fields blue and green.
+Sandy A You are on the right track!
In MS Query set the filter to: Color is one of [Color1], [Color2]
Then set up one cell as the input for [Color1] and another for [Color2]
Amazing, thank you
I need to go the other way, I have a database in excel and I need to do sql queries from it in access
SELECT * FROM [Sheet1$] IN 'C:\Book1.xlsx'[Excel 12.0;]
Slai20's answer is a good one if you want the entire used range of a worksheet. If you want a section of the worksheet name the range in Excel and use the name instead of [Sheet1$]
Just watched this in 2023 for the memories
A lot has happened in 12 years😃
How could I learn this fast .
This is very old. I highly recommend learning Power Query (PQ) instead. There are many PQ TH-cam videos.
@@CraigHatmakerBXL thnqqq sir
holllllly shittttttttttt hackers and tim and eric awesome show