How to read data from excel file and store data into db.

Revathi Parvathi

How to read data from excel file and store data into MSAccess database using C# dot net in windows application.My excel sheet has different template format.How to store these type of excel file format to MsAccess.Please Help me. Thanks in advance. image link for excel file .i wants to store some of the column of these file's data to db.Is it possible? https://i.stack.imgur.com/wKODY.jpg

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ramesh Palanivel9y ago
Accepted answer
Hi Revathi,

create two tables in your msaccess. First one comanydetailstable and second one is sellingdetails.

1. create primary key for both table.
2. In selling details table , create one fk which will going to have companyid (ex : CompanyId(FK) map company table primary key for this column.
3. just save all the single values in company table and save multiple values in selling table with mapping of company id.

To read Excel file using excel object, pls refer the below URL

http://csharp.net-informations.com/excel/csharp-read-excel.htm

To read excel file using Excel data reader plugin , please refer the below URL,

Note : excel data reader is thrid party plugin.

https://exceldatareader.codeplex.com/

If you have any query , let me know. pls add clear image, it will better to create sample database for you
Revathi Parvathi9y ago
Thanks for your help.I will try.
Ramesh Palanivel9y ago
Hi revathi,

I saw your excel file. To read this file , it will have more manual work. There are multiple invoices with same sheet. Its difficult to read. To avoid your manual work , read the excel cells for first invoice. write the code for reading your first invoice. Then remove first invoice rows and make the second invoice as first invoice.(Note : columns should be match same as first invoice). If you doing this way, you can reduce your manual work and easily upload all your invoice.
Revathi Parvathi9y ago
Thank you show much.How to store that particular data to table.Please help me.
Ramesh Palanivel9y ago
Hi Revathi,

As per your requirement, please find the below table design,
Table 1 : Customer Details
CustomerId(PK),
Name,
Address

Table 2 : InvoiceDetails
InvoiceID(PK)
Order No
Division
Customer ID(FK)(Forgien key mapping from Cusomter Details Table)
Order Date
Terms Code
Tax Schedule
Salesperson
Ship Via
Ship To Address Code
Customer PO

Table 3: InvoiceProductDetails
Id(PK)
InvoiceID(FK)(Forgien key mapping from InvoiceDetails Table)
Item Code
Item Description
Quantity Ordered
Unit Price
Revathi Parvathi9y ago
Thanks for your reply.I will try.For clear image please use the below-mentioned link.

https://i.stack.imgur.com/wKODY.jpg

I want store below-mentioned column to table from excel.Please help me.

Order No
Division
Customer ID
Order Date
Terms Code
Tax Schedule
Salesperson
Ship Via
Ship To Address Code C
customer PO
Item Code
Item Description
Quantity Ordered
Unit Price