title: Check excel file contents before import
body: <p>I am using VS2005 C# ASP.NET and SQL Server 2005.</p>
  
  <p>I have a function which import Excel data. I have met a situation when the data inside is inappropriate, it will bring down the SQL Server DB.</p>
  
  <p>E.g. SELECT [Username], [Password] from [userlist$] -> If an excel spreadsheet contains more than [Username] in one column or values below the columns, the server will crash.</p>
  
  <p>E.G. <img src="https://i.stack.imgur.com/D8tju.png" alt="enter image description here"></p>
  
  <p>May I know how can I have a statement to check for this file error before uploading? Prefer <code>if</code> <code>else</code> statements for checking.</p>
  
  <p>Thank you for any help or examples given.</p>
  
  <p>Below is my code snippet for the excel upload:</p>
  
  <pre><code>if (FileImport.HasFile)
      {
  
          // Get the name of the Excel spreadsheet to upload. 
          string strFileName = Server.HtmlEncode(FileImport.FileName);
  
          // Get the extension of the Excel spreadsheet. 
          string strExtension = Path.GetExtension(strFileName);
  
          // Validate the file extension. 
          if (strExtension == ".xls" || strExtension == ".xlsx")
          {
                  // Generate the file name to save. 
                  string strUploadFileName = "C:/Documents and Settings/user01/My Documents/Visual Studio 2005/WebSites/MajorProject/UploadFiles/" + DateTime.Now.ToString("yyyyMMddHHmmss") + strExtension;
  
                  // Save the Excel spreadsheet on server. 
                  FileImport.SaveAs(strUploadFileName);
  
                  // Create Connection to Excel Workbook
                  string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strUploadFileName + ";Extended Properties=Excel 8.0;";
  
                  using (OleDbConnection connection =
                               new OleDbConnection(connStr))
                  {
                      string selectStmt = string.Format("Select [COLUMNS]  FROM [userlist$]");
  
                      OleDbCommand command = new OleDbCommand(selectStmt, connection);
  
                      connection.Open();
                      Console.WriteLine("Connection Opened");
                      // Create DbDataReader to Data Worksheet
                      using (DbDataReader dr = command.ExecuteReader())
                      {
                          // SQL Server Connection String
                          string sqlConnectionString = "Data Source=&lt;datasource&gt;";
  
                          // Bulk Copy to SQL Server
                          using (SqlBulkCopy bulkCopy =
                                     new SqlBulkCopy(sqlConnectionString))
                          {
                              bulkCopy.DestinationTableName = "UserDB";
                              bulkCopy.WriteToServer(dr);
                              return;
                          }
                      }
                  }
  </code></pre>