Click here to Skip to main content
Click here to Skip to main content
Go to top

Reading Online CSV File and Manage it into Microsoft SQL Database

, 7 Aug 2012
Rate this:
Please Sign up or sign in to vote.
How to read an online Excel file and manage it in Microsoft SQL database

Introduction

For software developers who have been working for long days, there might be a need to read online files from desktop or web applications and then store the data in local system or database. It is a little complex if you have no experience in it. There are several ways in which you can do it. In this demonstration, I will explain it from my experience to handle this type of situation.

Background

I have an Excel file online, the URL is http://velocitypath.com/odesk/buildings.csv. I need to read this file and manage it in Microsoft SQL Server database. The structure of the Excel file is given below:

Figure 1

Let’s Get Started

To store data from Excel file to database, it is required to create a table structure in Microsoft SQL Server database.

  • Open Microsoft SQL Server Management Studio
  • Create a table structure like below:

Figure 2

Open Microsoft Visual Studio 2010

  • Create a New Project (File>New>Project>Visual C#>Windows Form Application)
  • Name it as ReadingOnlineExcelFile

Figure 3

By default, the new project includes a form called ReadExcel.cs. Design the form like below:

Figure 4

To read the online file, you need to send web request. After processing successful web request, the web server will return a web response. To accomplish this, you need to use HttpWebRequest, HttpWebResponse classes. And WebRequest class is used to create a web request. All these classes can be found in System.Net namespace.

using System.Net;

Now, create a method for handling web request and to get counter response. Our intention is to get StreamReader object as a return value of this method. So you need to include:

using System.IO;
#region Method

private StreamReader GetStream()
        {
            String strURL = String.Empty;
            strURL = "http://velocitypath.com/odesk/buildings.csv";

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(strURL);

            StreamReader reader;

            using (HttpWebResponse response =(HttpWebResponse)request.GetResponse())
            {
                reader = new StreamReader(response.GetResponseStream());
            }

            return reader;
        }

#endregion Method

In the above method, WebRequest.Create() is used to create web request, to get response from web request GetResponse(), to read stream from web response, GetResponseStream() is used.

Add System.Configuration reference:

Figure 5

Add the following namespace for data and SQL Operation.

using System.Data;
using System.Configuration;
using System.Data.SqlClient;

Add the following code in button click event:

private void btnReadExcel_Click(object sender, EventArgs e)
        {
            using (SqlConnection conn = new SqlConnection
            (ConfigurationManager.ConnectionStrings["SQLConnection"].ToString()))
            {
                conn.Open();

                StreamReader streamReader;

                streamReader = GetStream();

                streamReader.ReadLine();
                while (!streamReader.EndOfStream)
                {
                    String []row = streamReader.ReadLine().ToString().Split(',');

                    SqlCommand selectCommand;
                    selectCommand = new SqlCommand("SELECT * FROM Location
                    WHERE Latitude=@Latitude AND Longitude=@Longitude", conn);

                    selectCommand.Parameters.Add("@Latitude",
                    SqlDbType.Decimal, 20).Value = Convert.ToDecimal(row[0]);
                    selectCommand.Parameters.Add("@Longitude",
                    SqlDbType.Decimal, 20).Value = Convert.ToDecimal(row[1]);

                    SqlDataReader reader = selectCommand.ExecuteReader();

                    if (!reader.HasRows)
                    {
                        reader.Close();

                        SqlCommand insertCommand;
                        insertCommand = new SqlCommand("INSERT INTO Location
                        (Latitude,Longitude,BuildingName)
                        VALUES(@Latitude,@Longitude,@BuildingName)", conn);

                        insertCommand.Parameters.Add("@Latitude",
                        SqlDbType.Decimal, 20).Value = Convert.ToDecimal(row[0]);
                        insertCommand.Parameters.Add("@Longitude",
                        SqlDbType.Decimal, 20).Value = Convert.ToDecimal(row[1]);
                        insertCommand.Parameters.Add("@BuildingName",
                        SqlDbType.VarChar, 100).Value = row[2].ToString();

                        int intResult;
                        intResult = insertCommand.ExecuteNonQuery();
                    }
                }
            }
        }

In the above code, the following GetStream() returns Stream from web request.

  streamReader = GetStream();
  streamReader.ReadLine();

The above line of code reads line from stream.

Figure 6

Add line from stream into array for easy manipulation.

String []row = streamReader.ReadLine().ToString().Split(',');

Figure 7

Now check whether data already exists in the database.

SqlCommand selectCommand;
selectCommand = new SqlCommand
     ("SELECT * FROM Location WHERE Latitude=@Latitude AND Longitude=@Longitude", conn);

     selectCommand.Parameters.Add("@Latitude",
     SqlDbType.Decimal, 20).Value = Convert.ToDecimal(row[0]);
     selectCommand.Parameters.Add("@Longitude",
     SqlDbType.Decimal, 20).Value = Convert.ToDecimal(row[1]);

     SqlDataReader reader = selectCommand.ExecuteReader();

     if (!reader.HasRows)
     {
     }

If not, then insert data into database.

reader.Close();

SqlCommand insertCommand;
insertCommand = new SqlCommand("INSERT INTO Location
                (Latitude,Longitude,BuildingName) VALUES
                (@Latitude,@Longitude,@BuildingName)", conn);

insertCommand.Parameters.Add("@Latitude",
SqlDbType.Decimal, 20).Value = Convert.ToDecimal(row[0]);
insertCommand.Parameters.Add("@Longitude",
SqlDbType.Decimal, 20).Value = Convert.ToDecimal(row[1]);
insertCommand.Parameters.Add("@BuildingName",
SqlDbType.VarChar, 100).Value = row[2].ToString();

int intResult;
intResult = insertCommand.ExecuteNonQuery();

Here is the result from the database:

Figure 8

Conclusion

By reading this article, you can create your online file reading application. I think this may help you in software development and professional work.

History

  • 28th March, 2011: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Abdul Quader Mamun
Team Leader
Bangladesh Bangladesh
I have been developing software/web applications since 2002 mainly on Microsoft technologies.
 
I'm also experienced with other technologies.
 
I'm fan of well organized and effective coding.
 
A wide range of technologies, tools, languages, frameworks, components and design patterns I’m experienced with.
 
I’m a MCP, MCAD.NET, MCSD.NET.
Follow on   Google+

Comments and Discussions

 
QuestionMy vote of 1 Pinmembertvbic7-Nov-13 19:26 
SuggestionOnline Excel PinmemberIgor Petrushenko3-Nov-12 22:24 
GeneralOk as an excercise but... Pinmemberdave.dolan8-Aug-12 6:30 
GeneralRe: Ok as an excercise but... PinmemberAbdul Quader Mamun8-Aug-12 16:13 
GeneralMy vote of 3 Pinmembercjb1107-Aug-12 21:08 
Questioncan i read xlsx from your code PinmemberMember 894239223-Jul-12 4:08 
GeneralMy vote of 1 PinmemberWooln30-Aug-11 20:57 
GeneralOther solution PinmemberCikaPero10-Apr-11 22:24 
General5 Star Article PinmemberCode Hungery (Rashed Nadeem)5-Apr-11 0:46 
General[My vote of 1] Misleading article title PinmemberPaulLinton4-Apr-11 12:25 
GeneralMy vote of 5 PinmemberSureshDeyna29-Mar-11 16:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140905.1 | Last Updated 8 Aug 2012
Article Copyright 2011 by Abdul Quader Mamun
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid