Solved

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

Posted on 2014-03-04
10
1,430 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
Comment Utility
0
 
LVL 12

Expert Comment

by:Jitendra Patil
Comment Utility
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
Comment Utility
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
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 12

Accepted Solution

by:
Jitendra Patil earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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:eenookami
Comment Utility
I've requested that this question be deleted for the following reason:

No solution
0
 

Author Closing Comment

by:iNetBlazer
Comment Utility
Thank you to everyone on there attempts to offer a solution.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now