Popular Posts

There was an error in this gadget

Mar 4, 2013

Import data from CSV to sqlserver with additional field






<tr><td>CWR File: </td>
            <td><asp:FileUpload ID="JDE_CWR_FileUpload" runat="server" />
                                    <asp:Label ID="Label1" runat="server"></asp:Label></td></tr>
            





string fileExt = System.IO.Path.GetExtension(JDE_CWR_FileUpload.FileName);  //Get extension

                    if (fileExt == ".csv")   //check to see if its a .csv file
                    {

                        path = Server.MapPath("App_Data/upload/");
                        JDE_CWR_FileUpload.SaveAs(path + JDE_CWR_FileUpload.FileName);

                        JDE_CWR_FileUpload.SaveAs("C:\\temp\\" + JDE_CWR_FileUpload.FileName);
                        string sourceConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;
                                        Data Source=C:\temp\;
                                        Extended Properties=text;";
                        DataTable sourceData = new DataTable();
                        using (OleDbConnection conn =
                                   new OleDbConnection(sourceConnString))
                        {
                            conn.Open();
                            // Get the data from the source table as a SqlDataReader.

 OleDbCommand command = new OleDbCommand(@"SELECT *,'" + ARCRIVE_INSERTED_ID + "' as EXCELCUSTOM from [" + JDE_CWR_FileUpload.FileName + "]", conn);
 OleDbDataAdapter adapter = new OleDbDataAdapter(command);
                            adapter.Fill(sourceData);
                            conn.Close();
                        }

                        CopyDataCWR(sourceData);
                        




public static void CopyDataCWR(DataTable sourceData)
    {
        string destConnString = ConfigurationManager.ConnectionStrings["db_connection_string"].ConnectionString;
        // Set up the bulk copy object.
        using (SqlBulkCopy bulkCopy =
                          new SqlBulkCopy(destConnString))
        {
            bulkCopy.DestinationTableName = "dbo.t_CWR";
            // Guarantee that columns are mapped correctly by
            // defining the column mappings for the order.
            bulkCopy.ColumnMappings.Add("EXCELCUSTOM", "CWR_VAT_ARCRIVE_ID");
            bulkCopy.ColumnMappings.Add("Order Number", "orderNumber");
            bulkCopy.ColumnMappings.Add("Or Ty", "orTy");
            bulkCopy.ColumnMappings.Add("Line Number", "lineNumber");
            bulkCopy.ColumnMappings.Add("Zone", "zone");
           
            // Write from the source to the destination.
            bulkCopy.WriteToServer(sourceData);
        }
    }


No comments:

Post a Comment