Im trying to export a table with 152,000 records to CSV.  XL will only open
60K records.  I need to break up the file loading the contents into 3 new
files
file 1 record numbers 1-50,000
file 2 record numbers 50,000 - 100,000
file 3 record numbers 100,000 - 150,000

How do I do that

Ben Sprei

On 11/20/2014 11:38 PM, Ben wrote:
> Im trying to export a table with 152,000 records to CSV.  XL will only
> open 60K records.  I need to break up the file loading the contents into
> 3 new files
> How do I do that

You must be running a really old excel - this limit was changed back in
office 2007 days.

If it's one time then using a decent text editor just save the csv into
3 separate csv files.

Other option is to use sql to populate 3 intermediate tables and then
export each of those

Raul

What would be the code to get the first 60K records and then the NEXT 60K
and so on

Ben

"Raul"  wrote in message
news:B4A183AF-57A4-4EBD-AED7-31FF7137D275@news.elevatesoft.com...

On 11/20/2014 11:38 PM, Ben wrote:
> Im trying to export a table with 152,000 records to CSV.  XL will only
> open 60K records.  I need to break up the file loading the contents into
> 3 new files
> How do I do that

You must be running a really old excel - this limit was changed back in
office 2007 days.

If it's one time then using a decent text editor just save the csv into
3 separate csv files.

Other option is to use sql to populate 3 intermediate tables and then
export each of those

Raul

>>"What would be the code to get the first 60K records and then the NEXT 60K
>>and so on

I am not sure how you have structured the table. If you have an Integer Index you can use this, if not perhaps consider adding one to the table. Once you have an integer index on the table, use

CREATE [TABLE <ExportTableName>
<ColumnName> <ColumnDefinition>,
...

AS
--any valid select is acceptable here.
SELECT
<ColumnName>,
...

FROM <SourceTableName>

WHERE ID BETWEEN 1 AND 59,999

WITH DATA

--

Adding the Integer Indexing field:

ALTER TABLE <SourceTableName>
ADD COLUMN "ID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)!

CREATE INDEX "PID" ON <SourceTable> ("ID)!

On 11/22/2014 7:41 PM, Ben wrote:
> What would be the code to get the first 60K records and then the NEXT
> 60K and so on

use RANGE statement :

SELECT * from <SourceTableName> RANGE 1 to 59999

See also Adam's answer on how to insert into a new table with the select.

Raul

Ben


With a situation like your's I'd recommend the use of a third party tool eg Mike Skolnik's export suite (www.scalabium.com - high quality low cost) which would give you more flexibility, could export directly to xls format but would be slower than native EXPORT.

Roy Lambert