Importing Data from Access (or text) to Excel 1

[RamziSaab]

I have the following:

- text file (tab delimited) which has data (generated separately) with around 55 million rows and 5 columns (results from another program)

- i need to be able to query the first column (has identifier) and retrieve the data to do calculations in excel (around 55 at a time)...i.e. approx one million sims

what is the quickest method to access and query this data and spit it out in excel

Thanks

 

[SkipVought]

55 Million rows???

There's only capacity for 65,536 rows on a sheet in Excel.

???

Skip,

_[red][/red]

 

[SkipVought]

oRS.Open "SELECT ColumnAFieldName FROM " & strFilename & " Where SomeField='" & SomeValue & "'", oConn, 3, 1, 1

Skip,

_[red][/red]

 

[SkipVought]

I meant...

oRS.Open "SELECT * FROM " & strFilename & " Where ColumnAFieldName='" & SomeValue & "'", oConn, 3, 1, 1

Skip,

_[red][/red]

 

[RamziSaab]

thanks, but it seems quicker just creating an object and reading it in vba: e.g.

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("c:\myfile.txt", ForReading, 0)

 

[SkipVought]

All kinds of ways to skin a cat.

Skip,

_[red][/red]

 

[RamziSaab]

true, but surely not all ways are equal

 

[SkipVought]

1) You're only getting the first 65,536 rows. What about the other 54,934,464 rows that you never "see"???

2) the While is unnecessary since you're not doing a row-by-row traverse of your recordset

'Now actually open the text file and import into Excel
oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1
[s]While Not oRS.EOF[/s]
Sheets.Add
ActiveSheet.Range("A1").CopyFromRecordset oRS, 65536
[s]Wend[/s]

Skip,

_[red][/red]

 

[RamziSaab]

what i do currently is read line by line the text file and output the results (i.e. i built a simple query that reads the file and shoots out the data needed - around 55 rows at a time)