C # rimuovendo il carattere unicode

c# excel sqlbulkcopy unicode

Domanda

Sto tentando di inserire dati di testo da un foglio di lavoro Excel in una tabella MS SQL utilizzando la classe C # sqlBulkCopy. Il problema che sto avendo è che ricevo il famigerato Ricevuto una lunghezza di colonna non valida dal client bcp per l' errore colid 6 . Successivamente scopro che il vero problema è la formattazione Excel Unicode all'interno dei testi delle note. se inserisco le note nelle celle di Excel, l'inserimento dei dati funziona, altrimenti fallisce. Ecco il codice che sto tentando di utilizzare nel mio script C #:

 SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(DBconn.ConnectionString);
            {
                sqlBulkCopy.DestinationTableName = "##MasterFileTemp";



                foreach (DataColumn dc in MasterFileTemp.Columns)
                {
                    for (int j = 1; j < MasterFileTemp.Rows.Count - 1; j++)
                    {
                        if (MasterFileTemp.Rows[0][dc].ToString() == "Notes")
                        {

                            int pos = dc.Ordinal;
                            string dataText = Regex.Replace(MasterFileTemp.Rows[j][pos].ToString(), @"[^\u0000-\u007F]", string.Empty);
                            MasterFileTemp.Rows[j][pos] = dataText;
                            MasterFileTemp.AcceptChanges();
                            MessageBox.Show(MasterFileTemp.Rows[j][pos].ToString());


                        }
                    }
                }
                sqlBulkCopy.WriteToServer(MasterFileTemp);

Ecco come appaiono i dati di testo di Excel, si noti le doppie virgolette iniziali e finali generate da Excel:

"-Se sono necessarie informazioni aggiuntive, contattare il richiedente partner nella scheda Ordine di servizio all'interno dell'area di lavoro.Per eventuali altri problemi operativi, inviare un'email a James

** Le richieste di servizio includono la gestione del portafoglio (elenco di controllo), preclusione, preclusione, altri beni immobili di proprietà (OREO), solo la valutazione del patrimonio (nessun prestito)

"

string strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\";";
string GetExcelData = "Select * From [" + tabName + "A23:Z100]";
OleDbConnection cn = new OleDbConnection(strCn);
OleDbDataAdapter objAdapter2 = new OleDbDataAdapter(GetExcelData, cn);
                    DataSet ds2 = new DataSet();
                    objAdapter2.Fill(ds2, "dSheet1");
                    DataTable dt2 = ds2.Tables["dSheet1"];


      Here the entired code: 

namespace ST_426cda87cffe4ef6a10722ecf5f7fe65.csproj {[System.AddIn.AddIn ("ScriptMain", Version = "1.0", Publisher = "", Descrizione = "")] public partial class ScriptMain: Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase {

    #region VSTA generated code
    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    #endregion

    public void Main()
    {

        Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook excelBook = xlApp.Workbooks.Open(Dts.Variables["User::MasterFileTemplate"].Value.ToString(),
             0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

        String[] excelSheets = new String[excelBook.Worksheets.Count];
        int z = 0;
        foreach (Microsoft.Office.Interop.Excel.Worksheet wSheet in excelBook.Worksheets)
        {
            excelSheets[z] = wSheet.Name;
            z++;
        }
        excelBook.Close(false, Dts.Variables["User::MasterFileTemplate"].Value.ToString(), Missing.Value);
        xlApp.Quit();
        process_worksheets(excelSheets);
    }

    public void process_worksheets(string[] wsheets)
       {
        int r;            
        string[] vars = new string[1];
        string field;
        string filePath = (Dts.Variables["User::MasterFileTemplate"].Value.ToString());
        string DataServer = (Dts.Variables["User::DataServer"].Value.ToString());
        string strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\";";


          for (r=0; r < wsheets.Length; r++)
               {
                string tabName = wsheets[r].ToString() + "$";
                string GetExcelDate = "Select * From [" + tabName + "A15:B16]";   //This is the Excel line number for Prod Completed date and Completed by
                string GetExcelData = "Select * From [" + tabName + "A23:Z100]";  //This is the Excel line number where the header columns and data start   

                OleDbConnection cn = new OleDbConnection(strCn);
                SqlConnection DBconn = new SqlConnection();
                DBconn.ConnectionString = "Data Source="+DataServer + ";Initial Catalog=FNC;" + "Integrated Security=SSPI";

                OleDbDataAdapter objAdapter = new OleDbDataAdapter(GetExcelDate, cn);

                // This Dataset contains the header columns and data
                DataSet ds = new DataSet();
                objAdapter.Fill(ds, "dSheet1");
                DataTable dt = ds.Tables["dSheet1"];

                ///****  Parse Excel Serial Date  ***/
                string dtt = (dt.Rows[0][1].ToString());
                DateTime signoffDte = DateTime.Parse(dtt);
                DateTime currentDte = System.DateTime.Today;

                /// Check to see if Production sign-off date is less than current date and signed by is empty
                if ((signoffDte > currentDte) || (dt.Rows[1][1].ToString() == ""))
                {
                 //   MessageBox.Show(tabName.ToString() + "Date requirment Failed..processing next");
                    continue;  //Skip worksheet if Production signoff date or signature is invalid
                }
                else
                {
                    //This Dataset contains the header columns and data
                    OleDbDataAdapter objAdapter2 = new OleDbDataAdapter(GetExcelData, cn);
                    DataSet ds2 = new DataSet();
                    objAdapter2.Fill(ds2, "dSheet1");
                    DataTable dt2 = ds2.Tables["dSheet1"];


                    DataTable dth = dt2.Clone();
                    dth.ImportRow(dt2.Rows[0]);

                    /*** Create Master File Temp Table from Excel Template source file ***/
                    CreateTempTableAndBulkCopyData(dt2,DBconn);

                    /*****************************************************************************/
                    /*   Loop thru Excel Template File and only select the first row (Headers)   */
                    /*****************************************************************************/

                    for (int i = 0; i < 1; i++)  //Gets first row "A1:Z1" (column headers of excel spreadsheet)
                    {
                        // y=3 is static and must not be changed. This sets Partner_org_PK,Partner_ID,Partner_Name as key columns to perform SQL JOIN on
                        for (int y = 3; y < dth.Columns.Count; y++)
                        {
                            field = dth.Rows[0][y].ToString();
                            vars[0] = field;
                           UpdateMasterFileTable(DBconn, vars, dth); // Performs an update to the Partner Profile Table via a join on the Master File Temp table 

                        }
                        UpdateValidation(DBconn, dth, tabName);
                    }
                    ds.Clear();
                    ds2.Clear();
                    dt.Clear();
                    dth.Clear();

                    cn.Close();
                    DBconn.Close();
                    Dts.TaskResult = (int)ScriptResults.Success;
                }
                MessageBox.Show("Processed......" + tabName.ToString());
                System.Threading.Thread.Sleep(3000);
            }
 }
     /**************************************************************************************************/
     /*   Creates Master File Global Temp Table ###MasterFileTemp and Bulk Copy Excel data into it   */
     /**************************************************************************************************/
    public static void CreateTempTableAndBulkCopyData(DataTable dt2, SqlConnection DBconn)
    {
        DataTable MasterFileTemp = dt2;



        string createTempTable = "CREATE TABLE ##MasterFileTemp(";
        foreach (DataColumn dc in MasterFileTemp.Columns)
        {
            createTempTable += MasterFileTemp.Rows[0][dc] + " Varchar(255),";
        }
        createTempTable = createTempTable.Remove(createTempTable.Length - 1);   //remove trailing, unecessary comma
        createTempTable += ")"; // cap-off with ")" to complete the CREATE ##TEMP TABLE DDL
        {

            //Create temp table command
            SqlCommand command = new SqlCommand(createTempTable, DBconn);
            DBconn.Open();
            command.ExecuteNonQuery();
            MessageBox.Show(createTempTable.ToString());

            //Copy the DataTable to SQL Server Table using SqlBulkCopy
            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(DBconn.ConnectionString);
            {
                sqlBulkCopy.DestinationTableName = "##MasterFileTemp";

                foreach (DataColumn dc in MasterFileTemp.Columns)
                {
                    for (int j = 1; j < MasterFileTemp.Rows.Count - 1; j++)
                    {
                        if (MasterFileTemp.Rows[0][dc].ToString() == "Notes")
                        {

                            int pos = dc.Ordinal;
                            string dataText = MasterFileTemp.Rows[j][pos].ToString().Replace("\r\n", String.Empty);
                            MasterFileTemp.Rows[j][pos] = dataText;
                            MasterFileTemp.AcceptChanges();
                            //MessageBox.Show(MasterFileTemp.Rows[j][pos].ToString());

                        }
                    }
                }
                sqlBulkCopy.WriteToServer(MasterFileTemp);

            }
        }
    }
    /**************************************************************************************************/
    /*   Performs an up to the Partner Profile Table via a UPDATE join on the Master File Temp table  */
    /**************************************************************************************************/
    public void UpdateMasterFileTable(SqlConnection DBconn, string[] vars, DataTable dth)
        {
         string[] upvariable = vars;
         string sqlUpate = "UPDATE [dbo].[xstg_Partner_Profile]" +
         " SET [dbo].[xstg_Partner_Profile]." + upvariable[0] + "=##MasterFileTemp." + upvariable[0] +
         " FROM ##MasterFileTemp" +
         " WHERE [dbo].[xstg_Partner_Profile].Partner_Id= ##MasterFileTemp." + dth.Rows[0][1].ToString() +
         " AND [dbo].[xstg_Partner_Profile].Partner_Name= ##MasterFileTemp." + dth.Rows[0][2].ToString();
          SqlCommand command = new SqlCommand(sqlUpate, DBconn);
           command.ExecuteNonQuery();
          MessageBox.Show(sqlUpate.ToString());

         }
    /**************************************************************************************************/
    /*   Performs the update validation against production 90100 UI Report and creates Excel mismatch */
    /*   output for each worksheet tab in masterfileupdate template
    /**************************************************************************************************/
    public void UpdateValidation(SqlConnection DBconn, DataTable dth, string tabName)
    {
        string SelectSQL;
        string SelectFields=null;
        for (int x = 3; x < dth.Columns.Count; x++)
        {
           SelectFields += " p2." +dth.Rows[0][x]+ ", ";
        }
        SelectFields = SelectFields.Remove(SelectFields.Length - 2);  //remove trailing comma
        SelectSQL = "SELECT p2.Partner_ID, p2.Partner_Name,";
        SelectSQL += SelectFields;
        string ValidationSQL = " FROM (select * from dbo.Partner_Profile_CMS_Settings) p1" +
                               " FULL OUTER JOIN (Select * from dbo.xstg_Partner_Profile) p2" +
                               " ON p1.Partner_ID = p2.Partner_ID and p1.Partner_Name=p2.Partner_Name" +
                               " WHERE";

        SelectSQL += ValidationSQL; //Append select statement as one
        string ValidationSQLWhere=null;
        for (int y = 3; y < dth.Columns.Count; y++) //loop through data columns to get columns for update - mismatch. This is dynamic and makes up the Where clause
        {
            ValidationSQLWhere += " (P1."+dth.Rows[0][y]+" <> p2."+dth.Rows[0][y]+") OR";
        }
        ValidationSQLWhere = ValidationSQLWhere.Remove(ValidationSQLWhere.Length - 2);  //Remove "OR" two characters from the select statement where clause
        SelectSQL += ValidationSQLWhere; //Append Where clause string to main Select string
        MessageBox.Show("Validating... " + tabName); //Display entire string

        //Build SQL connection to run mismatch query, passing in SELECT statement above
        SqlDataAdapter VSAdapter = new SqlDataAdapter(SelectSQL, DBconn);
        // This Dataset contains Vaildate data
        DataSet validateDs = new DataSet();
        VSAdapter.Fill(validateDs, "VSheet1");
        DataTable validationTemp = validateDs.Tables["VSheet1"];
        String currentDate = DateTime.Now.ToString("MMddyyyy");
        String outputStatus="Validation is 100% accurate";


        /* Set up Excel workbook instance and loop through each worksheet avaialble file output */
        /****************************************************************************************/
        Excel.Application oXL = new Excel.ApplicationClass();
        oXL.DisplayAlerts = false;
        Excel.Workbooks oWBs = oXL.Workbooks;
        Excel.Workbook oWB = null;
        Excel.Worksheet oSheet;
        tabName = tabName.Remove(tabName.Length-1);  //remove training '$' from mismatch worksheets

        /* If the mismatch output file does not exist, create mismatch file and write out first worksheet */
        if (!File.Exists(Dts.Variables["User::MismatchOutputFile"].Value.ToString()))
        {


            oWB = oXL.Workbooks.Add(Missing.Value);
            // Get the active sheet 
            oSheet = (Excel.Worksheet)oWB.Worksheets.get_Item(1);
            oSheet.Name = tabName;

            int rowCount = 0;
            if (validationTemp.Rows.Count >= 1)
            {
                foreach (DataRow dr in validationTemp.Rows)
                {
                    rowCount += 1;
                    for (int i = 1; i < validationTemp.Columns.Count + 1; i++)
                    {
                        // Add the header time first only
                        if (rowCount == 2)
                        {
                            oSheet.Cells[1, i] = validationTemp.Columns[i - 1].ColumnName;
                        }                           
                        oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
                    }
                }
            }
            else
            {
               // MessageBox.Show("Validation is 100% accurate");
                oSheet.Cells[rowCount, 2] = outputStatus.ToString();
            }
            oWB.SaveAs(Dts.Variables["User::MismatchOutputFile"].Value.ToString(), Excel.XlFileFormat.xlWorkbookNormal,
               Missing.Value, Missing.Value, Missing.Value, Missing.Value,
               Excel.XlSaveAsAccessMode.xlShared,
               Missing.Value, Missing.Value, Missing.Value,
               Missing.Value, Missing.Value);
         }
        else /* If mismatch file already exists, loop thru and append additional worksheets */
        {
           System.Threading.Thread.Sleep(1000);
            try
            {
                oXL.DisplayAlerts = false;
                Excel.Sheets xlSheets = null;
                oWB = oXL.Workbooks.Open(Dts.Variables["User::MismatchOutputFile"].Value.ToString(),
                         Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                         Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                         Missing.Value, Missing.Value, Missing.Value,
                         Missing.Value, Missing.Value, Missing.Value);
                xlSheets = (Excel.Sheets)oWB.Sheets;
                oSheet = (Excel.Worksheet)xlSheets.Add(Type.Missing, xlSheets[1], Type.Missing, Type.Missing);
                oSheet.Name = tabName;
                int rowCount = 0;
                if (validationTemp.Rows.Count > 1)
                {
                    foreach (DataRow dr in validationTemp.Rows)
                    {
                        rowCount += 1;
                        for (int i = 1; i < validationTemp.Columns.Count + 1; i++)
                        {
                            // Add the header time first only
                            if (rowCount == 2)
                            {
                                oSheet.Cells[1, i] = validationTemp.Columns[i - 1].ColumnName;
                            }
                            oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
                        }
                    }
                 }else
                        {
                           // MessageBox.Show("Validation is 100% accurate");
                            oSheet.Cells[rowCount, 2] = outputStatus.ToString();
                        }   
                oWB.SaveAs(Dts.Variables["User::MismatchOutputFile"].Value.ToString(), Excel.XlFileFormat.xlWorkbookNormal,
                   Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                   Excel.XlSaveAsAccessMode.xlExclusive,
                   Missing.Value, Missing.Value, Missing.Value,
                   Missing.Value, Missing.Value);
                oWB.Close(true, Dts.Variables["User::MismatchOutputFile"].Value.ToString(), Type.Missing);
                oWBs.Close();
                oXL.Quit();
                Marshal.ReleaseComObject(oXL);
            }
            finally
            {

            }
        }

    }

}

}


Risposta popolare

Problema risolto. Ho identificato che le dimensioni delle colonne erano troppo piccole per consentire l'operazione sqlBlkCopy. Cambia la dimensione da varchar (255) a varchar (2000)



Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow