title: Fastest way to load data from text file then store it into database
body: <p>I have problem.</p>
  
  <p>I'm developing a project but I'm stuck in this part:</p>
  
  <p>I want to load a data from text file and store it into database access
  the things is the data inside each text file about 12.000 lines of data
  and each text file it takes about 10 minute to process it..</p>
  
  <p>NOTE : before store the data, I separate each line of data from text file and put it into string then I check whether the data is already inside database or not.
  if inside the database I update it. If not then I use insert statement..</p>
  
  <p>I'm using C# to develop this program?
  is there any fastest way to load and store this data?</p>
  
  <p>UPDATED:</p>
  
  <p>This is my code I hope it will help to understand my problems:</p>
  
  <pre><code>    using System;
  using System.Collections.Generic;
  using System.ComponentModel;
  using System.Data;
  using System.Drawing;
  using System.Text;
  using System.Windows.Forms;
  using System.IO;
  using System.Collections;
  using System.Data.OleDb;
  
  namespace DAF
  {
      public partial class FrontForm : Form
      {
          public Boolean status;
  
          public FrontForm()
          {
              InitializeComponent();
  
              //define location of the database
              string connection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\PC\Desktop\Graduation Project\Research\DAF\Data\DAFLogisticDepartment.mdb";
  
              //define location of the text file data
              DirectoryInfo di = new DirectoryInfo(@"C:\Users\PC\Desktop\Graduation Project\Research\DAF\Data\");
              FileInfo[] fiarr = di.GetFiles("*.txt");
  
  
              //define connection to database
              OleDbConnection con = new OleDbConnection(connection);
              String query;
              OleDbDataReader rdr = null;
  
              con.Open();
              //get all table from database
              OleDbCommand cmd = con.CreateCommand();
              DataTable dt = con.GetSchema("tables");
              DataRow[] dttable = dt.Select();
              con.Close();
  
              //read each new textfile inside the folder
              foreach (FileInfo fri in fiarr)
              {
                  StreamReader sr = new StreamReader(fri.FullName, System.Text.Encoding.Default);
                  String line;
                  String tabledbs, dbsName;
  
                  while ((line = sr.ReadLine()) != null)
                  {
                      String VRSD, locationID, truckID, yearIn, yearOut, weekIn, weekOut, dayIn, dayOut, timeIn, timeOut, route;
                      int plantID;
  
                      //process each line of data and put into each variable
                      VRSD = line.Substring(0, 4).Trim();
                      plantID = Convert.ToInt32(line.Substring(4, 1).Trim());
                      locationID = line.Substring(5, 4).Trim();
                      truckID = line.Substring(24, 5).Trim();
                      yearIn = line.Substring(32, 4).Trim();
                      weekIn = line.Substring(36, 2).Trim();
                      dayIn = line.Substring(38, 1).Trim();
                      timeIn = line.Substring(39, 8).Trim();
                      yearOut = line.Substring(47, 4).Trim();
                      weekOut = line.Substring(51, 2).Trim();
                      dayOut = line.Substring(53, 1).Trim();
                      timeOut = line.Substring(54, 8).Trim();
                      route = line.Substring(64, 2).Trim();
  
                      //make database name
                      dbsName = plantID + locationID;
  
                      con.Open();
                      //check if the table exist in database
                      for (int i = 0; i &lt; dttable.Length - 9; i++)
                      {
                          tabledbs = dttable[i]["TABLE_NAME"].ToString();
                          ArrayList indexlist = new ArrayList();
  
                          if (tabledbs == dbsName)
                          {
                              //if the table exist, status = true
                              status = true;
                              break;
                          }
                      }
                      con.Close();
  
                      con.Open();
  
                      if (status == true)
                      {
  
                          try
                          {        
                              //if the data not in the system, insert statement
                              query = @"insert into " + plantID + locationID + " values('" + VRSD.ToString() + "'," + plantID + ",'" + locationID + "','" + truckID + "','" + yearIn + "','" + weekIn + "','" + dayIn + "','" + timeIn + "','" + yearOut + "','" + weekOut + "','" + dayOut + "','" + timeOut + "')";
                              cmd = new OleDbCommand(query, con);
                              rdr = cmd.ExecuteReader();
                              con.Close();
                          }
                          catch
                          {
                              //if the data in the system, update statement
                              query = @"update " + dbsName + " set YearIn='" + yearIn + "', YearOut='" + yearOut + "', WeekIn='" + weekIn + "', WeekOut='" + weekOut + "', DayIn='" + dayIn + "', DayOut='" + dayOut + "', TimeIn='" + timeIn + "', TimeOut='" + timeOut + "' where LocationID='" + locationID + "' and PlantID=" + plantID + "";
                              cmd = new OleDbCommand(query, con);
                              rdr = cmd.ExecuteReader();
                              con.Close();
                          }
  
                      }
                      else
                      {
                          //create new table
                          string attribute = "VRSD String,PlantID Integer, LocationID String, TruckID String," +
                                              "YearIn String, WeekIn String, DayIn String, TimeIn String," +
                                              "YearOut String, WeekOut String, DayOut String, TimeOut String";
  
                          query = @"CREATE TABLE " + plantID + locationID + "(" + attribute + ")";
                          cmd = new OleDbCommand(query, con);
                          cmd.ExecuteNonQuery();
  
                          //insert the data
                          query = @"insert into " + plantID + locationID + " values('" + VRSD.ToString() + "'," + plantID + ",'" + locationID + "','" + truckID + "','" + yearIn + "','" + weekIn + "','" + dayIn + "','" + timeIn + "','" + yearOut + "','" + weekOut + "','" + dayOut + "','" + timeOut + "')";
                          cmd = new OleDbCommand(query, con);
                          rdr = cmd.ExecuteReader();
                          con.Close();
                      }
  
                      status = false;
                  }
                  sr.Close();
  
                  //after the text file load into database, the text file moved to history folder
                  MessageBox.Show(fri.FullName.ToString(), "File Manager", MessageBoxButtons.OK);
                  fri.MoveTo(@"C:\Users\PC\Desktop\Graduation Project\Research\DAF\Data\History\" + fri.Name.ToString() + ".txt");
              }
          }
  
          private void button2_Click(object sender, EventArgs e)
          {
              StandardReport sr = new StandardReport();
              sr.Show();
          }
  
          private void FrontForm_Load(object sender, EventArgs e)
          {
  
          }
      }
  }
  </code></pre>