import text file(500 MB) data into SQL Server

asp.net c# sqlbulkcopy sql-server

Question

The code I use to import text files bigger than 500 MB into databases is shown below.

I want to execute it in batches so that at least half of the text file's contents will be uploaded if a format-related problem occurs during execution.

Please provide any more recommendations for uploading such a huge text file.

private DataTable CreateDataTableFromFileLoop()
{
    string filename = "";

    if (fileuploadExcel.HasFile)
    {
       try
       {
           filename = Path.GetFileName(fuTextLoop.FileName);
           fuTextLoop.SaveAs(Server.MapPath("~/callText") + filename);
           //StatusLabel.Text = "Upload status: File uploaded!";
       }
       catch (Exception ex)
       {
           StatusLabel.Text = "Upload status: The file could not be uploaded. The following error occured: " + ex.Message;
       }
    }

    DataTable dt = new DataTable();
    DataColumn dc;
    DataRow dr;

    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.String");
    dc.ColumnName = "Sr No";
    dc.Unique = false;
    dt.Columns.Add(dc);

    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.String");
    dc.ColumnName = "Mobile";
    dc.Unique = false;
    dt.Columns.Add(dc);

    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.String");
    dc.ColumnName = "Name";
    dc.Unique = false;
    dt.Columns.Add(dc);

    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.String");
    dc.ColumnName = "Fath_Hus_Name";
    dc.Unique = false;
    dt.Columns.Add(dc);

    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.String");
    dc.ColumnName = "Address";
    dc.Unique = false;
    dt.Columns.Add(dc);

    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.String");
    dc.ColumnName = "City";
    dc.Unique = false;
    dt.Columns.Add(dc);

    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.String");
    dc.ColumnName = "PIN Code";
    dc.Unique = false;
    dt.Columns.Add(dc);

    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.String");
    dc.ColumnName = "Contact Number";
    dc.Unique = false;
    dt.Columns.Add(dc);

    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.String");
    dc.ColumnName = "Activation_date";
    dc.Unique = false;
    dt.Columns.Add(dc);

    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.String");
    dc.ColumnName = "Subs_type";
    dc.Unique = false;
    dt.Columns.Add(dc);

    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.String");
    dc.ColumnName = "Status";
    dc.Unique = false;
    dt.Columns.Add(dc);

    dc = new DataColumn();
    dc.DataType = System.Type.GetType("System.String");
    dc.ColumnName = "Subs_name";
    dc.Unique = false;
    dt.Columns.Add(dc);

    StreamReader sr = new StreamReader(Server.MapPath("~/callText") + filename);

    SqlConnection conn = new  SqlConnection("Server=.;Database=temp;Trusted_Connection=True;");

if (ddlSub.SelectedValue.ToString() == "Reliance")
{
   try
   {
        string input;
        string mob_chk;

        while ((input = sr.ReadLine()) != null)
        {
            string[] s = input.Split(new char[] { ',' });

            dr = dt.NewRow();
            SqlCommand comm = new SqlCommand("select Mobile from call where Mobile='" + s[1] + "'", conn);
            conn.Open();
            SqlDataReader sdr = comm.ExecuteReader();

            if (sdr.HasRows)
            {
                goto end_of_loop;
            }
            if (!sdr.HasRows)
            {
                dr["Sr No"] = s[0];
                dr["Mobile"] = s[1];
                dr["Name"] = s[3];
                dr["Fath_Hus_Name"] = s[4]+s[5]+s[6] + s[7];
                dr["Address"] = s[8]+s[9];
                dr["City"] = s[10];
                dr["PIN Code"] = s[11];
                dr["Contact Number"] = s[16];
                dr["Activation_date"] = s[18];
                dr["Subs_type"] = s[15];
                //dr["Status"] = s[10];
                dr["Subs_name"] = ddlSub.SelectedValue.ToString();
            }

            dt.Rows.Add(dr);
        end_of_loop:
            conn.Close();
        }

        sr.Close();

        dt.Rows[0].Delete();
   }
   catch (Exception ex)
   {
           StatusLabel.Text = "Upload status: The file could not be uploaded. The following error occured: " + ex.Message;
   }
}

if (dt.Rows.Count > 0)
{
   return dt;
}
else
{
   return null;
}
1
1
6/28/2012 5:59:06 AM

Accepted Answer

You may always process the full file and release disk space from the rows as you process them if the file will be processed altogether. To do this, process the file backwards and truncate it after each record has been processed. Additionally, if you resume from a mistake, you may "safely" read the full file.

To maintain track of the file, you would first need to define a FileStream and feed it to the StreamReader.

FileStram fs = new FileStream(Server.MapPath("~/callText") + filename, FileMode.Open);
StreamReader sr = new StreamReader(fs);

Now that a n or r has been located, you may read the file backwards until you reach "sr.ReadLine," but first you need to know how big the file is so you know how big it should be after you have finished reading the row.

long oldLen = fs.Length;
fs.Seek(-2,SeekOrigin.End);//dont really begin from the end, because a line-end is very likely to be right there
//here goes a simple while to read the file backwards until you find '\n' or '\r'. 
...
//you should aso check for BOF
fs.Seek(1,SeekOrigin.Current); //this is case that sr.Readline() gets confused with the line-end I just found
//here you can input=sr.ReadLine() and all you have to do
...
...

You may now simply truncate it by subtracting the size of the row you just read from the original size.

fs.SetLength(oldLen - Encoding.Unicode.GetByteCount(input));//replace Unicode with whatever encoding the file has.

If there is a mistake or the operation is complete, you may also add up the sizes of the rows you read and truncate the file.

0
6/28/2012 7:21:31 AM

Popular Answer

You are referring to text files, correct? Because fileuploadexcel is used in your code...

Anyway, if it is text, then http://www.filehelpers.com is by far the simplest method.

It is quick and simple to use, and it supports fixed and delimited formats.

One of its many error modes enables you to achieve this.

engine.ErrorManager.ErrorMode = ErrorMode.SaveAndContinue;

records = engine.ReadFile(...

if (engine.ErrorManager.HasErrors)
   foreach (ErrorInfo err in engine.ErrorManager.Errors)
   {
      Console.WriteLine(err.LineNumber); 
      Console.WriteLine(err.RecordString);
      Console.WriteLine(err.ExceptionInfo.ToString());`
   }

As a consequence, any data that complies with the format is imported.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow