Click here to Skip to main content
15,936,119 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi Friends

I have created one Windows Application for import .csv file . But I am unable to load large data inside it .
Ex- more than 25 MB file not load

Please check below code and some screenshots

C#
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using System.IO;
using System.Globalization;
using Microsoft.SqlServer.Server;
using System.Data.Sql;
using System.Data.OleDb;
using System.Threading;


namespace Isolution.csproj
{
    public partial class Form1 : Form
    {
        DataView dv;
        DataSet dataset1;
        string srt;

        public System.Windows.Forms.Button Browse = new Button();
        public System.Windows.Forms.Button Process = new Button();
        public System.Windows.Forms.Button save = new Button();
        public System.Windows.Forms.TextBox Locations = new TextBox();
        public System.Windows.Forms.Label Information = new Label();
        public System.Windows.Forms.DataGridView Datagidveiw1 = new DataGridView();
        public System.Windows.Forms.OpenFileDialog OpenFileDialogbox = new OpenFileDialog();
        public Form1()
        {
            /*DateTime Start = DateTime.Now;
          DateTime End = DateTime.Now;
          TimeSpan CallTime = End - Start;*/
            InitializeComponent();
            this.Browse.Location = new System.Drawing.Point(315, 10);
            this.Browse.Size = new System.Drawing.Size(90, 30);
            this.Browse.Text = "Browse";
            this.Browse.Font = new System.Drawing.Font("Times of Roman", 12f, FontStyle.Bold);
            this.Browse.Enabled = true;
            this.Browse.Visible = true;
            this.Browse.Cursor = Cursors.Hand;
            this.Browse.Click += new EventHandler(Browse_Click);
            this.Controls.Add(this.Browse);
            //***************************************************************
            this.Process.Location = new System.Drawing.Point(430, 10);
            this.Process.Size = new System.Drawing.Size(90, 30);
            this.Process.Text = "Process";
            this.Process.Font = new System.Drawing.Font("Times of Roman", 10f, FontStyle.Bold);
            this.Process.Enabled = true;
            this.Process.Visible = true;
            this.Process.Cursor = Cursors.Hand;
            this.Process.Click += new EventHandler(Process_Click);
            this.Controls.Add(this.Process);
            //*****************************************************************
            this.Locations.Location = new System.Drawing.Point(10, 14);
            this.Locations.Size = new System.Drawing.Size(300, 50);
            this.Locations.Enabled = true;
            this.Locations.Visible = true;
            this.Locations.Font = new System.Drawing.Font("Times of Roman ", 11f, FontStyle.Regular);
            this.Controls.Add(this.Locations);
            //*****************************************************************
            this.Datagidveiw1.Location = new System.Drawing.Point(10, 70);
            this.Datagidveiw1.Size = new System.Drawing.Size(1280, 600);
            this.Datagidveiw1.Enabled = true;
            this.Datagidveiw1.Visible = true;
            this.Datagidveiw1.ReadOnly = true;
            this.Datagidveiw1.ScrollBars = ScrollBars.Both;
            this.Datagidveiw1.TabIndex = 0;
            this.Datagidveiw1.RowHeadersBorderStyle = DataGridViewHeaderBorderStyle.Raised;
            this.Datagidveiw1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.EnableResizing;
            this.Datagidveiw1.ScrollBars = ScrollBars.Both;
            this.Datagidveiw1.DefaultCellStyle.WrapMode = DataGridViewTriState.False;
            this.Datagidveiw1.AllowUserToAddRows = false;
            this.Datagidveiw1.ReadOnly = false;
            this.Datagidveiw1.RowHeadersVisible = false;

            this.Datagidveiw1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize;
            // this.Datagidveiw1.CellFormatting += new DataGridViewCellFormattingEventHandler(Datagidveiw1_CellFormatting);
            this.Datagidveiw1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            this.Datagidveiw1.Font = new System.Drawing.Font("Times of Roman", 9f, FontStyle.Regular);
            this.Controls.Add(this.Datagidveiw1);
            //******************************************************************************************
            this.save.Location = new System.Drawing.Point(540, 10);
            this.save.Size = new System.Drawing.Size(90, 30);
            this.save.Text = "save";
            this.save.Font = new System.Drawing.Font("Times of Roman", 12f, FontStyle.Bold);
            this.save.Enabled = true;
            this.save.Visible = true;
            this.save.Cursor = Cursors.Hand;
            this.save.Click += new EventHandler(save_Click);
            this.Controls.Add(this.save);
            //********************************************************
            this.Information.Location = new System.Drawing.Point(800, 15);
            this.Information.AutoSize = true;
            this.Information.Enabled = true;
            this.Information.Visible = true;
            this.Information.BackColor = Color.Transparent;
            this.Information.ForeColor = Color.Black;
            this.Information.Font = new System.Drawing.Font("Times of roman", 12f, FontStyle.Regular);
            this.Controls.Add(this.Information);
        }
            //*********************************************************

