ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.[Solved] Reading Excel using OleDB ignoring alphanumeric values using C#<p>Hi <a class="username" rel="userNK">userNK</a>,</p> <p>Add <strong>IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text</strong> to the Extended Properties in the connection string would fix the problem.</p> <p>Check this example. Now please take its reference and correct your code.</p> <p><strong><span style="text-decoration: underline;">HTML</span></strong></p> <pre class="brush: html">&lt;asp:FileUpload ID=&#34;FileUpload1&#34; runat=&#34;server&#34; /&gt; &lt;asp:Button ID=&#34;btnUpload&#34; runat=&#34;server&#34; Text=&#34;Upload&#34; OnClick=&#34;btnUpload_Click&#34; /&gt; &lt;br /&gt;&lt;br /&gt; &lt;asp:GridView ID=&#34;GridView1&#34; runat=&#34;server&#34;&gt; &lt;/asp:GridView&gt;</pre> <p><strong><span style="text-decoration: underline;">Namespaces</span></strong></p> <p><strong>C#</strong></p> <pre class="brush: csharp">using System; using System.Data; using System.Data.OleDb; using System.IO;</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Imports System Imports System.Data Imports System.Data.OleDb Imports System.IO</pre> <p><strong><span style="text-decoration: underline;">Code</span></strong></p> <p><strong>C#</strong></p> <pre class="brush: csharp">protected void Upload(object sender, EventArgs e) { if (FileUpload1.HasFile) { string filename = Path.GetFileName(FileUpload1.PostedFile.FileName); string extension = Path.GetExtension(FileUpload1.PostedFile.FileName); string filePath = Path.Combine(Server.MapPath(&#34;~/UploadedFiles/TemporaryFiles&#34;), filename); FileUpload1.SaveAs(filePath); string excelConnectionString = &#34;&#34;; switch (extension) { case &#34;.xls&#34;: //Excel 97-03 excelConnectionString = &#34;Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=&#39;Excel 8.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text&#39;&#34;; break; case &#34;.xlsx&#34;: //Excel 07 excelConnectionString = &#34;Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=&#39;Excel 12.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text&#39;&#34;; break; } excelConnectionString = String.Format(excelConnectionString, filePath); OleDbConnection excelConnection = new OleDbConnection(excelConnectionString); OleDbCommand cmdExcel = new OleDbCommand(); OleDbDataAdapter oleDA = new OleDbDataAdapter(); DataTable dt = new DataTable(); cmdExcel.Connection = excelConnection; excelConnection.Open(); DataTable dtExcelSchema; dtExcelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string SheetName = dtExcelSchema.Rows[0][&#34;TABLE_NAME&#34;].ToString(); excelConnection.Close(); excelConnection.Open(); cmdExcel.CommandText = &#34;SELECT * From [&#34; + SheetName + &#34;]&#34;; oleDA.SelectCommand = cmdExcel; oleDA.Fill(dt); excelConnection.Close(); GridView1.DataSource = dt; GridView1.DataBind(); } }</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Protected Sub Upload(ByVal sender As Object, ByVal e As EventArgs) If FileUpload1.HasFile Then Dim filename As String = Path.GetFileName(FileUpload1.PostedFile.FileName) Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName) Dim filePath As String = Path.Combine(Server.MapPath(&#34;~/UploadedFiles/TemporaryFiles&#34;), filename) FileUpload1.SaveAs(filePath) Dim excelConnectionString As String = &#34;&#34; Select Case extension Case &#34;.xls&#34; excelConnectionString = &#34;Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=&#39;Excel 8.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text&#39;&#34; Case &#34;.xlsx&#34; excelConnectionString = &#34;Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=&#39;Excel 12.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text&#39;&#34; End Select excelConnectionString = String.Format(excelConnectionString, filePath) Dim excelConnection As OleDbConnection = New OleDbConnection(excelConnectionString) Dim cmdExcel As OleDbCommand = New OleDbCommand() Dim oleDA As OleDbDataAdapter = New OleDbDataAdapter() Dim dt As DataTable = New DataTable() cmdExcel.Connection = excelConnection excelConnection.Open() Dim dtExcelSchema As DataTable dtExcelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) Dim SheetName As String = dtExcelSchema.Rows(0)(&#34;TABLE_NAME&#34;).ToString() excelConnection.Close() excelConnection.Open() cmdExcel.CommandText = &#34;SELECT * From [&#34; &amp; SheetName &amp; &#34;]&#34; oleDA.SelectCommand = cmdExcel oleDA.Fill(dt) excelConnection.Close() GridView1.DataSource = dt GridView1.DataBind() End If End Sub</pre> <p><strong><span style="text-decoration: underline;">Screenshot</span></strong></p> <p><strong><span style="text-decoration: underline;"><img src="https://i.imgur.com/oxLdd5t.jpg" alt="" width="465" height="296" /></span></strong></p>https://www.aspforums.net:443/Threads/158077/Solved-Reading-Excel-using-OleDB-ignoring-alphanumeric-values-using-C/https://www.aspforums.net:443/Threads/158077/Solved-Reading-Excel-using-OleDB-ignoring-alphanumeric-values-using-C/Thu, 08 Aug 2019 00:59:21 GMT[Solved] Reading Excel using OleDB ignoring alphanumeric values using C#<p>Hi <a class="username" rel="userNK"> userNK</a>,</p> <p>I have checked the provided code. Its working at my end.</p> <pre class="brush: csharp">string filename = &#34;Test.xls&#34;; string Message = string.Empty; try { string filePath = Path.Combine(Server.MapPath(&#34;~/UploadedFiles/TemporaryFiles&#34;), filename); string extension = Path.GetExtension(filename); string excelConnectionString = &#34;&#34;; switch (extension) { case &#34;.xls&#34;: //Excel 97-03 excelConnectionString = &#34;Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=&#39;Excel 8.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text&#39;&#34;; break; case &#34;.xlsx&#34;: //Excel 07 excelConnectionString = &#34;Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=&#39;Excel 12.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text&#39;&#34;; break; } excelConnectionString = String.Format(excelConnectionString, filePath); OleDbConnection excelConnection = new OleDbConnection(excelConnectionString); OleDbCommand cmdExcel = new OleDbCommand(); OleDbDataAdapter oleDA = new OleDbDataAdapter(); DataTable dt = new DataTable(); cmdExcel.Connection = excelConnection; excelConnection.Open(); DataTable dtExcelSchema; dtExcelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string SheetName = dtExcelSchema.Rows[0][&#34;TABLE_NAME&#34;].ToString(); excelConnection.Close(); excelConnection.Open(); cmdExcel.CommandText = &#34;SELECT * From [&#34; + SheetName + &#34;]&#34;; oleDA.SelectCommand = cmdExcel; oleDA.Fill(dt); excelConnection.Close(); }</pre> <p><strong><span style="text-decoration: underline;">Screenshots</span></strong></p> <p><strong>The Excel</strong></p> <p><strong><img src="https://i.imgur.com/VhufgBU.jpg" alt="" width="472" height="322" /></strong></p> <p><strong>The Dataset Visualizer </strong></p> <p><strong><img src="https://i.imgur.com/FguMzqr.jpg" alt="" width="472" height="253" /></strong></p>https://www.aspforums.net:443/Threads/158077/Solved-Reading-Excel-using-OleDB-ignoring-alphanumeric-values-using-C/https://www.aspforums.net:443/Threads/158077/Solved-Reading-Excel-using-OleDB-ignoring-alphanumeric-values-using-C/Fri, 09 Aug 2019 05:39:02 GMT