Loading Large Excel Files

Question

I'm using the OLEDB connection string for this and it appears that whenever I try to load an Excel file (I'm using .NET) it takes an incredibly loooong time. This causes a timeout and most files greater than a certain size can't be loaded into the program. Is there any other way or pattern I can use to make everything work faster (The data loading I mean)?

I don't have the exact code but I just used a simple connection and a dataTable.Fill()

Answer 1

you could try doing the insert like

insert into mytable(col1,col2,col3)
select 1,2,3
union all
select 4,5,6

on the chance you're using sql server 2008 or better you could also do something like this:

INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)

this generally is much more efficient, though i'm not sure if you mean that the drag is on reading the file or inserting individual records.

Answer 2

Settled for a slightly faster approach using DataReader. Looks like there's not a lot I could do to improve the speed.