locked
split 30,000 character field from excel 2007 and insert into SQL 2008 RRS feed

  • Question

  • User1080785583 posted

    My problem is I need to split the string into 10,000 length fields. Believe me, this import works only because nvarchar(MAX) exists in SQL SERVER 2008. Every other field type truncates at 4k. 

     

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Office.Interop.Excel;
    using System.Data.SqlClient;
    using System.Data;
    using System.Data.OleDb;
    using System.Data.Common;
    
    
    namespace ExportExcel
    {
        class Program
        {
            static void Main(string[] args)
            {
                string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=xxx\RMISUpload.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
                DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
    
                //string ClaimsConnectionString = @"Data Source=xxx;Initial Catalog=xxx;Persist Security Info=True;User ID=xxx;Password=xxx; providerName='System.Data.SqlClient'/>";
                ExcelNotesDataSetTableAdapters.tblNotesTableAdapter _notesAdapter = new ExportExcel.ExcelNotesDataSetTableAdapters.tblNotesTableAdapter();
    
                try
                {
                    using (DbConnection connection = factory.CreateConnection())
                    {
                        connection.ConnectionString = connectionString;
    
                        using (DbCommand command = connection.CreateCommand())
                        {
                            command.CommandText = "Select Comments, [Claim Number] from [Claims$]";
                            connection.Open();
                            using (DbDataReader dr = command.ExecuteReader())
                            {
                                while (dr.Read())
                                {
                                    if (dr["Comments"].ToString().Length > 0)
                                    {
    
                                        string myNotes = dr["Comments"].ToString();
    
                                        if (myNotes.Length > 37000)
                                        {
                                            // this will fail because it is too large.
                                            // we need to split the string somehow
                                            // not working
                                            //myNotes.Split([], 10000, StringSplitOptions.RemoveEmptyEntries);
                                        }
                                        else
                                        {
                                            _notesAdapter.Insert(-1, dr["Claim Number"].ToString(), Convert.ToDateTime("1/1/2050"),
                                        "TEST", "TEST", "TEST", "TEST", false, Convert.ToDateTime("1/1/2050"), dr["Comments"].ToString(), false, false);
                                        }
    
                                        //Console.WriteLine(dr["Claim Number"].ToString() + " has a length greater than 2100 " + 
                                        // "and its length is " + dr["Comments"].ToString().Length );
                                    }
                                    //Console.WriteLine(dr["Comments"].ToString() + ","  + dr["Claim Number"].ToString());
    
                                    //Console.ReadLine();
    
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.Write(ex.Message.ToString());
                }
                Console.WriteLine("Completed.");
                Console.ReadLine();
            }
        }
    }


     

    Friday, February 26, 2010 10:52 AM

All replies

  • User2130758966 posted

    You can use the "text" data type to store as much data as you need, or ntext if you need to store unicode text:

    Friday, February 26, 2010 12:05 PM
  • User1080785583 posted

    This solution does not work. It still gets truncated even as a "text" field. Also, text in sql does not allow me to do the len function which makes my job harder in validating length of the field.

    Friday, February 26, 2010 12:17 PM
  • User2130758966 posted

    There must be some other problem then because the documentation in the link I posted states that you can store 2,147,483,647 characters in a text field.

    Friday, February 26, 2010 1:34 PM
  • User1080785583 posted

    It says 2,147,483,646 bytes, How many characters are in a byte? I think there is 1 character in a byte.. could be wrong though. varchar max seems to be capping out my field at 31737 characters. That is the largest value that is successfully imported.

    Friday, February 26, 2010 1:39 PM