Click here to Skip to main content
15,914,014 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I need to write a console application in C# reading data from XLSX and writing to SQL server using ExcelDataReader v2.1 Library .

I need to read the data in Cell D17,D18,D19 and drop to buying column in SQL.
And read Cell K17,K18,K19 and drop to /selling column in SQL
This is the SQL table query and database name is test1

SQL
SELECT TOP 1000 [id]
      ,[code]
      ,[buying]
      ,[selling]
  FROM [test1].[dbo].[Currency]


This is a code i tried but it is not using ExcelDataReader v2.1. i need help to do it with ExcelDataReader v2.1

C#
using System;
using System.IO;
using Bytescout.Spreadsheet;
using System.Data.SqlClient;

namespace ExportToSQLServer
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
               
                string connectionString = "Data Source=192.168.1.215;Initial Catalog=RECIPES2;Persist Security Info=True;User ID=sa;Password=***********";

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    
                    ExecuteQueryWithoutResult(connection, "USE test1");

                    //Load XLS document
                    using (Spreadsheet document = new Spreadsheet())
                    {
                        document.LoadFromFile("SimpleReport.xls");
                        Worksheet worksheet = document.Workbook.Worksheets[0];

                        for (int row = 0; row <= worksheet.UsedRangeRowMax; row++)
                        {
                            String insertCommand = string.Format("INSERT XlsTest VALUES('{0}','{1}')",
                           worksheet.Cell(row, 0).Value, worksheet.Cell(row, 1).Value);
                            ExecuteQueryWithoutResult(connection, insertCommand);
                        }
                    }

                    // Check the data successfully exported
                    using (SqlCommand command = new SqlCommand("SELECT * from XlsTest", connection))
                    {
                        SqlDataReader reader = command.ExecuteReader();

                        if (reader != null)
                        {
                            Console.WriteLine();
                            Console.WriteLine("Exported XLS data:");
                            Console.WriteLine();

                            while (reader.Read())
                            {
                                Console.WriteLine(String.Format("{0}  |  {1}", reader[0], reader[1]));
                            }
                        }
                    }

                    Console.WriteLine();
                    Console.WriteLine("Press any key.");
                    Console.ReadKey();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
                Console.ReadKey();
            }
        }

        static void ExecuteQueryWithoutResult(SqlConnection connection, string query)
        {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.ExecuteNonQuery();
            }
        }
    }
}


Please Help
Posted
Updated 22-Jul-13 19:52pm
v2

1 solution

Read the data from the Excel using column name and import it to a datatable in asp.net and insert the records to the SQL table.
Use separate datatable for buying and selling to avoid collisions, if you are expert use the same datatable too.

Refer this to load the Excel in the datatable.

Load GridView from Excel[^]

Here what i did is, loaded the gridview from excel u just send the data without loading it in gridview.
Instead of usinf SQL bulkcopy get each records in datatable and insert the records
 
Share this answer
 
Comments
Hawkeye101 23-Jul-13 2:54am    
@manikandan Your example is for web application, i prefer Console or windows form application example! And i dont prefer to use other libraries only the ExcelDataReader v2.1 Library. Any help?
Manikandan Sekar 23-Jul-13 3:48am    
Oh sorry. I will get back to you on this.
Hawkeye101 23-Jul-13 3:59am    
Thanks!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900