Remove Line break and BulkCopy xlsx file

[mba91000]

Hi,

Please how to remove Line break and BulkCopy xlsx file in an SQL Database Table ?

The lines 3 and 4 are not imported into the sql database.

Best regards

Code: Select all

class Program1
    {


        static void Main(string[] args)
        {
           
            try
            {

                InsertDataIntoSQLServerTb(GetDtFromXLSFile("TEST.xlsx"));

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadLine();
            }
        }


        private static DataTable GetDtFromXLSFile(string file_path)
        {
            const int desiredSize = 7;//Number of culums in the ataTable
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(file_path);

            Worksheet sheet = workbook.Worksheets["Feuil1"];

            //Find and Replace lines break
            CellRange[] ranges = sheet.FindAllString("\r\n",false, false);
            foreach (CellRange range in ranges)
            {
                range.Text = range.Text.Replace("\r\n", " ");
            }

            //workbook.SaveToFile(file_path, FileFormat.Version2010);

            DataTable dt = sheet.ExportDataTable();

            //Remove excess columns
            while (dt.Columns.Count > desiredSize)
            {
                dt.Columns.RemoveAt(desiredSize);
            }

            return dt;
        }


        //CREATE TABLE MyTable(SOMMAIRE nvarchar(max) NULL,LIBELLE nvarchar(max) NULL,ARRET nvarchar(max) NULL,JURIDICTION nvarchar(max) NULL,
        //COMMENTAIRE nvarchar(max) NULL,LIBELLEFIN nvarchar(max) NULL,DATEARRET nvarchar(max) NULL)
        static void InsertDataIntoSQLServerTb(DataTable fileData)
        {
            try
            {
                using (SqlConnection dbConnection = Tools.Connection())//Connexion to sql server Db
                {
                    using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                    {
                        s.DestinationTableName = "MyTable";
                        foreach (var column in fileData.Columns)
                            s.ColumnMappings.Add(column.ToString(), column.ToString());
                        s.WriteToServer(fileData);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
            }
        }


    }



[rachel.lei]

Hello,

Thanks for your inquiry.
Kindly note that the line break symbol should be "\n" instead of "\r\n". And I debugged and found that the datatable exported by our Spire.XLS contains the line 3 and line 4, please see the attached screenshot. Meanwhile, I tried to write the exported datatable into a new Excel file using our product, and found its content is consistent with the source file, the following is the code I used. Is the data in the data table you exported complete? If so, I am afraid the issue that the data has not been completely imported into the database is related to your database operation.

Code: Select all

        static void Main(string[] args)
        {
            try
            {
                //Get the data table from Excel
                DataTable dt = GetDtFromXLSFile("TEST.xlsx");

                //Write the data table to a new Excel file
                Workbook book = new Workbook();
                Worksheet sheet = book.Worksheets[0];
                sheet.InsertDataTable(dt, true, 1, 1);
                for (int i = 1; i <= sheet.LastColumn; i++)
                {
                    sheet.AutoFitColumn(i);
                }
                book.SaveToFile("insertTableToExcel.xlsx", ExcelVersion.Version2013);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadLine();
            }
        }

        private static DataTable GetDtFromXLSFile(string file_path)
        {
            const int desiredSize = 7;//Number of culums in the ataTable
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(file_path);

            Worksheet sheet = workbook.Worksheets["Feuil1"];

            //Find and Replace lines break
            //The lines break symbol should be "\n" instead of "\r\n"
            CellRange[] ranges = sheet.FindAllString("\n", false, false);
            foreach (CellRange range in ranges)
            {
                range.Text = range.Text.Replace("\n", " ");
            }

            DataTable dt = sheet.ExportDataTable();

            //Remove excess columns
            while (dt.Columns.Count > desiredSize)
            {
                dt.Columns.RemoveAt(desiredSize);
            }

            return dt;
        }

Sincerely,
Rachel
E-iceblue support team

[mba91000]

Thanks for your help Rachel,
Indeed, there was an error on the code of the line break.
Best regards

[rachel.lei]

Hello,

Thanks for your response. Feel free to contact us if there are any questions.

Sincerely,
Rachel
E-iceblue support team