I'm having trouble with connecting to a odbc resource because the file is too large (65536 lines). I need to have access to the whole file, does anyone know a solution for this

Regards
Theo

Have you considered importing the data in MS Access?

Another way of approaching the issue is to create a pivot in excel based on the data.Before dumping the data in the spreadsheet Excel gives a window asking if you would like a pivot table create. Take this option and this way you would have the data in a summarized version and when you double click on any number you can get the supporting data

"Theo" wrote ...

I'm having trouble with connecting to a odbc resource because the file is too large (65536 lines). I need to have access to the whole file, does anyone know a solution for this?


Are you sure? In my experience, practically speaking one tends to work
with a subset of data or aggregated data. Even people like auditors
and actuaries who are interested in the whole data set can't digest
65K+ rows of data in one chunk (well, actuaries actually might <g).

Let's say you could get the whole lot on one worksheet, what would be
you next move? Use autofilter, write some formulas using SUM or
VLOOKUP? Consider whether any of these operations could be done
*before* the data gets to your workbook. Remember, you can later go
back to the data source and get a different data set as required.

If you truly need the whole lot in one big table then Excel isn't the
tool for you.

--

@Onedaywhen:
I have to read the whole file because the database which I'm connecting
to has a rocky odbc driver (hyperfile / windev). When I use the
filtering before I read the data I get error after error unfortunatly.
When I read the whole file, then I'm able to filter etc...

Another question:
Is it possible to read the data from that file and convert it to let say
a txt file without reading the whole file into a worksheet?

Another problem with that odbc driver is that it only works with
Excel...

Regards,
Theo


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Theo van Velthuijsen wrote ...

I have to read the whole file because the database which I'm connecting
to has a rocky odbc driver (hyperfile / windev). When I use the
filtering before I read the data I get error after error unfortunatly.
When I read the whole file, then I'm able to filter etc

Theo van Velthuijsen wrote ...

I have to read the whole file because the database which I'm connecting
to has a rocky odbc driver (hyperfile / windev). When I use the
filtering before I read the data I get error after error unfortunatly.
When I read the whole file, then I'm able to filter etc

Are you saying that e.g.

SELECT Col1 FROM MyTable WHERE Col2=5

causes errors but e.g.

SELECT Col1 FROM MyTable

works fine? If so, you do have problems.

I assume you are using MS Query (Data, Import External Data). If you
used ADO in VBA code, you may do some filtering there e.g.

Dim oConn As Object
Dim oRs As Object
Dim strSql As String

Set oConn = CreateObject("ADODB.Connection")

With oConn
.CursorLocation = 3 ' adUseClient
.ConnectionString = CONN_STRING
.Open
End With

strSql = "SELECT Col1" & _
" FROM MyTable"

Set oRs = oConn.Execute(strSql)
oRs.Filter = "Col2=5"
Sheet1.Range("A2").CopyFromRecordset oRs

Is it possible to read the data from that file and convert it to let say
a txt file without reading the whole file into a worksheet?

Your idea to use an intermediate data source is a good one but that
would also take some VBA coding. Rather than a text file, personally
I'd use a Jet database...

Another problem with that odbc driver is that it only works with
Excel

....ah, I see. If you have MS Access, you may be able to link the
tables and successfully query the linked tables. Definitely worth a
try. You don't need MS Access UI to do this if you can write the
equivalent VBA, but it's a lot easier with MS Access especially if you
don't know whether it will work!

So, how's your VBA abilities?