Programmatically load CSV file into Excel Worksheet (Delphi 7)

Question

I have a large amount of data to insert into an worksheet of an existing Excel workbook. The Excel workbook will have other worksheets containing calculations and a pivot tables. The data may have as many as 60,000 rows and more than 30 columns. This solution must work for both Excel 2003 and Excel 2007.

Using the Excel OLE object is way too slow so we are attempting to load the data from a CSV file. We have come up with a method to load the data by placing the data onto the clipboard and then pasting it into the worksheet. I feel this is a quite a kludge. Is there another way to programmatically load a CSV file into a worksheet? Or perhaps a different solution altogether?

---

Update: We got slammed with another task before we could fully investigate the answers. We should be able to get back to this in a couple of weeks. I'll be sure to update again when we get back to this task.

Thanks for all of the answers to date!

Answer 1

XLSReadWrite is a component that can read and write excel files from Delphi. It's fast and it has support for Excel 2003 and 2007. You can create new excel files as well as open existing ones and add/modify them.

Also you do not need to have Excel installed to be able to use it.

See http://www.axolot.com/components/xlsrwii20.htm

Answer 2

Any chance you can drop the requirement for this to work with Office 2003? I would have recommended the Open XML Format SDK. It lets you bind managed code assemblies to spreadsheet documents that can handle events such as Open or Close, and read and write to cells in the document, among other things. Alternatively, you can use it to manipulate XSLX documents from an application. Quite slick, actually.

Since this won't work for you, how about writing a macro that pulls in the CSV file when the spreadsheet is loaded?

Answer 3

you can load the csv into listview or usin OLEDB provider to load it on DBGrid, then export it into xls file format using TMxExport component from Max Components:

Max Components

Answer 4

Have you tried linking the csv file directly into the worksheet.

Go to Data -> Import External Data -> Import Data change the file type to 'Text Files'

You can then refresh the worksheet when the csv is update.

NOTE: I have not done this with the volume of data you have indicated, so YMMV

Answer 5

Actually there is a way that is quite fast, pretty old tech (nowdays) but is probably the fastest.

It's ADO or for earlier versions DAO (note not ADO.NET)

You can read a CSV file using ADO and the JET Engine to get the data into a ADO recordset, then an Excel Range Object has a CopyFromRecordSet method that will copy (very fast) from the ADO (or DAO) recordset.

http://msdn.microsoft.com/en-us/library/aa165427(office.10).aspx

Answer 6

You can try to use Tab Separated Values instead of CSV - than you just paste this into Excel :)