Solved

How to StreamRead CSV file and pass to DataTable then Bulk Copy SQL

Posted on 2014-03-04
10
1,461 Views
Last Modified: 2014-04-08
Hello Experts,

I'm trying to StreamRead a CSV file, pass the values to a class object which get read into a DataTable then Bulk Copy to SQL.  Also, prior to passing the values to the object it checks sql for a duplicate date.  If a duplicate date is found then delete all rows with that date then bulk copy.  My proplem is that only one row get bulk copy of thousands of rows.

Below are my codes.  Thank you for your assistance.

CSV file

02/26/2014,57693,08,CBT ,001602,03/02/2014, 0 , 0 ,C, 18059 , 376244 
02/26/2014,23512,01,CBT ,001602,03/02/2014, 72 , 0 ,N, 18059 , 376244 
02/26/2014,45664,01,CBT ,001602,03/02/2014, 48 , 0 ,C, 18059 , 376244 
02/26/2014,58202,01,CBT ,001602,03/02/2014, 0 , 0 ,C, 18059 , 376244 
02/26/2014,34376,01,CBT ,001602,03/02/2014, 0 , 0 ,N, 18059 , 376244

Open in new window


ASPX

<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %>

 <telerik:RadAsyncUpload ID="ReportFileUploader" runat="server" Width="270px" ToolTip="Select Report File to Upload" 
            OnFileUploaded="ReportFileUploader_FileUploaded" MaxFileInputsCount="1" MultipleFileSelection="Automatic" 
            AllowedFileExtensions="txt" Skin="Forest" ></telerik:RadAsyncUpload>


          <br />
            <telerik:RadButton ID="UploadFileButton" runat="server" Text="Upload File" Skin="Hay" OnClick="UploadFileButton_Click"></telerik:RadButton>

Open in new window


ASPX.cs

protected void UploadFileButton_Click(object sender, EventArgs e)
        {
            //ValidFiles.Visible = true;

            UploadedFile _docUploader = ReportFileUploader.UploadedFiles[0];          
                       
            Stream filename = _docUploader.InputStream;

            ParseData(filename);
          
        }


 protected void ParseData(Stream filename)
        {
            string[] value;
            string reportDate = "";
            string ownerId = "";
            string nameSuffix = "";
            string parentInitial = "";
            string contractCode = "";
            string expirationDate = "";
            string storyLong = "";
            string storyShort = "";
            string comFlag = "";
            string interestCount = "";
            string afcOI = "";

            StreamReader streamReader = new StreamReader(filename);
            string row = "";

           while ((row = streamReader.ReadLine()) != null)           
            {
                value = row.Split(new char[] { ',' });
                reportDate = value[0];
                ownerId = value[1];
                nameSuffix = value[2];
                parentInitial = value[3];
                contractCode = value[4];
                expirationDate = value[5];
                storyLong = value[6];
                storyShort = value[7];
                comFlag = value[8];
                interestCount = value[9];
                afcOI = value[10];

                TitleFuture titleFuture = new TitleFuture();
                DateTime _reportDate;
                DateTime.TryParse(reportDate, out _reportDate);
                titleFuture.ReportDate = _reportDate;
                titleFuture.OwnerId = ownerId;
                titleFuture.NameSuffix = nameSuffix;
                titleFuture.ParentInitial = parentInitial;
                titleFuture.ContractCode  = contractCode ;
                DateTime _expirationDate;
                DateTime.TryParse(expirationDate, out _expirationDate);
                titleFuture.ExpirationDate = _expirationDate;
                titleFuture.StoryLong = storyLong;
                titleFuture.StoryShort = storyShort ;
                titleFuture.ComFlag = comFlag;
                titleFuture.InterestCount = interestCount;
                titleFuture.AFCOI = afcOI;


                DbReportDateRetriever _dbReportDateRetriever = new DbReportDateRetriever();
                _dbReportDateRetriever.GetReportDate(_reportDate);
                DateTime dbReportDateNow = _dbReportDateRetriever.dbReportDate;

                if (dbReportDateNow == _reportDate)
                {
                   DataUpdater _dataUpdater = new DataUpdater();
                    _dataUpdater.UpdateData(_reportDate);
                }

                titleFuture.InsertData(titleFuture);
                                                                         
            }
                      
        }

Open in new window


DataUpdater.cs

