StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhile Uploading the Excel sheet table to SQL server table $ is not uploading with numbers but upload with text only
text
Body
copied!<p>i want to upload the excel sheets data to the sql sever 2008, all my data i.e numbers and Alphabets from my excel sheet table are going to upload in the Database table but $ sign is not going to upload there as i have to use a currency as a column, $sign going to upload with text data but not with numbers.....pls let me knw abt this problem</p> <p>this is my code</p> <pre><code>using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.Xml; using System.Configuration; using System.IO; using log4net.Config; using log4net; namespace ExcelUpload { class Program { static ILog log = LogManager.GetLogger(typeof(Program)); //Variable declarations public static string strSqlConnection, strExcelDataQry, strSqlTable, strExcelFilePath, sexcelconnectionstring; public static int intRows; static void Main(string[] args) { #region GET PARAMS FROM CONFIG FILE strSqlConnection = ConfigurationManager.ConnectionStrings["SQLConnection"].ToString(); strExcelFilePath = ConfigurationManager.AppSettings["ExcelFileName"].ToString(); string[] sqlSheets = ConfigurationManager.AppSettings["Sheets"].Split(','); #endregion #region SET CONNECTIONS sexcelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelFilePath + ";Extended Properties='Excel 12.0 xml;HDR=YES;'"; SqlConnection Sqlconn = new SqlConnection(strSqlConnection); SqlBulkCopy bulkcopy = new SqlBulkCopy(strSqlConnection); OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring); #endregion XmlConfigurator.Configure(); log4net.ThreadContext.Properties["Context"] = "ExcelUpload"; try { log.Info("Started Execution of ExcelUpload Process"); foreach (string sqlSheetName in sqlSheets) { #region GET PARAMS FROM CONFIG FILE BASED ON CURRENT SHEET strExcelDataQry = "select " + ConfigurationManager.AppSettings[sqlSheetName + "ColumnNames"] + " from [" + sqlSheetName + "$]"; strSqlTable = ConfigurationManager.AppSettings[sqlSheetName + "Table"]; intRows = Convert.ToInt32(ConfigurationManager.AppSettings[sqlSheetName + "RowsToExclude"].ToString()); #endregion #region TRUNCATE TABLE SqlCommand SqlCommand = new SqlCommand("TRUNCATE TABLE " + strSqlTable, Sqlconn); Sqlconn.Open(); SqlCommand.ExecuteNonQuery(); Sqlconn.Close(); log.Info("Table " + strSqlTable + " Truncated Successfully"); #endregion #region BULK COPY DATA log.Info("Started processing the sheet " + sqlSheetName); OleDbCommand oledbcmd = new OleDbCommand(strExcelDataQry, oledbconn); oledbconn.Open(); DataSet ds = new DataSet(); using (OleDbDataAdapter adapter = new OleDbDataAdapter(oledbcmd)) { adapter.Fill(ds); } for (int iRow = 0; iRow < intRows; iRow++) { ds.Tables[0].Rows[iRow].Delete(); } ds.Tables[0].AcceptChanges(); foreach (DataRow dr in ds.Tables[0].Rows) { foreach (DataColumn col in ds.Tables[0].Columns) { if (col.DataType == typeof(System.String)) { dr[col] = dr[col].ToString().Trim(); } } } bulkcopy.DestinationTableName = strSqlTable; bulkcopy.WriteToServer(ds.Tables[0]); oledbconn.Close(); log.Info("Sheet " + sqlSheetName + " successfully loaded to the table " + strSqlTable); #endregion } log.Info("ExcelUpload Process completed successfully"); } catch (System.Exception ex) { log.Info("Error while processing :- " + ex.Message); } finally { Sqlconn.Close(); oledbconn.Close(); } } } } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload