суббота, 21 июля 2012 г.

Problem with inserting in .xlsx (Excel 2007) long string (more than 255 characters) by Microsoft.ACE.OLEDB.12.0

Once I developed tool that allows export data from MSSQL to different formats (.csv,.xls,.mdb...). The tool takes any sql, connection strings (source and destination) and return data in needed format. The tool uses MS SSIS API. But  recently I have come across with issue when I tried to export data into .xlsx (Excel 2007). Lets look on export to excel. First step is create excel file and then loading data.
Look at the code which creates xlsx file:

 using(DbConnection connection=new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\temp\\MyExcel.xlsx;Extended Properties=\"Excel 12.0 XML;HDR=Yes\""))
            {
                DbCommand command = connection.CreateCommand();
                command.CommandText = "CREATE TABLE [DATA] ([ID] Long,[NAME] LongText,[COMMENTS] LongText)";
                connection.Open();
                command.ExecuteNonQuery();
            }
Next we need load data into the file. For this I used SSIS package that fills data into excel file, but here I want to illustrate a issue on easy example. Look at the next code:

   using(DbConnection connection=new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\temp\\MyExcel.xlsx;Extended Properties=\"Excel 12.0 XML;HDR=Yes\""))
            {
                DbCommand command = connection.CreateCommand();
                command.CommandText = "insert into [DATA] ([ID],[NAME],[COMMENTS]) values (1,'Hello world', 'Some information about the world')";
                connection.Open();
                command.ExecuteNonQuery();
            }
If you execute the code everythink will be OK. But if you want to insert instead of "Some information about the world" a string longer than 255 characters you will get error "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data." This problem appears when you work with xlsx (Excel 2007). If you do the same steps with xls (Excel 97-2003) everythink works good!
The problem is because I used two different connections. One connection for create xlsx file and another one for insert data into the file.

For solving the issue I found next workaround. I did not close first connection which creates xlsx file while loading data are not completed.

Finallly code:


 DbConnection connection1 = null;
            String connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\temp\\MyExcel.xlsx;Extended Properties=\"Excel 12.0 XML;HDR=Yes\"";
            try
            {
                connection1 = new OleDbConnection(connectionString);
                DbCommand command = connection1.CreateCommand();
                command.CommandText = "CREATE TABLE [DATA]([ID] Long,[NAME] LongText,[COMMENTS] LongText)";
                connection1.Open();
                command.ExecuteNonQuery();
                using (DbConnection connection2 = new OleDbConnection(connectionString))
                {
                    command = connection2.CreateCommand();
                    command.CommandText = "insert into [DATA] ([ID],[NAME],[COMMENTS]) values (2,'Hello world2', 'Some information about the world.')";
                    connection2.Open();
                    command.ExecuteNonQuery();
                }
            }
            finally
            {
                if (connection1 != null)
                    connection1.Close();
            }