Excel sheet as Database, Why Does a apostrophe appear?

[pitchblack408]

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 '[phone]

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

[phone]

'[phone]
'[phone]


thecode:

private void WriteToLeadTable()
{
 
            // Establish a connection to the data source.
            System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(
                "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strSampleFolder +
                "LeadConsistencyScorecard.xls;Extended Properties=Excel 8.0;");
            objConn.Open();
 
            // Add record
            System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();
            objCmd.Connection = objConn;
 
            /* The fallowing two fields will not be updated because we do not know what is up
            * And it is up to the channel manager to know
            * 
            * Assigned_Date, Assigned_To_Position, Assigned_To_Name,"
            * + "Status,
             * + "', '" + Fields.Assigned_Date
             * + "', '" + Fields.Assigned_To_Position    + "', '" + Fields.Assigned_To_Name  + "', '" + Fields.Status
             * 
            */
 
 
            //Write to LeadTable
            objCmd.CommandText = "INSERT into [Leads$] (Lead_ID, Event_ID, Lead_Comment_ID, Event_Name,"
              + " Received_Date, Lead_Grade,"
              + " Company_Name,"    +" Street_1,"  +" Street_2," +" Street_3,"        + " City,"
              + " State,"           +" Zip_Code,"  +" Country,"  +" Company_Phone,"   + " Website,"
              + " First_Name,"      +" Last_Name," +" Job_Title," + " Contact_Phone," + " Phone_Ext,"
              + " Alternate_Phone," +" Email,"     +" Industry_Vertical)"
              + " values ('" + Fields.Lead_ID   + "', '" + Fields.Event_ID          + "', '" + Fields.Lead_Comment_ID
              + "', '" + Fields.Event_Name      + "', '" + Fields.Rainmaker_Date    + "', '" + Fields.Lead_Grade        
              + "', '" + Fields.Company_Name    + "', '" + Fields.Street_1          + "', '" + Fields.Street_2 
              + "', '" + Fields.Street_3        + "', '" + Fields.City              + "', '" + Fields.State
              + "', '" + Fields.Zip_Code        + "', '" + Fields.Country           + "', '" + Fields.Company_Phone
              + "', '" + Fields.Website         + "', '" + Fields.First_Name        + "', '" + Fields.Last_Name
              + "', '" + Fields.Job_Title       + "', '" + Fields.Contact_Phone     + "', '" + Fields.Phone_Ext
              + "', '" + Fields.Alternate_Phone + "', '" + Fields.Email             + "', '" + Fields.Industry_Vertical + "')";
 
              objCmd.ExecuteNonQuery();
 
            // Close the connection.
              objConn.Close(); 
        }
 
 

[pitchblack408]

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" <[e-mail] > 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(excelFil eName);
¤ string strConnectionSt ring = @"Provider=Micr osoft.Jet.OLEDB .4.0;Data
¤ Source="
¤ + System.IO.Path. GetDirectoryNam e(excelFileName ) + @"\" +
¤ System.IO.Path. GetFileName(exc elFileName)
¤ + @";Extended Properties='Exc el 8.0;HDR=YES'";
¤
¤ using (System.Data.Ol eDb.OleDbConnec tion objConn = new
¤ System.Data.Ole Db.OleDbConnect ion(strConnecti onString))
¤ using (System.Data.Ol eDb.OleDbComman d cmd = new
¤ System.Data.Ole Db.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.ExecuteNonQ uery();
¤ 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.ExecuteNonQ uery();
¤ }
¤ }
¤ }
¤ }
¤
¤ 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

[pitchblack408]

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

[klatuvarata]

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

Wala!