               //*************************************************************************
        public static DataSet GetDataset(string filename)
        {
            
            string Connectionstring = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + Path.GetDirectoryName(filename) +  "\";Extended Properties='text;HDR=Yes;FMT=Delimited(;)'");

            string cmdstring = string.Format(@"SELECT * FROM {0}", Path.GetFileName(filename));          
                  
            
            DataSet dataset = new DataSet();         

            OleDbConnection olconn = new OleDbConnection(Connectionstring);
            {
                olconn.Open();
                OleDbCommand comm = new OleDbCommand(cmdstring);
               OleDbDataAdapter adapter = new OleDbDataAdapter( cmdstring,olconn);
               OleDbCommandBuilder cmdbuilder = new OleDbCommandBuilder(adapter);
               
                  dataset.Clear();
                adapter.Fill(dataset, "Test");
             
                

                olconn.Close();              

            }

            return dataset;
        }

        public void Browse_Click(object sender, EventArgs e)
        {
            DateTime  startTime = DateTime.Now;

            int intcount;

            try
            {
                OpenFileDialogbox.InitialDirectory = Application.ExecutablePath.ToString();
                DialogResult RESULT = this.OpenFileDialogbox.ShowDialog();
                if (RESULT == DialogResult.OK)
                {
                    string filename = OpenFileDialogbox.FileName;
                    Locations.Text = filename;


                }
                else
                {
                    MessageBox.Show("Dear User Please select Path");
                }

                dataset1 = Form1.GetDataset(Locations.Text);
                Datagidveiw1.DataSource = dataset1.Tables[0].DefaultView;

                intcount = dataset1.Tables[0].Rows.Count;
                MessageBox.Show("Total Rows are " + intcount.ToString());

                for (int c = 0; c < dataset1.Tables[0].Rows.Count; c++)
                {
                    string idias = dataset1.Tables[0].Rows[c][0] + "";
                    if (idias== "")
                        dataset1.Tables[0].Rows.RemoveAt(c);

                }
                dataset1.AcceptChanges();
                srt = "Total_data_Load_seonds : ";
                DateTime endtime = DateTime.Now;
                TimeSpan diff = endtime.Subtract(startTime);
                Information.Text=  srt + diff.Seconds.ToString() + "," + diff.TotalSeconds.ToString();              
            }




            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }   

   

