IMPORT EXCEL TO DATASET AND TO SQL TABLE

Question

I am having an excel file with multiple number of sheets to read to dataset.

I want to read each of the sheets to dataset tables, and to create the records for table to table values reference, and finally need to add all these to a n sql datatable.

Is there anyone to help me out?

Mohan Gopi · Answer

Hi Jani Try this; For Reading Excel to Dataset: Call this below function and it will return Dataset. DataSet dsExcelValue= ImportExceltoDataset(ExcelFilePath) public static DataSet ImportExceltoDataset( string fileName) { Microsoft.Office.Interop.Excel. Application oXL; Workbook oWB; Worksheet oSheet; Range oRng; // creat a Application object oXL = new Microsoft.Office.Interop.Excel. Application (); try { // get WorkBook object oWB = oXL.Workbooks.Open(fileName Missing .Value Missing .Value Missing .Value Missing .Value Missing .Value Missing .Value Missing .Value Missing .Value Missing .Value Missing .Value Missing .Value Missing .Value Missing .Value Missing .Value); // get WorkSheet object int M = oXL.Worksheets.Count; for ( int sheetnum = 1; sheetnum { oSheet = (Microsoft.Office.Interop.Excel. Worksheet )oWB.Sheets[sheetnum]; for ( int j = oSheet.UsedRange.Cells.Columns.Count + 10; j > 0; j--) { if (j > oSheet.UsedRange.Cells.Columns.Count) { if ( string .IsNullOrEmpty(((Microsoft.Office.Interop.Excel. Range )oSheet.Cells[1 j]).Text.ToString())) { ((Microsoft.Office.Interop.Excel. Range )oSheet.Cells[1 j]).EntireColumn.Delete( null ); } } else { if ( string .IsNullOrEmpty(((Microsoft.Office.Interop.Excel. Range )oSheet.Cells[1 j]).Text.ToString())) { int IsDeleteCunt = 1; for ( int i = 1; i { if ( string .IsNullOrEmpty(((Microsoft.Office.Interop.Excel. Range )oSheet.Cells[i j]).Text.ToString())) { IsDeleteCunt++; } else { break ; } } if (IsDeleteCunt == oSheet.UsedRange.Cells.Rows.Count) { ((Microsoft.Office.Interop.Excel. Range )oSheet.Cells[1 j]).EntireColumn.Delete( null ); } } } } } DataSet ds = new DataSet (); string WrkshtName =  ; for ( int N = 1; N { oSheet = (Microsoft.Office.Interop.Excel. Worksheet )oWB.Sheets[N]; WrkshtName = oSheet.Name; System.Data. DataTable dt = new System.Data. DataTable (WrkshtName); ds.Tables.Add(dt); DataRow dr; StringBuilder sb = new StringBuilder (); int jValue = oSheet.UsedRange.Cells.Columns.Count; int iValue = oSheet.UsedRange.Cells.Rows.Count; int EmptyColumnCount = 1; // get data columns for ( int j = 1; j { oRng = (Microsoft.Office.Interop.Excel. Range )oSheet.Cells[1 j]; string strValue = oRng.Text.ToString(); if (strValue.Trim() ==  ) { EmptyColumnCount++; } dt.Columns.Add(strValue System. Type .GetType( System.String )); } if (EmptyColumnCount >= jValue) { ds.Tables.Remove(WrkshtName); } else { //get data in cell for ( int i = 2; i { dr = ds.Tables[WrkshtName].NewRow(); int k = 0; EmptyColumnCount = 1; for ( int j = 1; j { oRng = (Microsoft.Office.Interop.Excel. Range )oSheet.Cells[i j]; (( Range )oSheet.Cells[1 j]).EntireColumn.AutoFit(); string strValue = oRng.Text.ToString(); if (strValue.Trim() ==  ) { EmptyColumnCount++; } dr[k] = strValue; k++; } if (EmptyColumnCount { ds.Tables[WrkshtName].Rows.Add(dr); } } } } return ds; } catch ( Exception ex) { return null ; } } Form DataSet to Sql Database: Pass your output Dataset as Datatable by Datatable to this function it will create table in Database and Dump DataTable Data into sql Table. public void LoadImportData( DataTable dtCol string TblName) { SqlConnection con =new SqlConnection( connection string ) ; // Your Sql Server Connection. if (CreateTableInDatabase(dtCol TblName)) { SqlBulkCopy bc = new SqlBulkCopy (con.ConnectionString SqlBulkCopyOptions .TableLock); bc.DestinationTableName = TblName; bc.BatchSize = dtCol.Rows.Count; con.Open(); bc.WriteToServer(dtCol); bc.Close(); con.Close(); } } public bool CreateTableInDatabase( DataTable dtSchemaTable string tableName) { int i = 0; string ctStr =  ; int EmptyHearder = 0; ctStr = CREATE TABLE [dbo].[ + tableName + ](
 ; foreach ( DataColumn ddt in dtSchemaTable.Columns) { i++; string MyString =  ; if (ddt.ColumnName.Trim() !=  ) { MyString = ddt.ColumnName; } else { EmptyHearder++; MyString = EmptyHeader_ + EmptyHearder.ToString(); } if (! Char .IsLetter(MyString.Trim()[0])) { MyString = MyString.Remove(0 1); } MyString = MyString.Replace( '('  '_' ); MyString = MyString.Replace( )   ); MyString = RemoveSpecialCharacters(MyString); ddt.ColumnName = MyString; string dataty = ddt.DataType.Name.ToString().Trim().ToLower(); if (dataty == string || dataty ==  || dataty == object ) { ctStr +=  [ + ddt.ColumnName.Trim() + ][nvarchar](MAX) NULL ; } else if (dataty.Substring(0 3) == int ) { bool isVar = false ; for ( int j = 0; j { string dt = dtSchemaTable.Rows[j][ddt].ToString(); if (dt.Length > 10) { isVar = true ; } } if (isVar == false ) { ctStr +=  [ + ddt.ColumnName.Trim() + ][bigint] NULL ; } else { ctStr +=  [ + ddt.ColumnName.Trim() + ][nvarchar](MAX) NULL ; } } else if (dataty.Substring(0 3) == byt ) { ctStr +=  [ + ddt.ColumnName.Trim() + ][image] NULL ; } else if (dataty == real || dataty == double ) { ctStr +=  [ + ddt.ColumnName.Trim() + ][float] NULL ; } else if (dataty == boolean ) { ctStr +=  [ + ddt.ColumnName.Trim() + ][Bit] NULL ; } else if (dataty == date ) { ctStr +=  [ + ddt.ColumnName.Trim() + ][date] NULL ; } else { ctStr +=  [ + ddt.ColumnName.Trim() + ][ + ddt.DataType.Name.Trim() + ] NULL ; } if (i { ctStr +=  ; } ctStr += 
 ; } ctStr +=  [S_No] [bigint] IDENTITY(11) NOT NULL  
  ; ctStr +=  [IconID] [Image] NULL 
 ) ;  ; SqlConnection conn = objCommonDAL.GetConnection(); SqlCommand command = conn.CreateCommand(); command.CommandText = ctStr; conn.Open(); command.ExecuteNonQuery(); conn.Close(); return true ; } public static string RemoveSpecialCharacters( string str) { StringBuilder sb = new StringBuilder (); foreach ( char c in str) { if ((c >= '0' && c '9' ) || (c >= 'A' && c 'Z' ) || (c >= 'a' && c 'z' ) || c == '_' ) { sb.Append(c); } } return sb.ToString(); } I hope this will help you. Thank's Mohan G

