+ Reply to Thread
Results 1 to 4 of 4

SQL query from excel files

  1. #1
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    SQL query from excel files

    Hi,
    I would like to share my file I’m working with. Basically it makes SQL requests to other excel file and return information I need.
    This could be usefull for all who works with heavy excel files (50+ Mb) and have needs to get some data from this large files without opening it. Also this file have interesting functionality – to make similar requests to different files (in case you have many files with identical structure).
    *This definitely is not ideal file for serious use and code could be much more elegant, but my purpose is to share main idea of queries from excel files

    On the top of sheet SQL – small menu
    Name:  menu.jpg
Views: 521
Size:  46.5 KB
    1 – Button to choose the source File, data will be returned from File you’ll choose
    2 – After File will be chosen this ComboBox will contain names of all sheets in the File
    3 – After name of sheet will be chosen you will be able to choose witch columns from File you wish to load (names of columns are loaded automatically after Sheet name is chosen)
    4, 5, 6 are optional, but if some criteria is needed – all cells in particular row has to be filled
    4 – columns names (same as 3)
    5 – pre-defined operators for criteria
    6 – value of criteria
    7 – if you need to make same requests from many files – Unckeck and just choose different file – all prev build Selection and criteria data will remain. In case this is Checked – after new file will be chosen – all Selection and Criteria info will be cleaned and you'll need to build Selection and Criteria again
    8 – Button to Run query and get result

    I’m attaching small dummy database file to explain how it works.
    Example 1.
    Name:  ex1.jpg
Views: 566
Size:  55.7 KB
    After I’ve chosen File – path to this file will appear to the right from button. I need to det data from sheet Data and from that sheet I need Columns „OrderDate”, „Region”, „Item”, „Units”. You can choose columns in any order you wish them to arrear below.
    If I need just to get all data – I ‘ll press button 8 and get all records.

    Example 2.
    Name:  ex2.jpg
Views: 498
Size:  57.2 KB
    Same file, but sheet Customers. Columns under Select. Now I need to get records where values in Column „Region” is equal to „east”. If criteria is as string (not number) – you don’t need to use quaters or other symbols, just type it like on the picture.
    Press button 8 and get result.

    Example 3.
    Name:  ex3.jpg
Views: 576
Size:  57.9 KB
    Sheets Data, Columns under Select. Now I need return data by two criterias: Rep and Units. As you can see you can choose criterias even if you don’t need to return data from this column (Rep=jones).
    All multiply criterias working with AND between them, thats mean query will return data that meets two criteria at the same time.
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: SQL query from excel files

    Will you always be getting information from the same Excel file, or do you want your users to select a file at random? Will the table structures be the same all the time, or will we have to "parse" them?
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: SQL query from excel files

    @ dflak
    Hi,
    I'm not sure that I clearly understood your questions.. Did you try to run attached file? I've shared this assuming it is working version
    Users can make different selections from the same excel file or make the same selections from different files by choosing file in Dialog Window (lets say we have 12 files for each month, structure of all files are the same).

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: SQL query from excel files

    Ah, I got it now. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Using Excel Query and shared files
    By Lynneth in forum Excel General
    Replies: 0
    Last Post: 02-23-2016, 11:28 AM
  2. Can't create Mac query files for Excel for Mac 2011
    By veronicahope in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 08-05-2013, 02:55 AM
  3. Vlookup query : How to pull data from raw excel files into a master excel file?
    By jamesjamesiata in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-22-2013, 06:26 AM
  4. Excel query files and buttons
    By mbroxholme in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-19-2013, 12:48 PM
  5. Cannot get Microsoft Query to work on Excel Files
    By djblois1 in forum Excel General
    Replies: 1
    Last Post: 12-20-2011, 12:51 PM
  6. ATTN: Works wdb files to Excel 2003 converter query
    By SeeAll in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 07-10-2006, 03:34 AM
  7. query multiple excel files
    By pollymoe in forum Excel General
    Replies: 1
    Last Post: 02-01-2006, 10:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1