How to import external data from web into database via C#.

[zeeshanmughal]

I am creating a software for my company in C#. The working process is like this.
1: we write information in an Excel file.
2: Then we go to the website (Company's customer support page) there is only one search box. and we search after appropriate information. it gives us result with all information. we need only information from the "summary column" and we write it down in the Excel file.

what i am trying to do is creating a tool that will loop through all the given information, search it and get results and get back and save into the excel file.

but problem is that i don't know where do i start. i have googled it alot but couldn't find any thing on that topic.

Thanks in advance

[thines01]

Which part is confusing?
a) Getting the data from the website
b) Writing data in an excel file?

[zeeshanmughal]

Which part is confusing?
a) Getting the data from the website
b) Writing data in an excel file?

both of them. i don't know how to get data from website with one click. some logical explanation and little bit hinting code could be appreciate.

Thanks in advance.

[thines01]

What you're trying to do requires a tremendous amount of explanation -- especially if you're coding it.
You will need to be familiar with adding COM references to your code and then using them.

Here is a code snippet that goes out to Bing.com and queries on the word "WebClient" and returns all links found in the results and stores them in a spreadsheet. I wasn't too discriminatory with the links, so some of them are not relevant to the search.

The code uses COM automation to write the data into a spreadsheet -- which means in order for you to be proficient at this technique, you will need to know how Excel references its own internal strucure (WorkBook, WorkSheet, Range, Cell, Value2, etc).

For the web piece: The code opens a web page as a stream and reads in the HTML with all whitespace removed (makes it easier to find things). Then it splits the input by double-quotes and searches for http links.

With that said, I expect you to take what you don't understand and web search the individual piece rather than trying to take the code as a whole and find it on the web.

The result goes in the users temp directory.
I made this a console app, so I could deliver a working solution.
Other than adding the COM reference (and having Excel installed), no external pieces are necessary. Dot Net 3.5 or better required.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text.RegularExpressions;
using Microsoft.Office.Interop.Excel;

namespace DW_414673_CS_CON
{
   class Program
   {
      private static List<string> GetResultsFromBing(ref string strError)
      {
         List<string> lst_strRetVal = new List<string>();
         WebClient wc = new WebClient();
         try
         {
            string strData = "";
            List<string> lst_strData = new List<string>();
            
            StreamReader fileWebIn = 
               new StreamReader(wc.OpenRead("http://www.bing.com/search?q=webclient&qs=n&form=QBLH&pq=webclient&sc=8-9&sp=-1&sk="));

            if (!fileWebIn.EndOfStream)
            {
               // Remove all whitespace
               strData = Regex.Replace(fileWebIn.ReadToEnd(), @"\s", "");

               // Split and stack the entries
               lst_strData = strData.Substring(strData.IndexOf("All Results") + 11)
                  .Split("\"".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList();
            }
            fileWebIn.Close();

            Regex rxUrl = new Regex("(?<url>http://.*)");

            // Put all matches in the output list
            lst_strData.Where(s => rxUrl.IsMatch(s)).ToList().ForEach(s =>
                  lst_strRetVal.Add(rxUrl.Match(s).Groups["url"].Value));
         }
         catch (Exception exc)
         {
            strError = exc.Message;
         }

         return lst_strRetVal;
      }

      private static bool WriteResultsToXls(string strOutXlsFileName, List<string> lst_strUrls, ref string strError)
      {
         bool blnRetVal = true;
         Application excel = new Application();
         try
         {
            Workbook wb = excel.Workbooks.Add();
            wb.Worksheets.Add();
            
            ////////////////////////////////////////////////////////////////////
            // Write each element of the list to column 1 of the spreadsheet.
            long lngRow = 0;
            lst_strUrls.ForEach(s => ((Worksheet)wb.Worksheets[1]).Cells[++lngRow, 1] = s);

            wb.SaveAs(strOutXlsFileName, XlFileFormat.xlExcel8,
               Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, XlSaveAsAccessMode.xlNoChange,
               XlSaveConflictResolution.xlLocalSessionChanges,
               Type.Missing , Type.Missing, Type.Missing, Type.Missing);

            wb.Close(XlSaveAction.xlSaveChanges);
         }
         catch (Exception exc)
         {
            blnRetVal = false;
            strError = exc.Message;
         }
         finally
         {
            excel.Quit();
         }

         return blnRetVal;
      }

      static void Main(string[] args)
      {
         string strError="";
         List<string> lst_strUrls = GetResultsFromBing(ref strError);
         
         if (lst_strUrls.Count.Equals(0))
         {
            Console.WriteLine(strError);
            return;
         }

         //lst_strUrls.ForEach(s => Console.WriteLine(s));

         string strOutFile = Path.Combine(Path.GetTempPath(), "TestWebToSheet.xls");
         if (!WriteResultsToXls(strOutFile, lst_strUrls, ref strError))
         {
            Console.WriteLine("Could not write to Excel: " + strError);
            return;
         }
      }
   }
}

[thines01]

I realize that first sentence didn't come out the way I wanted it to. :O

It should have read "...especially if you're coding it"
...meaning rather than using somebody's pre-packaged web extractor. :)

[zeeshanmughal]

Thanks for quick response. that was very helpful example. i will upload my code here while i work on my project.
thanks again.

[doaa.foudah]

you insert the data you search for using the string quirey in URL, but if this not an option, how to write the data to the text box and press the button to search.