        public void Process_Click(object sender, EventArgs e)
        {
            char comma = ',';
            char slash = '/';
            char colon = '"';
            string Blank = "";

            DateTime startTime1 = DateTime.Now;
            
           
            try
            {

                dv = dataset1.Tables[0].DefaultView;
                dv.Sort = "[INCIDENT_NUMBER] Desc,[START_DATE] asc, [END_DATE] asc";
                Datagidveiw1.DataSource = dv;


                //**************Replace values in dataset *90867 client code of product

                foreach (DataRow datarow in dv.Table.Rows)
                {
                    foreach (DataColumn datacol in dv.Table.Columns)
                    {
                        datarow[datacol] = System.Text.RegularExpressions.Regex.Replace(datarow[datacol].ToString(), comma.ToString(), slash.ToString());
                        datarow[datacol] = System.Text.RegularExpressions.Regex.Replace(datarow[datacol].ToString(), colon.ToString(), Blank.ToString());
                    }
                }
                char ndot = '.';
                char SR = '/';
                string strColon = ndot.ToString();
                string strBlank = SR.ToString();
                foreach (DataRow datarow in dv.Table.Rows)
                {
                    foreach (DataColumn datacol in dv.Table.Columns)
                    {
                        string s = datarow[7].ToString();
                        string r = datarow[8].ToString();
                        s = s.Replace(ndot, SR);
                        r = r.Replace(ndot, SR);
                        s = s.Replace(strColon, strBlank);
                        r = r.Replace(strColon, strBlank);
                        datarow[7] = s;
                        datarow[8] = r;
                    }
                }



                Datagidveiw1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
                MessageBox.Show("Process is Complete");

                srt = "Total_Process_seonds : ";
                DateTime endtime1 = DateTime.Now;
                TimeSpan diff = endtime1.Subtract(startTime1);
                Information.Text = srt + diff.Seconds.ToString() + "," + diff.TotalSeconds.ToString();
                
            }
            catch (System.Exception ex)
            {       
               MessageBox.Show(ex.Message);
            }
        }
        public void DataExport()
        {
            try
            {

                string strColumn = string.Empty;
                string strRow = string.Empty;
                StringBuilder objSB = new StringBuilder();

                for (int i = 0; i < Datagidveiw1.Columns.Count; i++)
                {
                    strColumn += (i >= Datagidveiw1.Columns.Count - 1) ? Datagidveiw1.Columns[i].Name : Datagidveiw1.Columns[i].Name + ",";
                }

                objSB.AppendLine(strColumn.Trim());


                for (int i = 1; i < Datagidveiw1.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < Datagidveiw1.Columns.Count; j++)
                    {
                        strRow += (j >= Datagidveiw1.Columns.Count - 1) ? Datagidveiw1.Rows[i].Cells[j].Value.ToString().Replace("\n", "") : Datagidveiw1.Rows[i].Cells[j].Value.ToString().Replace("\n", "") + ",";
                    }
                    objSB.AppendLine(strRow.ToUpperInvariant());
                    strRow = string.Empty;
                }

                File.AppendAllText(Locations.Text, objSB.ToString());

                Datagidveiw1.Refresh();

                MessageBox.Show("Done!");
                Application.Exit();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
          public void save_Click(object sender, EventArgs e)
        {

            try
            {
                SaveFileDialog SaveDialogbox = new SaveFileDialog();
                SaveDialogbox.FileName = Locations.Text;
                SaveDialogbox.Filter = "Text and CSV Files(*.txt, *.csv)|*.txt;*.csv|Text Files(*.txt)|*.txt|CSV Files(*.csv)|*.csv|All Files(*.*)|*.*";
                SaveDialogbox.FilterIndex = 1;
                SaveDialogbox.RestoreDirectory = true;
                DialogResult Dialogresult1 = SaveDialogbox.ShowDialog();
                if (Dialogresult1 == DialogResult.OK)
                {
                    Locations.Text = SaveDialogbox.FileName;
                    DataExport();
                    Application.DoEvents();
                }

                 else
                 {
                 MessageBox.Show("Invalid Arguments");
                }
             }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}
Please see Error Screen Shot .

Thanks for your answer in advance
Vishal
Posted
Updated 21-Aug-12 5:39am
v2
Comments
[no name] 21-Aug-12 11:42am    
Do you really think that "unable to load large data" is a good description of your problem? Not very many people are going to want to take the time to debug your code for you. You might want to chop this down to something that is relevant to your problem and then ask a specific question.
vishu1999a 21-Aug-12 13:29pm    
Dear wes
My problem is that , i am unable to Import large file into datagridveiw , Its givem error " Syntax error in FROM clause "
thanks for your answer in advance

1 solution

Without getting too involved in your code, I will recommend this project from a CP member: A Fast CSV Reader[^] If your issue is performance related, I would definitely look into using this.
 
Share this answer
 
Comments
fjdiewornncalwe 21-Aug-12 14:07pm    
Agreed. I used that project as a starting point for a very large CSV import process I was working on a while back. Excellent resource. +5.
vishu1999a 25-Aug-12 7:45am    
Hi Friend

Thanks for your great help

but i have not receive better solution.
can you see once above code ?
only problem that data take a too much time to load ,
and when csv file load it rows and columns not autofit.
its was looking in very bad format .

please look into issue

thanks for your answer in advance.

vishal

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900