Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Friday, August 12, 2016 7:26 AM
Hello. I am using OleDB connection (ACE 12.0) to insert rows to Excel xlsx sheet. I have a few rows where last field is over 255 chars long. I am using data reader to pull SQL Server data and these results are added to Excel. I have two scenarios, both using the same connection and OleDBCommand, the only difference is in the second option I add a field in front to indicate record type field. Both routines define create table statement and values + parameters in a similar way (I use LongVarChar ole db Type) and yet in the second case I get an error about trying to insert too many characters. In the first case the whole text is inserted without problems, the longest string is 360 characters. When I remove the rows with the long comments both cases work fine. I wonder if anyone else experienced the same and what could be the reason? I did read other people's posts, but I never found a case where one would be able to make it work in any scenario. I wouldn't be surprised if both cases failed, but I cannot understand why the first case works and the second doesn't even though the code is almost the same.
All replies (5)
Sunday, August 14, 2016 5:50 PM âś…Answered
Hi,
the "table name" of an excel sheet has a "$" sign at the end and as long you put the name in brackets it is ok.
The data conflict error has a different reason: the Access Excel driver tries to detect the data type of the columns. And even if you create it as a MEMO data type it will determine it as a WCHAR(255) column, if there is no data or only some data with a length less then 256 characters.
That is especially true for the Excel Xml driver, as it seems to ignore even a create done just beforehand. The older xls format is a little more tolerant, but will react the same if there the sheet exists and there is no data or only short text.
My test code I've used for simplicity:
internal static void InsertExcelWithMemoCreateOnly()
{
InsertExcelWithMemo(true);
}
internal static void InsertExcelWithMemoTruncateFirst()
{
InsertExcelWithMemo(false, true);
}
internal static void InsertExcelWithMemo(bool createOnly = false, bool truncateFirstRow = false)
{
string tableName = "MemoTable1";
int memoTextSize = 4096;
string[] schemaRestrictions = new string[] { null, null, tableName + "$", null };
OleDbType memoParameterType = OleDbType.LongVarWChar;
int memoParameterSize = 4096;
// HDR=Yes;TypeGuessRows=0 doesn't help if sheet is empty
// XLSX ignores the CREATE TABLE data type
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TEMP\MemoTable.xlsx;Extended Properties='Excel 12.0 Xml;Hdr=Yes;TypeGuessRows=0;'";
// Works as long the connection is pooled
// otherwise th same if there is some data with less than 256
//string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TEMP\MemoTable.xls;Extended Properties='Excel 8.0;';";
//string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TEMP\Mappe1.xls;Extended Properties='Excel 8.0;';";
try
{
using (var connection = new OleDbConnection(connectionString))
{
connection.Open();
int id = 1;
StringBuilder memoBuilder = new StringBuilder(memoTextSize);
for (int index = 0; index < memoTextSize; index++)
memoBuilder.Append((char)('A' + index % 26));
var tablesTable = connection.GetSchema("Tables", schemaRestrictions);
if (tablesTable.Rows.Count == 0)
{
var createCommand = new OleDbCommand("CREATE TABLE [" + tableName + "] ("
+ "IdColumn int, "
+ "NameColumn TEXT(255), "
+ "MemoColumn LONGTEXT); ", // MEMO
connection);
createCommand.ExecuteNonQuery();
// uncomment to start with a row less < 256
if (truncateFirstRow)
{
memoBuilder.Length = 10; // If the data inserted is shorter you still get a TEXT(255)
}
}
else
{
var columnsTable = connection.GetSchema("Columns", schemaRestrictions);
foreach (DataRow columnRow in columnsTable.Select("", "ORDINAL_POSITION"))
{
Console.WriteLine("{0} {1} {2} ({3})",
columnRow["ORDINAL_POSITION"],
columnRow["COLUMN_NAME"],
Enum.GetName(typeof(OleDbType),
(int)columnRow["DATA_TYPE"]), columnRow["CHARACTER_MAXIMUM_LENGTH"]);
}
var idCommand = new OleDbCommand("SELECT MAX(IdColumn) FROM [" + tableName + "$];", connection);
object idValue = idCommand.ExecuteScalar();
if (idValue != DBNull.Value)
id = Convert.ToInt32(idValue) + 1;
}
if (createOnly)
{
return;
}
var insertCommand = new OleDbCommand(
"INSERT INTO [" + tableName + "$] (IdColumn, NameColumn, MemoColumn) VALUES (@IdColumn, @NameColumn, @MemoColumn);",
//"INSERT INTO [" + tableName + "$] (MemoColumn) VALUES (@MemoColumn);",
connection);
insertCommand.Parameters.Add("@IdColumn", OleDbType.Integer).Value = id;
insertCommand.Parameters.Add("@NameColumn", OleDbType.VarWChar, 255).Value = String.Format("Name {0}", id);
var memoParameter = insertCommand.Parameters.Add("@MemoColumn", memoParameterType, memoParameterSize);
memoParameter.Value = memoBuilder.ToString();
insertCommand.ExecuteNonQuery();
Console.WriteLine("Inserted Id: {0}", id);
}
}
catch (OleDbException ex)
{
Console.WriteLine("Exception: {0}", ex.Message);
}
}
Sometimes it is necessary to stop and restart the execution, to see the error. The different connection strings can be changed to test a different provider / engine.
To avoid the error you should insert a long text (more than 255 characters) initially. Another solution would be using excel automation or the Open Xml or a similar SDK.
Regards, Elmar
Friday, August 12, 2016 8:56 AM
Hi,
please show the CREATE TABLE statement and the parameter definition for the command you are using, as your description isn't clear for me.
Note: Access always uses Unicode (wide character) and a memo column should be specified as OleDbType.LongVarWChar, see INFO: OleDbType Enumeration vs. Microsoft Access Data Types.
Regards, Elmar
Friday, August 12, 2016 1:19 PM
It certainly would help to see your code so we can see what you are doing. The problem with Excel data access has always been that the ISAM driver makes a best guess as to the data type of column by scanning the rows of data present in that column. By default the number of rows scanned is the first eight, although this can be changed through the TypeGuessRows Registry entry for the ACE Provider/Excel ISAM driver.
255 characters is the cutoff for a Text field. Unless the column is determined to be "Memo", based upon the scan results, any characters beyond 255 will be truncated when using a SELECT statement. Upon INSERT you will see an exception if the column is determined to be Text.
Paul ~~~~ Microsoft MVP (Visual Basic)
Friday, August 12, 2016 7:37 PM
Thanks for responses.
Connection string for both cases:
String strConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strOutputExcelFileName + ";Extended Properties='Excel 12.0 Xml'";
oledbconConnection = new OleDbConnection(strConnString);
oledbconConnection.Open();
Working example:
1.Calling method:
sqlcomReadCommand.CommandText = custOutOptions.SELECTCommand;sqldrSQLReader = sqlcomReadCommand.ExecuteReader();
WriteExcelOutput(strOutputFileName, sqldrSQLReader);
2.Within method:
a) building column string:
private void WriteExcelOutput(String strExcelFileName, SqlDataReader sqldrReader)
{strExcelFileName = Path.ChangeExtension(strExcelFileName, custOutOptions.strExcelExtension);
if (boolConnectToSource(strExcelFileName))
{
StringBuilder sbColumnString = new StringBuilder(256);
StringBuilder sbColumnDefinition = new StringBuilder(256);
SortedDictionary<Int32, custColumn> sdictColumns = sdictGetOutputCols();
sbColumnString.Append("(");
sbColumnDefinition.Append("(");
String strCurrCol;
foreach (custColumn custcolColumn in sdictColumns.Values)
{
if (!custcolColumn.boolIsRecordType && !custcolColumn.boolIsRowID)
{
custcolColumn.intOutReaderPosition = sqldrReader.GetOrdinal(custcolColumn.strName);
}
else if (custcolColumn.boolIsRecordType)
{
custcolColumn.intOutReaderPosition = sqldrReader.GetOrdinal("_rtpe_");
}
strCurrCol = custcolColumn.strName;
sbColumnDefinition.Append("[");
sbColumnDefinition.Append(strCurrCol);
listParamLengths.Add(custcolColumn.intLength);
if (custcolColumn.intLength > 255)
{
sbColumnDefinition.Append("] MEMO,");
}
else
{
sbColumnDefinition.Append("] TEXT,");
}
sbColumnString.Append("[");
sbColumnString.Append(strCurrCol);
sbColumnString.Append("],");
}
sbColumnString.Replace(',', ')', sbColumnString.Length - 1, 1);
sbColumnDefinition.Replace(',', ')', sbColumnDefinition.Length - 1, 1);
b) Writing to excel (depending on extension) - both odbc and oledb will work, but I am concerned about oledb because it is used later as well:
try
{
if (custOutOptions.strExcelExtension == "xls")
{
OdbcCommand odbcCmd = new OdbcCommand();
odbcCmd.Connection = odbcconnConnection;
odbcCmd.Parameters.Clear();
odbcCmd.CommandText = "CREATE TABLE [" + custOutOptions.ExcelSheetName + "]" + sbColumnDefinition;
odbcCmd.ExecuteNonQuery();
StringBuilder sbValues = new StringBuilder(256);
for (Int32 i = 0; i < sdictColumns.Count; i++)
{
sbValues.Append("?,");
odbcCmd.Parameters.Add(new OdbcParameter("Par" + i.ToString(),OdbcType.Text,listParamLengths[i]));
}
sbValues.Replace(',', ')', sbValues.Length - 1, 1);
odbcCmd.CommandText = "INSERT INTO [" + custOutOptions.ExcelSheetName + "]" + sbColumnString + " VALUES(" + sbValues;
Int32 intCurrParamIndex = 0;
Int32 intRowNum = 1;
while (sqldrReader.Read())
{
foreach (custColumn custcolColumn in sdictColumns.Values)
{ if (custcolColumn.boolIsRecordType) {
odbcCmd.Parameters[intCurrParamIndex].Value = sqldrReader["_rtpe_"];
}
if (custcolColumn.boolIsRowID)
{
odbcCmd.Parameters[intCurrParamIndex].Value = intRowNum++;
}
else
{
odbcCmd.Parameters[intCurrParamIndex].Value = sqldrReader[custcolColumn.intOutReaderPosition];
}
intCurrParamIndex++;
}
intCurrParamIndex = 0;
odbcCmd.ExecuteNonQuery();
}
}
else
{
OleDbCommand oledbCmd = new OleDbCommand();
oledbCmd.Connection = oledbconConnection;
oledbCmd.Parameters.Clear();
oledbCmd.CommandText = "CREATE TABLE [" + custOutOptions.ExcelSheetName + "]" + sbColumnDefinition;
oledbCmd.ExecuteNonQuery();
StringBuilder sbValues = new StringBuilder(256);
for (Int32 i = 0; i < sdictColumns.Count; i++)
{
sbValues.Append("?,");
oledbCmd.Parameters.Add(new OleDbParameter("Par"+i.ToString(),OleDbType.LongVarChar,listParamLengths[i]));
}
sbValues.Replace(',', ')', sbValues.Length - 1, 1);
oledbCmd.CommandText = "INSERT INTO [" + custOutOptions.ExcelSheetName + "]" + sbColumnString + " VALUES(" + sbValues;
Int32 intCurrParamIndex = 0;
Int32 intRowNum = 1;
//Object[] vals;//***
while (sqldrReader.Read())
{
foreach (custColumn custcolColumn in sdictColumns.Values)
{
if (custcolColumn.boolIsRecordType)
{
oledbCmd.Parameters[intCurrParamIndex].Value = sqldrReader["_rtpe_"];
}
else if (custcolColumn.boolIsRowID)
{
oledbCmd.Parameters[intCurrParamIndex].Value = intRowNum++;
}
else
{
oledbCmd.Parameters[intCurrParamIndex].Value = sqldrReader[custcolColumn.intOutReaderPosition];
}
intCurrParamIndex++;
}
intCurrParamIndex = 0;
oledbCmd.ExecuteNonQuery();
}
}
sbFilesCreated.AppendLine(strExcelFileName);
}
And now the part that won't work unless I specify length of parameters to be 255:
1.Initializing SQL data reader:
sqlcomReadCommand.CommandText = "set nocount on; select case when [" + custOutOptions.strRecordTypeField + "] = '" + custOutOptions.Insert + "' then 'Insert' else 'Delete' end as [" +
strCompareTableRecTypeField + "] ," + sbCols + " from " +
strFinalTB + " where [" + custOutOptions.strRecordTypeField + "] IN ('" + custOutOptions.Insert + "','" + custOutOptions.Delete + "')";
DR = sqlcomReadCommand.ExecuteReader();
StringBuilder sbColumnString = new StringBuilder(256);
StringBuilder sbColumnDefinition = new StringBuilder(256);
sbColumnString.Append("(");
sbColumnDefinition.Append("(");
2.Building table creation string:
for (Int32 i = 0; i < DR.FieldCount; i++)
{
strCurrCol = DR.GetName(i);
sbColumnDefinition.Append("[");
sbColumnDefinition.Append(strCurrCol);
if (i > 0 && listParamLengths[i-1] > 255)
{
sbColumnDefinition.Append("] MEMO,");
}
else
{
sbColumnDefinition.Append("] TEXT,");
}
sbColumnString.Append("[");
sbColumnString.Append(strCurrCol);
sbColumnString.Append("],");
}
sbColumnString.Replace(',', ')', sbColumnString.Length - 1, 1);
sbColumnDefinition.Replace(',', ')', sbColumnDefinition.Length - 1, 1);
3.Building parameter insert query - please note I have added 255 as the max parameter size - if I went over this value the next block will fail for text longer than 255 character.
OleDbCommand oledbCmd = new OleDbCommand();
oledbCmd.Connection = oledbconConnection;
oledbCmd.Parameters.Clear();
oledbCmd.CommandText = "CREATE TABLE [InsertsDeletes]" + sbColumnDefinition;
oledbCmd.ExecuteNonQuery();
StringBuilder sbValues = new StringBuilder(256);
for (Int32 i = 0; i < DR.FieldCount; i++)
{
sbValues.Append("?,");
oledbCmd.Parameters.Add(new OleDbParameter("Par" + i.ToString(),OleDbType.LongVarWChar,255));
}
sbValues.Replace(',', ')', sbValues.Length - 1, 1);
oledbCmd.CommandText = "INSERT INTO [InsertsDeletes$]" + sbColumnString + " VALUES(" + sbValues;
4.And this is where we fail:
Int32 intCurrParamIndex = 0;
Object[] vals;
if (File.Exists(strOldFile) && File.Exists(strNewFile))
{
while (DR.Read())
{
if (custInsDelRange.intRows == 1000000)
{
oledbCmd.CommandText = "create table [InsertsDeletes" + custInsDelRange.introws.Count + "]" + sbColumnDefinition;
oledbCmd.ExecuteNonQuery();
oledbCmd.CommandText = "insert into [InsertsDeletes" + custInsDelRange.introws.Count + "$]" + sbColumnString + " values(" + sbValues;
custInsDelRange.introws.Add(0);
}
vals = new Object[DR.FieldCount];
DR.GetValues(vals);
foreach (Object o in vals)
{
oledbCmd.Parameters[intCurrParamIndex].Value = o;
intCurrParamIndex++;
}
intCurrParamIndex = 0;
oledbCmd.ExecuteNonQuery();
custInsDelRange.intRows++;
}
}
DR.Close();
Maybe worth mentioning - I actually tried to use a bit of code with object array above in the WriteExcelOutput method and the method still worked.
Summary: WriteExcelOutput method with oledb worked with fields longer than 255 characters even though columns and parameters were defined in a similar fashion to where the code failed.
Saturday, August 13, 2016 8:34 PM
Found the solution after comparing all parts of code. The answer was in an unexpected place - the insert query. I removed the $ sign from the table name and it worked... Thanks to all for answers.