Tag Archives: excel

ASP.Net Bulk Insert Excel Data using SqlBulkCopy

This code sample is for a Excel Import programmatically into SQL Server database.

Important points to note.

  • Use excel data in a single sheet.
  • The excel file format should match the table schema.

Excel format:

Id Name Description CreatedAt CreatedBy ModifiedAt ModifiedBy Enabled Deleted

.aspx page

<asp:FileUpload ID="FileUpload1" runat="server" />

<asp:Button ID="Button1" runat="server" Text="Import" CssClass="btn btn-warning" OnClick="ImportExcel" />

Code Behind File

protected void ImportExcel(object sender, EventArgs e)
            if (FileUpload1.HasFile)
                // SQL Server Connection String
                string sqlConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["flexi_stocky"].ConnectionString;

                // Bulk Copy to SQL Server 
                SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);
                    string path = string.Concat(Server.MapPath("~/uploads/" + FileUpload1.FileName));

                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
                    OleDbConnection connection = new OleDbConnection();
                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    bulkInsert.DestinationTableName = "your_sqlTableName";
                    //Show success
                catch (Exception ex)
                    //Show error

                //File not set
Leave a comment

Posted by on April 12, 2015 in ASP.Net


Tags: ,

Import Excel Data into MySQL with PHP


To import Excel data, first you need to have a Excel reader. It should be accurate enough to interpret Excel data as expected. There ‘s a good old Excel reader.

Download PHPExcelReader.

In the downloaded archive, you only need Excel directory with files including and reader.php.

Just extract it where your web server can access.

Next place your excel file or just create one with some dummy data. Make sure this file is readble by the web server.

Finally create your php script to connect with database, read Excel file and insert data into db.

<?php require_once 'Excel/reader.php'; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('CP1251');

$conn = mysql_connect("localhost","root","");

for ($x = 2; $x <= count($data->sheets[0]["cells"]); $x++) {
$first = $data->sheets[0]["cells"][$x][1];
$middle = $data->sheets[0]["cells"][$x][2];
$last = $data->sheets[0]["cells"][$x][3];
$sql = "INSERT INTO mytable (First,Middle,Last)
VALUES ('$first','$middle','$last')";
echo $sql."\n";


Even your 1cent donation is appreciated.
Donate Button with Credit Cards


Posted by on February 19, 2014 in PHP



Generate Excel Data in PHP

Create Excel

Create Excel

header("Content-Type: application/xls");
header("Content-disposition: attachment; filename=data.xls");
echo 'Id' . "\t" . 'Item' . "\t" . 'Qty' . "\n";
echo '001' . "\t" . 'Compaq 610 laptop' . "\t" . '02' . "\n";
1 Comment

Posted by on December 21, 2013 in PHP