Solved

not all  rows are loaded into table

Posted on 2011-09-19
8
187 Views
Last Modified: 2012-05-12
When I load my  data is only getting 3829 loaded out of 15913 rows of data
SELECT     COUNT(*) AS Expr1
FROM         load_into_wf 

Open in new window


using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;





namespace import_from_excel_1
{
    class Program
    {
        static void Main(string[] args)
        {

            
SqlConnection con = new SqlConnection("Data Source=USVAVI; Initial Catalog=ANDREWS_DB_REPVIEW; User Id=sa; Password= azkaban09$");
            string filepath = "o:\\EE_Roster_ALL.csv";
            StreamReader sr = new StreamReader(filepath);
            string line = sr.ReadLine();
            string[] value = line.Split(',');
            DataTable dt = new DataTable();
            DataRow row;
            foreach (string dc in value)
            {
                dt.Columns.Add(new DataColumn(dc));
            }

            while ( !sr.EndOfStream )
            {
                value = sr.ReadLine().Split(',');
                if(value.Length == dt.Columns.Count)
                {
                    row = dt.NewRow();
                    row.ItemArray = value;
                    dt.Rows.Add(row);
                }
            }
            SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
            bc.DestinationTableName = "load_into_wf";
            bc.BatchSize = dt.Rows.Count;
            con.Open();
            bc.WriteToServer(dt);
            bc.Close();
            con.Close(); 
 










                }
            }
        }
    

 

Open in new window


EE-Roster-ALL.csv
0
Comment
Question by:jaypappas
  • 3
  • 3
8 Comments
 
LVL 44

Accepted Solution

by:
AndyAinscow earned 250 total points
ID: 36560640
Your line 37 :                 if(value.Length == dt.Columns.Count)
looks suspicious.  This would result in rows NOT being added (your bobserved behaviour) if the statement was not true.
0
 

Author Comment

by:jaypappas
ID: 36560765
Your line 37 :                 if(value.Length == dt.Columns.Count)
looks suspicious.  This would result in rows NOT being added (your bobserved behaviour) if the statement was not true.  


 it does not know what to do with lines with quotes in them.

example:

ANV,403605,353605,Batte,Alonzo Gene,A,H,P,9/8/2008,3020SF,San Francisco Office,NPUNI,Unarmed Security Officer,334159,"Mitchell,Richard D",3500
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 36560856
Do you mean the quote marks are the cause of your problem ?


ps.  I hope that isn't real information detailing who an 'unarmed security officer' is (name + address....).
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:jaypappas
ID: 36561599
Yes the quotes are causing the problem.  This data is sample data not real data.
0
 

Author Comment

by:jaypappas
ID: 36574212
I've requested that this question be deleted for the following reason:

No timely responses
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 36574213
Comment #36560640 points out the problem why not all rows are being added.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Windows Service to Receive TCP Packets 4 121
install app on other machine 13 32
Connecting database in Xamarin? 6 51
Linq Help 1 35
Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

911 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

23 Experts available now in Live!

Get 1:1 Help Now