Reading a large delimited text file using ADO

Dear Experts,

We are developing a method to read the content of a large text file which is the exported result of a database query. The intention is to rearrange content in a suitable way to be able to create lists and analysis reports in Excel. We cannot open the text file directly in Excel since it is too large so we would like to investigate if we can use ADO to create a connection and retrieve data by using SQL queries. We have some experience of using that technique together with Access and xls data sources.

The file structure looks like this:

------------------------------------
|Field1      |Field2     |Field 3  |
------------------------------------
|Value1      |Value2     |Value3   |
|Value1      |Value2     |Value3   |
|Value1      |Value2     |Value3   |

To define | as a delimiter, we use a schema.ini file, which is a cofinguration file, according to http://msdn2.microsoft.com/en-us/library/ms974559.aspx

Since the text file must be slightly modified in other ways, we are forced to change it in some way. It would, however, be nice if we didnt have to change every line by removing the first and last character. Even though this is a very fast process (we guess, any comments?), there could be over 100 000 lines to process, why it would take some time to do this.

The code to connect to the text file is attached. Any advice would be greatly appreciated. Are we going in the right direction?

Regards,

StellanStart Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

'Database connection
    Dim cnDatabaseConnection As ADODB.Connection
    Set cnDatabaseConnection = CreateObject("ADODB.Connection")
 
    'Recordset
    Dim rsRecordset As ADODB.Recordset
    Set rsRecordset = CreateObject("ADODB.Recordset")
 
    'Open the database connection
    cnDatabaseConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\" & ";Extended Properties='text;HDR=Yes;FMT=Delimited'"
 
    cnDatabaseConnection.Open