Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

not all  rows are loaded into table

Posted on 2011-09-19
8
Medium Priority
?
195 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 45

Accepted Solution

by:
AndyAinscow earned 1000 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 45

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 45

Expert Comment

by:AndyAinscow
ID: 36574213
Comment #36560640 points out the problem why not all rows are being added.
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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

885 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