By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,110 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Excel sheet as Database, Why Does a apostrophe appear?

P: 9
Hello I am using excel as my database and when I do an insert there is an apostrophe that appears in the cell where a string was inserted. For example '(474)343-3433

It appears that the apostrophe appears after an insert is done that with a null value.
example

(424)333-3433

'(474)343-3433
'(424)343-3333


thecode:

Expand|Select|Wrap|Line Numbers
  1. private void WriteToLeadTable()
  2. {
  3.  
  4.             // Establish a connection to the data source.
  5.             System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(
  6.                 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strSampleFolder +
  7.                 "LeadConsistencyScorecard.xls;Extended Properties=Excel 8.0;");
  8.             objConn.Open();
  9.  
  10.             // Add record
  11.             System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();
  12.             objCmd.Connection = objConn;
  13.  
  14.             /* The fallowing two fields will not be updated because we do not know what is up
  15.             * And it is up to the channel manager to know
  16.             * 
  17.             * Assigned_Date, Assigned_To_Position, Assigned_To_Name,"
  18.             * + "Status,
  19.              * + "', '" + Fields.Assigned_Date
  20.              * + "', '" + Fields.Assigned_To_Position    + "', '" + Fields.Assigned_To_Name  + "', '" + Fields.Status
  21.              * 
  22.             */
  23.  
  24.  
  25.             //Write to LeadTable
  26.             objCmd.CommandText = "INSERT into [Leads$] (Lead_ID, Event_ID, Lead_Comment_ID, Event_Name,"
  27.               + " Received_Date, Lead_Grade,"
  28.               + " Company_Name,"    +" Street_1,"  +" Street_2," +" Street_3,"        + " City,"
  29.               + " State,"           +" Zip_Code,"  +" Country,"  +" Company_Phone,"   + " Website,"
  30.               + " First_Name,"      +" Last_Name," +" Job_Title," + " Contact_Phone," + " Phone_Ext,"
  31.               + " Alternate_Phone," +" Email,"     +" Industry_Vertical)"
  32.               + " values ('" + Fields.Lead_ID   + "', '" + Fields.Event_ID          + "', '" + Fields.Lead_Comment_ID
  33.               + "', '" + Fields.Event_Name      + "', '" + Fields.Rainmaker_Date    + "', '" + Fields.Lead_Grade        
  34.               + "', '" + Fields.Company_Name    + "', '" + Fields.Street_1          + "', '" + Fields.Street_2 
  35.               + "', '" + Fields.Street_3        + "', '" + Fields.City              + "', '" + Fields.State
  36.               + "', '" + Fields.Zip_Code        + "', '" + Fields.Country           + "', '" + Fields.Company_Phone
  37.               + "', '" + Fields.Website         + "', '" + Fields.First_Name        + "', '" + Fields.Last_Name
  38.               + "', '" + Fields.Job_Title       + "', '" + Fields.Contact_Phone     + "', '" + Fields.Phone_Ext
  39.               + "', '" + Fields.Alternate_Phone + "', '" + Fields.Email             + "', '" + Fields.Industry_Vertical + "')";
  40.  
  41.               objCmd.ExecuteNonQuery();
  42.  
  43.             // Close the connection.
  44.               objConn.Close(); 
  45.         }
  46.  
  47.  
Jan 9 '08 #1
Share this Question
Share on Google+
3 Replies


P: 9
Take a look here if any one cares
Paul Clement
Posted: Tue Jun 28, 2005 8:12 am
Guest
On 28 Jun 2005 03:17:13 -0700, "Sam Jost" <samjost@web.de> wrote:

I written myself some small generic class to export any dataset to an
excel spreadsheet:

public static void Export(DataSet data, String excelFileName)
{
System.IO.File.Delete(excelFileName);
string strConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source="
+ System.IO.Path.GetDirectoryName(excelFileName) + @"\" +
System.IO.Path.GetFileName(excelFileName)
+ @";Extended Properties='Excel 8.0;HDR=YES'";

using (System.Data.OleDb.OleDbConnection objConn = new
System.Data.OleDb.OleDbConnection(strConnectionStr ing))
using (System.Data.OleDb.OleDbCommand cmd = new
System.Data.OleDb.OleDbCommand("", objConn))
{
objConn.Open();
foreach (DataTable dt in data.Tables)
{
cmd.CommandText = "CREATE TABLE [" + dt.TableName + "] (";
String valueNames = "(";
Boolean first = true;
foreach (DataColumn dc in dt.Columns)
{
if (!first)
{
cmd.CommandText += ",\r\n";
valueNames += ", ";
}
cmd.CommandText += " [" + dc.ColumnName + "] NVARCHAR(100)";
valueNames += " [" + dc.ColumnName + "]";
first = false;
}
cmd.CommandText += ")";
valueNames += ")";
cmd.ExecuteNonQuery();
foreach (DataRow dr in dt.Rows)
{
String values = "(";
first = true;
foreach (DataColumn dc in dt.Columns)
{
if (!first)
values += ", ";
values += " '" + dr[dc] + "'";
first = false;
}
values += ")";
cmd.CommandText = "INSERT INTO [" + dt.TableName + "$] " +
valueNames + " VALUES " + values;
cmd.ExecuteNonQuery();
}
}
}
}

This does work quite ok for my uses, the only problem is: After export
the first character in every single cell of the excel spreadsheet is
the quotation mark '
Somehow the export does not strip the leading quotation marks from my
values - anyone can give me a hint how I do get rid of these?


That is the way the Excel ISAM driver was designed to work. It adds the apostrophe to discriminate
between text and numeric values. I don't believe it shows up in the cell, just the formula bar.


Paul
~~~~
Microsoft MVP (Visual Basic)
Back to top
Sam Jost
Posted: Tue Jun 28, 2005 8:43 am
Guest
My bad - is there any way to get rid of this 'Feature by design' so I
don't have these apostrophs in every cell?
Maybe using a different type instead of NVARCHAR for the field,
anything?

Thanks,
Sam
Jan 9 '08 #2

P: 9
I made sure that it always outputs a value. So if there in no value it will output null as the value. That gets rid of the extra charater
Jan 9 '08 #3

P: 1
It's a settings issue dealing with Lotus Notes
Go to
Tools-->Options-->Transition--> Clear the Transition Navigation keys box.

Wala!
Apr 13 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.