Click here to Skip to main content
11,578,524 members (59,453 online)
Click here to Skip to main content

Reading Online CSV File and Manage it into Microsoft SQL Database

, 7 Aug 2012 CPOL 24.8K 692 33
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.

You may also be interested in...

Comments and Discussions

 
Questionwhy I'm getting error at readline() Pin
n shiva Ram31-Oct-14 1:31
membern shiva Ram31-Oct-14 1:31 
QuestionMy vote of 1 Pin
tvbic7-Nov-13 19:26
membertvbic7-Nov-13 19:26 
SuggestionOnline Excel Pin
Igor Petrushenko3-Nov-12 22:24
memberIgor Petrushenko3-Nov-12 22:24 
GeneralOk as an excercise but... Pin
dave.dolan8-Aug-12 6:30
memberdave.dolan8-Aug-12 6:30 
GeneralRe: Ok as an excercise but... Pin
Abdul Quader Mamun8-Aug-12 16:13
memberAbdul Quader Mamun8-Aug-12 16:13 
GeneralMy vote of 3 Pin
cjb1107-Aug-12 21:08
membercjb1107-Aug-12 21:08 
Questioncan i read xlsx from your code Pin
Member 894239223-Jul-12 4:08
memberMember 894239223-Jul-12 4:08 
GeneralMy vote of 1 Pin
Wooln30-Aug-11 20:57
memberWooln30-Aug-11 20:57 
GeneralOther solution Pin
CikaPero10-Apr-11 22:24
memberCikaPero10-Apr-11 22:24 
General5 Star Article Pin
Code Hungery (Rashed Nadeem)5-Apr-11 0:46
memberCode Hungery (Rashed Nadeem)5-Apr-11 0:46 
General[My vote of 1] Misleading article title Pin
PaulLinton4-Apr-11 12:25
memberPaulLinton4-Apr-11 12:25 
GeneralMy vote of 5 Pin
SureshDeyna29-Mar-11 16:07
memberSureshDeyna29-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 | Terms of Use | Mobile
Web03 | 2.8.150603.1 | Last Updated 8 Aug 2012
Article Copyright 2011 by Abdul Quader Mamun
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid