Issues with Import and Export

[KamalBansal]

Hello Everyone,
I'm facing with one problem since two days at importing and exporting data from different file format, actually I want to import data from different file format such as txt, csv, xls etc in sql server and apply some filtration in it. After that export data in these files formats. So I want ti know whether any option in sql server to perform this actions or I have to use any third party software?
please give me appropriate answer.

[thines01]

Yes, it is possible to use C# code to import data from a file into the database.
It is also possible to use C# code to export data from a database into a file.

[Cameronsmith63]

copy and pasted from a other thread, works for CSV. For excel, you going to have to use interops, and also force a GC to make sure the process is closed.

1) read csv, create odbc connection to treat the csv as if it was a table itself. Store to datagrid if you want(for viewing purposes)
C# Syntax (Toggle Plain Text)

string tempPath = @"path";
      string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + tempPath + @"\;Extensions=asc,csv,tab,txt";
      OdbcConnection conn = new OdbcConnection(strConn);
      OdbcDataAdapter da = new OdbcDataAdapter("Select [Terminal Number], Status from [" + file + "] where " + Status + "", conn);
      conn.Open();
      da.Fill(dt);
      dgvData.DataSource = dt;
      dgvData.Columns[1].DefaultCellStyle.Format = "T";
      conn.Close();

2) Do a bulk insert

C# Syntax (Toggle Plain Text)

private void Bulk1(object sender, EventArgs e)//Does a bulk copy into reply table, updates download table with today's date
   {
      string tempPath = @"path";
      string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + tempPath + @"\;Extensions=asc,csv,tab,txt";
      OdbcConnection conn = new OdbcConnection(strConn);
      dt.Clear();
      OdbcDataAdapter da = new OdbcDataAdapter("Select [Terminal Number], Status from [" + file + "] where Status = 'VERIFIED'", conn);
      conn.Open();
      da.Fill(dt);
      dgvData.DataSource = dt;
      dgvData.Columns[1].DefaultCellStyle.Format = "T";
      conn.Close();
      conn.Dispose();
      thisConnection = new SqlConnection(@"Network Library = DBMSSOCN; Data Source="SERVER"; Initial Catalog=RemoteDownload; integrated security = true");
      thisConnection.Open();
      //String Release = SWRelease.Text; used with a odbc adapter and sql command
      String Release = SWRel.Text;
      SqlCommand thisCommand = thisConnection.CreateCommand();
      thisCommand.CommandText = "truncate table "TABLE"";
      thisReader = thisCommand.ExecuteReader();
      thisReader.Close();
 
      using (SqlBulkCopy bulkCopy = new SqlBulkCopy(thisConnection))
      {
         bulkCopy.DestinationTableName = ""TABLE"";
 
         try
            {  // Write from the source to the destination.
               bulkCopy.WriteToServer(dt);
            }
 
         catch (Exception ex)
            {
               Console.WriteLine(ex.Message);
               thisConnection.Close();
               Console.ReadLine();
               }
 
         finally
               {
               thisConnection.Close();
               }
            thisReader.Close();
            thisConnection.Close();
         }
         thisCommand.CommandText = "update "TABLE" set "COLUMN" = '" + currentYear + "-" + currentMonth + "-" + currentDay + " 00:00:00.000' where "COLUMN" is null and "COLUMN" is null and "COLUMN" in (Select "COLUMN" from TABLE where Status = 'VERIFIED')";
         thisConnection.Open();
         thisReader = thisCommand.ExecuteReader();
         thisReader.Close();
         thisConnection.Close();
   }

[SunilDubey]

Hi KamalBansal,
SQL Server has an option to solve such kind of task. To resolve this problem you have to use SQL BULK query command. You have also use a third party tool to resolve this problem. There are lots of famous third party tool such as MindStick DataConverter, AutoConverter etc. I had used MindStick DataConverter, it is free charge tool so you can easily download it from following URL...

http://www.mindstick.com/Product/ProductDC.aspx

I hope it might be useful for you.

[Cameronsmith63]

I don't like the idea of third party tools, simply because its not your own code. That method that I have provided has a bulk copy in it as well.