public class DataUpdater
    {

        public DateTime _dbReportDateNow { get; set; }

        public void UpdateData(DateTime _dbReportDateNow)
        {
            using (SqlConnection conn = DbConnection.DbConnectionManager())
            {
                SqlCommand cmd = new SqlCommand("dbo.spDataUpdater", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@dbReportDateNow", _dbReportDateNow);
                cmd.ExecuteNonQuery();
               
                conn.Close();
            }      
        }
    }

Open in new window


DbReportDataRetriever.cs

public class DbReportDateRetriever
    {
        public DateTime dbReportDate { get; set; }

        public void GetReportDate(DateTime dbReportDate)
        {


            using (SqlConnection conn = DbConnection.DbConnectionManager())
            {
                try
                {                                 
                
                SqlCommand cmd = new SqlCommand("dbo.spGetReportDate", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@dbReportDate", dbReportDate);

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    //this.dbReportDate = Convert.ToDateTime(reader["Report_Date"]);
                    this.dbReportDate = reader.GetDateTime(0);
                   
                }
                reader.Close();
                conn.Close();
            }            
            
            catch (Exception ex)
            {
            
            }

            }
            
        }
    }

Open in new window


TitleFuture.cs

    public class TitleFuture
    {
        public int RecordId { get; set; }
        public DateTime ReportDate { get; set; }
        public string OwnerId { get; set; }
        public string NameSuffix { get; set; }
        public string ParentInitial { get; set; }
        public string ContractCode { get; set; }
        public DateTime ExpirationDate { get; set; }
        public string StoryLong { get; set; }
        public string StoryShort { get; set; }
        public string ComFlag { get; set; }
        public string InterestCount { get; set; }
        public string AFCOI { get; set; }


        public void InsertData(TitleFuture _titleFuture)
        {
            

            DataTable titleFutureData = new DataTable("TitleFutureData");

            DataColumn recordId = new DataColumn();
            recordId.ColumnName = "Record_ID";


            DataColumn reportDate = new DataColumn();
            reportDate.ColumnName = "Report_Date";

            DataColumn ownerId = new DataColumn();
            ownerId.ColumnName = "Owner_Id";

            DataColumn nameSuffix = new DataColumn();
            nameSuffix.ColumnName = "Name_Suffix";

            DataColumn parentInitial = new DataColumn();
            parentInitial.ColumnName = "Parent_Initial";

            DataColumn contractCode = new DataColumn();
            contractCode.ColumnName = "Contract_Code";

            DataColumn expirationDate = new DataColumn();
            expirationDate.ColumnName = "Expiration_Date";

            DataColumn storyLong = new DataColumn();
            storyLong.ColumnName = "Story_Long";

            DataColumn storyShort = new DataColumn();
            storyShort.ColumnName = "Story_Short";

            DataColumn comFlag = new DataColumn();
            comFlag.ColumnName = "Com_Flag";

            DataColumn interestCount = new DataColumn();
            interestCount.ColumnName = "Interest_Count";

            DataColumn afcOI = new DataColumn();
            afcOI.ColumnName = "AFC_OI";

            titleOnlyData.Columns.Add(recordId);
            titleOnlyData.Columns.Add(reportDate);
            titleOnlyData.Columns.Add(ownerId);
            titleOnlyData.Columns.Add(nameSuffix);
            titleOnlyData.Columns.Add(parentInitial);
            titleOnlyData.Columns.Add(contractCode);
            titleOnlyData.Columns.Add(expirationDate);
            titleOnlyData.Columns.Add(storyLong);
            titleOnlyData.Columns.Add(storyShort);
            titleOnlyData.Columns.Add(comFlag);
            titleOnlyData.Columns.Add(interestCount);
            titleOnlyData.Columns.Add(afcOI);


            DataRow titleFutureDataRow = titleOnlyData.NewRow();
            titleFutureDataRow["Record_ID"] = "";
            titleFutureDataRow["Report_Date"] = _titleFuture.ReportDate;
            titleFutureDataRow["Owner_Id"] = _titleFuture.OwnerId;
            titleFutureDataRow["Name_Suffix"] = _titleFuture.TraderSuffix;
            titleFutureDataRow["Parent_Initial"] = _titleFuture.parentInitial;
            titleFutureDataRow["Contract_Code"] = _titleFuture.ContractCode;
            titleFutureDataRow["Expiration_Date"] = _titleFuture.ExpirationDate;
            titleFutureDataRow["Story_Long"] = _titleFuture.StoryLong;
            titleFutureDataRow["Story_Short"] = _titleFuture.StoryShort;
            titleFutureDataRow["Com_Flag"] = _titleFuture.ComFlag;
            titleFutureDataRow["Interest_Count"] = _titleFuture.InterestCount;
            titleFutureDataRow["AFC_OI"] = _titleFuture.AFCOI;

            titleOnlyData.Rows.Add(titleFutureDataRow);

           
            using (SqlConnection conn = DbConnection.DbConnectionManager())
            {
                //conn.Open();

                using (SqlBulkCopy sql = new SqlBulkCopy(conn))
                {
                    sql.DestinationTableName = titleOnlyData.TableName;

                    foreach (var column in titleOnlyData.Columns)
                        sql.ColumnMappings.Add(column.ToString(), column.ToString());

                    sql.WriteToServer(titleOnlyData);

                    conn.Close();
                    
                }

                

            }

        }
        

    }

Open in new window

0
Comment
Question by:iNetBlazer
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39904603
0
 
LVL 12

Expert Comment

by:jitendra patil
ID: 39905654
yes zberteoc is right,

try loading the file in temp table and remove the unnecessary data from the temp table and you will get the data needed, then just try insert from select and all done.

remember all above can be done using storedprocedure.

hope this helps.
0
 

Author Comment

by:iNetBlazer
ID: 39906600
Thanks guys but I can't take that apporach for a couple of reasons.  1. I do not have sql permission to use the bulk load statement.  2. I need to parse the csv file for a particular date and check it for duplicate in sql.  if date exist, delete all those rows then bulk copy the data.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 26

Expert Comment

by:Zberteoc
ID: 39906634
Comparing you can do inside SQL once you loaded the file into a working/load table. It is also much more efficient this way. If you can't use BULK INSERT just load the csv to that working table and then you will update the actual table within SQL server using a stored procedure or some SQL code from application. You can consider the MERGE statement:

http://www.cs.utexas.edu/~cannata/dbms/SQL%20Merge.html

This can help too:

http://www.morgantechspace.com/2013/10/import-csv-file-into-sql-server-using.html
0
 
LVL 12

Accepted Solution

by:
jitendra patil earned 500 total points
ID: 39908719
First import CSV to DataTable Import CSV file to DataTable
then remove duplicates based on your column of interest Removing Duplicate Records from Dataset/DataTable
using below code try bulk insert from datatable.
private void BulkUpload(DataTable dt)
{
    dt.TableName="YourDataTable";
    string constr="your connection string";
    using(SqlConnection connection=new SqlConnection(constr))
    {
      connection.Open();
      //CreatingTranscationsothatitcanrollbackifgotanyerrorwhileuploading
      SqlTransaction trans=connection.BeginTransaction();
     //Start bulkCopy
     using(SqlBulkCopy bulkCopy=new SqlBulkCopy(connection,
     SqlBulkCopyOptions.TableLock|
     SqlBulkCopyOptions.FireTriggers,
     trans))
     {
       //Setting timeout to 0 means no time out for this command will not timeout until upload complete.
     //Change as per you
     bulkCopy.BulkCopyTimeout=0;
     bulkCopy.DestinationTableName=dt.TableName;
     //write the data in the "dataTable"
     bulkCopy.WriteToServer(dt);
    }
   }
}

Open in new window

We have to take care some common mistakes as below.
Name of table in database should match with name of data table.
This will work only with sql server database.
Name of Columns in database should match Columns name of data table.
Name mapping of table name and columns name is case sensitive. Should be same with case matching.

hope this helps.
0
 

Author Comment

by:iNetBlazer
ID: 39909224
Hi Zberteoc

I tried the below link but for some reason the solution does not work.  

http://www.morgantechspace.com/2013/10/import-csv-file-into-sql-server-using.html

When i pass the file csv file into >>> TextFieldParser(filename) and debug it through the "foreach" loop, the method return value result in 0 columns and 0 rows.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39909477
Sorry, VB.NET and C# not my strong part. I am a SQL guy and what I can't do directly from SQL I do using Python. For this kind of job Python is just perfect but C# would work of course.

Here is an example how utterly simple is to do this in Python:

http://www.youtube.com/watch?v=jQ9aDyBWCXI

I hope this will make you consider Python. ;)
0
 
LVL 2

Expert Comment

by:Rob Jurd (eenookami)
ID: 39985815
I've requested that this question be deleted for the following reason:

No solution
0
 

Author Closing Comment

by:iNetBlazer
ID: 39985816
Thank you to everyone on there attempts to offer a solution.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question