?
Solved

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

Posted on 2014-03-04
10
Medium Priority
?
1,522 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39904603
0
 
LVL 13

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

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 13

Accepted Solution

by:
Jitendra Patil earned 1500 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 27

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

771 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