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

Question

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

Ramesh Palanivel · 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 Parvathi · Answer

Thanks for your help.I will try.

Ramesh Palanivel · Answer

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 Parvathi · Answer

Thank you show much.How to store that particular data to table.Please help me.

Ramesh Palanivel · Answer

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 Parvathi · Answer

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