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
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()
{
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.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;
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