Solved

not all  rows are loaded into table

Posted on 2011-09-19
8
188 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Reading the Web.Config using IIS 7.5? 4 35
EF5: Question about Metadata Artifact Processing 4 15
Problem!!! 8 33
What Does This C# Code Block Do? 5 24
In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

815 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

8 Experts available now in Live!

Get 1:1 Help Now