Jeetendra Gund · Answer

Hi.. try this code public void CreateMasterTable(byte[] ExcelFile DatabaseDetails authenticationToken string tableName) { try { if (authenticationToken != null && authenticationToken.DatabaseDetail != null) { if (null == ExcelFile) throw new Exception(); SqlConnection conn = new SqlConnection(); ConnectionString =Data Source=jeetendra; + Initial Catalog=Employee; + User id=sa; + Password=server@2010; conn.Open(); if ( IntegraObjectContext.Database.SqlQuery ( Select count(*) from INFORMATION_SCHEMA.COLUMNS where table_name=' + tableName + s'). FirstOrDefault() > 0) { IntegraObjectContext.Database.ExecuteSqlCommand(DROP TABLE  + tableName + s); } var UnCompressedFabricMasterExcelFile = ConversionHelper.GetUnCompressedData(ExcelFile); var tempPath = Path.GetTempPath(); tempPath += Excel\; if (!Directory.Exists(tempPath)) Directory.CreateDirectory(tempPath); var fileName = Guid.NewGuid(); ConversionHelper.ConvertByteDataToImage(tempPath + fileName + .xls UnCompressedFabricMasterExcelFile); var Source = tempPath + fileName + .xls; var ColumnNameTypeList = ReadExcelSheet.GetColumnNamesFromExcelFile(Source tableName); #region Working For Fields and AlterTableCommand string alterTableCommand = null; // ALTER TABLE [dbo].[ + tableName + s] ADD CONSTRAINT [ + tableName + _ + tableName + Id] DEFAULT (newid()) FOR [ + tableName + Id] ; string fields = null; //[ + tableName + Id] UNIQUEIDENTIFIER NOT NULL; foreach (var colNameWithType in ColumnNameTypeList) { if (string.IsNullOrEmpty(colNameWithType.ColumnName) || string.IsNullOrEmpty(colNameWithType.ColumnType)) continue; string strDefault = null; if ((colNameWithType.ColumnType.Contains(nvarchar) || colNameWithType.ColumnType.Contains(nchar) || colNameWithType.ColumnType.Equals(ntext) || colNameWithType.ColumnType.Equals(text) || colNameWithType.ColumnType.Contains(varchar))) { strDefault = 'NA'; } var allowNull = NULL; if (!colNameWithType.AllowNull) allowNull = NOT NULL; fields += [ + colNameWithType.ColumnName + ]  + colNameWithType.ColumnType +   + allowNull + ; if (!string.IsNullOrEmpty(strDefault)) alterTableCommand +=  ALTER TABLE [dbo].[ + tableName + s] ADD CONSTRAINT [ + tableName + _ + colNameWithType.ColumnName + ] DEFAULT ( + strDefault + ) FOR [ + colNameWithType.ColumnName + ] ; } if (fields != null) fields = fields.Remove(fields.Length - 1); fields = fields + ); //fields = fields +  CONSTRAINT [PK_ + tableName + ] PRIMARY KEY CLUSTERED ( [ + tableName + // Id] ASC )WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF IGNORE_DUP_KEY = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] ; #endregion Working For Fields and AlterTableCommand var createTableQuery = CREATE TABLE [ + tableName + s] ( + fields + alterTableCommand; SqlCommand cmd = new SqlCommand(ConnectionString ); cmd.CommandType = CommandType.Text; cmd.CommandText = createTableQuery ; var nCount = cmd.ExecuteNonQuery(); conn.Close(); } return; } catch (Exception exception) { CLog.ExceptionError(exception); throw; } } Regards Jeetendra

Suthish Nair · Answer

If your thread got resolved please Accept the thread as Answer that helped you.

Sanjeeb Lenka · Answer

HI Refer to this link it may help you. there is a example matching your requirement. http://www.aspsnippets.com/Articles/Read-and-Import-Excel-Sheet-into-SQL-Server-Database-in-ASP.Net.aspx