How to transfer Excel data sheet to an Access Table

[rameshika]

Hi, I am writing a small program, where I need to transfer Excel Data into Access tables using C#.Net.If anyone has attempted this please be kind enough to help me on this. Rameshi

[pmarfleet]

Use the JET OLE DB Provider to connect to your spreadsheet. Loop through the data in the spreadsheet and run an INSERT query on your Access database for each row of data.

Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush

[MickCurley]

You could also fill a datatable from from the excel spreadsheet (ExcelDT) and also fill another datatable from the Access Table (AccessDT). Then just use a foreach loop on the excel datatable:

     foreach (DataRow row in ExcelDT.Rows)
     {          
        DataRow newrow = AccessDT.NewRow();

        newrow.ItemArray = row.ItemArray;           
        
        AccessDT.Rows.Add(newrow);

     }

Finally call update on the AccessDT and Bingo your done :) Hope this helps.

A craft is an enemy if not well learned.

[pmarfleet]

This method adds unnecessary overhead to the process. It is more efficient to use a DataReader to access the Excel data and run direct INSERT statements against the Access database.

Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush