none
Utility for extracting data from db RRS feed

  • Question

  • Hi ,

    I have 2 queries with me

    Query1:



    "select A.FromName as AwardGiverName,B.LName as GiverLastName,B.FName as GiverFirstName,B.EMAIL as GiversMailid,A.ToName as AwardReceiverNName,C.LName as Receiverlastname,C.FName as ReceiverFirstName,C.EMAIL as ReceiversMailid,A.REASON,A.Date,A.REASONSSTRING,B.GRADE
    from Information as A
    Left join Contact as B
    on A.FromName=B.Name
    Left Join Contact as C
    on A.ToName=C.Name
    where Year(Date)=2018 "        and

    Query 2:


    select A.FromName1 as AwardGiverName,B.LName as GiverLastName,B.FName as GiverFirstName,B.EMAIL as GiversMailid,A.ToName1 as AwardReceiverNName,C.LName as Receiverlastname,C.FName as ReceiverFirstName,C.EMAIL as ReceiversMailid,A.REASON1,A.Date1,A.REASONSSTRING1,B.GRADE
    from Information as A
    Left join Contact as B
    on A.FromName1=B.Name
    Left Join Contact as C
    on A.ToName1=C.Name
    where Year(Date1)=2018 

        Here i want to create utility where if Type A selected Query 1 should get fire and data should get created in csv in C location and if  Type B is selected Query 2 should get fire and data should get created in csv in c location. 

    Could you please help with this.

    Thanks,

    Shweta


    Monday, March 26, 2018 2:38 AM

All replies

  • Use ADO.NET to execute the query and get the results. Then generate the CSV file. There are plenty of examples online on how to do this.

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-code-examples

    https://stackoverflow.com/questions/18757097/writing-data-into-csv-file-in-c-sharp

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/07646598-b15c-43b6-99c9-25609086843e/write-data-into-csv-file-c?forum=csharpgeneral

    http://www.dotnetlearners.com/blogs/view/237/C--create-csv-file-and-export-datatable-to-csv.aspx


    Michael Taylor http://www.michaeltaylorp3.net

    Monday, March 26, 2018 2:04 PM
  • Hi,

    Could anyone please help with whole code with these queries .

    Waiting for response!!

    Thanks,

    Shweta


    Shweta

    Monday, March 26, 2018 3:21 PM
  • Does it have to be done using C#?


    Access 2007:

    In the first figure (1.1) you can see I have opened up the standard example database that comes with MS Access (called Northwind).  I have also selected the query called “Top Ten Orders by Sales Amount”.  To this query, I have added customer address information since SpatialKey loves data that contains temporal (time and date) and geospatial (data that can be translated to a location) data.

    Fig. 1.1 – MS Access 2007

    From here I can easily export the results to a CSV file:

    1. Click on the table or query to export from (in this example “Top Ten Orders by Sales Amount” on the left)
    2. Click the “External Data” tab at the top of the window
    3. In the “Export” section click “Text File” and a wizard will appear
    4. Choose a location for the exported CSV and name your file (make sure the file ends with a .csv extension)
    5. Click OK
    6. On the next screen be sure the “Delimited” option is selected
    7. Click the “Advanced…” button in the lower left of the window
    8. SpatialKey stores its data as Unicode UTF-8, we need to ensure that the data is exported in this format
      1. Click the drop-down box next to Code-Page
      2. Choose Unicode (UTF-8) in the options list
      3. Click OK
    9. Back at the Export Text window click “Next”
    10. Be sure “Comma” is selected as the delimiter and Text Qualifier is a double quote: “
    11. Click the checkbox “Include Field Names on First Row” (should be selected)
    12. Click “Next”
    13. Verify the file name and location and click “Finish”
    14. The final screen of the wizard gives you the option to save the steps allowing for easy re-exporting of the data in the future. If you anticipate needing to update the data in SpatialKey go ahead and check the checkbox to save some time in the future. Close the window when finished.

    If you open the file in a text editor, you should see something like this:

    Fig. 1.2 – Exported Northwind Data

    Now you are ready to upload the new CSV File to SpatialKey.

    MS Access 2003:

    The next figure shows the older MS Access 2003 version of the Northwind database. I will be exporting the saved query called “Orders Qry” on the left.

    Fig. 1.3 – MS Access 2003 Northwind Database

    Lets export the CSV:

    1. Be sure the table or query is selected and click the File menu and choose “Export…”
    2. The export wizard will begin
    3. Select the location for the exported data and name your file (be sure to set the file extension as .csv)
    4. Change the Save as type: to “Text Files”
    5. Click “Export”


    Using C# for the task may be overkill, but it would be a good learning experience nonetheless. 

    private static void ExportQuery(string databaseLocation, string queryNameToExport, string locationToExportTo)
    {
        var application = new Application();
        application.OpenCurrentDatabase(databaseLocation);
        application.DoCmd.TransferSpreadsheet(AcDataTransferType.acExport, AcSpreadSheetType.acSpreadsheetTypeExcel12,
                                              queryNameToExport, locationToExportTo, true);
        application.CloseCurrentDatabase();
        application.Quit();
        Marshal.ReleaseComObject(application);
    }
    
    You would call it like this:
    
    ExportQuery(@"C:\blah\blah.accdb", "myQuery", @"C:\blah\blah.xlsx");
    
    Be sure to add these using statements:
    
    using System.Runtime.InteropServices;
    using Microsoft.Office.Interop.Access;



    MY BOOK

    Tuesday, March 27, 2018 12:09 AM
  • Hi,

    Thanks for this 

    But i want utility in ASP.net. Could anyone please help with this

    Thanks,

    Shweta


    Shweta

    Tuesday, March 27, 2018 4:45 AM
  • Hi,

    Thanks for this 

    But i want utility in ASP.net. Could anyone please help with this

    Thanks,

    Shweta


    Shweta

    Hello,

    Since this is for ASP.NET I would recommend using the ASP.NET forums to ask this question.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, March 27, 2018 9:11 AM