Hello together

Technical Question of Data Import out of an text file (*.txt)

From what variables or parameters depends (most) wheter a textfile can
be imported into excel,
without getting the error message "Excel cannot complete this task with
available resources. Choose less data or close other applications." ?
Are this maybe the amount of defined columns or the temporary memory on
the pc or .... ?

Is it possible to compress the data during the import or any other
hacks or tipps ?


Our code looks at the moment as followed (it will directly load the
data into a pivotcache to display the data in a pivot:

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = Array( _
Array("ODBC;DefaultDir=" + dataPath + ";"), _
Array("Driver={Microsoft Text Driver (*.txt;
*.csv)};DriverId=27;"))
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM " & dataFileName)
.CreatePivotTable TableDestination:="R" & pivotPageAttributes +
13 & "C1", _
TableName:="PivotTable3", _
DefaultVersion:=xlPivotTableVersion10
End With


Now, why i ask......
There are cases, where it is possible to import 2millions resultrows
into the pivot and then there are cases, where it is not possible to
import 300thousound resultrows from the text files.
I don't see why in any cases it works in other cases doesn't ...

Any ideas ?

Kind regards
Roman

this is for pivot tables - but I would imagine the pivot cache does some
preprocessing and the cause may be related:

http://support.microsoft.com/?kbid=211517
XL2000: Limits of PivotTables in Microsoft Excel 2000 (Q211517)

http://support.microsoft.com/?kbid=291061
XL2002: Limits of PivotTables in Microsoft Excel 2002 (Q291061)

--
Regards,
Tom Ogilvy


" wrote:

Hello together

Technical Question of Data Import out of an text file (*.txt)

From what variables or parameters depends (most) wheter a textfile can
be imported into excel,
without getting the error message "Excel cannot complete this task with
available resources. Choose less data or close other applications." ?
Are this maybe the amount of defined columns or the temporary memory on
the pc or .... ?

Is it possible to compress the data during the import or any other
hacks or tipps ?


Our code looks at the moment as followed (it will directly load the
data into a pivotcache to display the data in a pivot:

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = Array( _
Array("ODBC;DefaultDir=" + dataPath + ";"), _
Array("Driver={Microsoft Text Driver (*.txt;
*.csv)};DriverId=27;"))
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM " & dataFileName)
.CreatePivotTable TableDestination:="R" & pivotPageAttributes +
13 & "C1", _
TableName:="PivotTable3", _
DefaultVersion:=xlPivotTableVersion10
End With


Now, why i ask......
There are cases, where it is possible to import 2millions resultrows
into the pivot and then there are cases, where it is not possible to
import 300thousound resultrows from the text files.
I don't see why in any cases it works in other cases doesn't ...

Any ideas ?

Kind regards
Roman

Hello Tom,

Thanks for your info!
I will study the infos on the microsoft page.
Maybe there is an answer.

Regards,
Roman


Tom Ogilvy wrote:
this is for pivot tables - but I would imagine the pivot cache does some
preprocessing and the cause may be related:

http://support.microsoft.com/?kbid=211517
XL2000: Limits of PivotTables in Microsoft Excel 2000 (Q211517)

http://support.microsoft.com/?kbid=291061
XL2002: Limits of PivotTables in Microsoft Excel 2002 (Q291061)

--
Regards,
Tom Ogilvy


" wrote:

Hello together

Technical Question of Data Import out of an text file (*.txt)

From what variables or parameters depends (most) wheter a textfile can
be imported into excel,
without getting the error message "Excel cannot complete this task with
available resources. Choose less data or close other applications." ?
Are this maybe the amount of defined columns or the temporary memory on
the pc or .... ?

Is it possible to compress the data during the import or any other
hacks or tipps ?


Our code looks at the moment as followed (it will directly load the
data into a pivotcache to display the data in a pivot:

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = Array( _
Array("ODBC;DefaultDir=" + dataPath + ";"), _
Array("Driver={Microsoft Text Driver (*.txt;
*.csv)};DriverId=27;"))
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM " & dataFileName)
.CreatePivotTable TableDestination:="R" & pivotPageAttributes +
13 & "C1", _
TableName:="PivotTable3", _
DefaultVersion:=xlPivotTableVersion10
End With


Now, why i ask......
There are cases, where it is possible to import 2millions resultrows
into the pivot and then there are cases, where it is not possible to
import 300thousound resultrows from the text files.
I don't see why in any cases it works in other cases doesn't ...

Any ideas ?

Kind regards
Roman