Exporting into excel

[Guest]

Hi,

I have a acess table with 360,000 rows, how do I export rows 1-65,000 into
one excel spreadsheet, then 65,001 - 130,000 etc?

Because I want to do some editions that are not possbile in access.

THanks for your help.

 

[Douglas J. Steele]

Create a query that sorts the results by ID. Change the query to a TOP 65000
query (either through the properties of the query, or by going into the
SQL). Export the query.

Note the last ID number that was exported. Change your query so that it only
returns those records where ID > last id exported. Re-export.

Repeat until done.

 

[Joseph Meehan]

Hi,

I have a acess table with 360,000 rows, how do I export rows 1-65,000
into one excel spreadsheet, then 65,001 - 130,000 etc?

Because I want to do some editions that are not possbile in access.

THanks for your help.

I understand the next version of Excel extends that limit.

 

[Jamie Collins]

Create a query that sorts the results by ID. Change the query to a TOP 65000
query

A slightly different approach would be exploit the fact that an INSERT
to a worksheet stops (rather than fails) when the resultset row count
exceeds the worksheet row limit. This also relies to a certain extent
on a unique identifier being present (as it should be).

To demonstrate:

Create a new Excel workbook with a table to receive the table:

CREATE TABLE [Excel 8.0;DATABASE=C:\New.xls;].TargetRange1 (
data_col DOUBLE
);

Note that Excel has limited data types, all columns are nullable, no
constraints, etc.

Create a table in the database with more rows than the Excel worksheet
limit:

CREATE TABLE Test (
data_col INTEGER NOT NULL UNIQUE)
;
INSERT INTO Test (data_col) VALUES (1)
;
INSERT INTO Test (data_col)
SELECT Units.nbr + Tens.nbr + Hundreds.nbr + Thousands.nbr +
TenThousands.nbr AS seq FROM ( SELECT nbr FROM ( SELECT 0 AS nbr FROM
DropMe UNION ALL SELECT 1 FROM DropMe UNION ALL SELECT 2 FROM DropMe
UNION ALL SELECT 3 FROM DropMe UNION ALL SELECT 4 FROM DropMe UNION ALL

SELECT 5 FROM DropMe UNION ALL SELECT 6 FROM DropMe UNION ALL SELECT 7
FROM DropMe UNION ALL SELECT 8 FROM DropMe UNION ALL SELECT 9 FROM
DropMe ) AS DIGITS ) AS Units, ( SELECT nbr * 10 AS nbr FROM ( SELECT 0

AS nbr FROM DropMe UNION ALL SELECT 1 FROM DropMe UNION ALL SELECT 2
FROM DropMe UNION ALL SELECT 3 FROM DropMe UNION ALL SELECT 4 FROM
DropMe UNION ALL SELECT 5 FROM DropMe UNION ALL SELECT 6 FROM DropMe
UNION ALL SELECT 7 FROM DropMe UNION ALL SELECT 8 FROM DropMe UNION ALL

SELECT 9 FROM DropMe ) AS DIGITS ) AS Tens, ( SELECT nbr * 100 AS nbr
FROM ( SELECT 0 AS nbr FROM DropMe UNION ALL SELECT 1 FROM DropMe UNION

ALL SELECT 2 FROM DropMe UNION ALL SELECT 3 FROM DropMe UNION ALL
SELECT 4 FROM DropMe UNION ALL SELECT 5 FROM DropMe UNION ALL SELECT 6
FROM DropMe UNION ALL SELECT 7 FROM DropMe UNION ALL SELECT 8 FROM
DropMe UNION ALL SELECT 9 FROM DropMe ) AS DIGITS ) AS Hundreds, (
SELECT nbr * 1000 AS nbr FROM ( SELECT 0 AS nbr FROM DropMe UNION ALL
SELECT 1 FROM DropMe UNION ALL SELECT 2 FROM DropMe UNION ALL SELECT 3
FROM DropMe UNION ALL SELECT 4 FROM DropMe UNION ALL SELECT 5 FROM
DropMe UNION ALL SELECT 6 FROM DropMe UNION ALL SELECT 7 FROM DropMe
UNION ALL SELECT 8 FROM DropMe UNION ALL SELECT 9 FROM DropMe ) AS
DIGITS ) AS Thousands, ( SELECT nbr * 10000 AS nbr FROM ( SELECT 0 AS
nbr FROM DropMe UNION ALL SELECT 1 FROM DropMe UNION ALL SELECT 2 FROM
DropMe UNION ALL SELECT 3 FROM DropMe UNION ALL SELECT 4 FROM DropMe
UNION ALL SELECT 5 FROM DropMe UNION ALL SELECT 6 FROM DropMe UNION ALL

SELECT 7 FROM DropMe UNION ALL SELECT 8 FROM DropMe UNION ALL SELECT 9
FROM DropMe ) AS DIGITS ) AS TenThousands
WHERE Units.nbr + Tens.nbr + Hundreds.nbr + Thousands.nbr +
TenThousands.nbr BETWEEN 2 AND 99999
;

Attempt to INSERT all the rows into the worksheet:

INSERT INTO [Excel 8.0;DATABASE=C:\New.xls;].TargetRange1 (data_col)
SELECT T1.data_col FROM Test AS T1
;

The 'Spreadsheet is full' error shows; on inspection, a partial
insertion is seen to have succeeded, resulting in a full Excel column.

Proceeding (if it was opened for manual inspection, don't forget to
close the workbook otherwise the dreaded Q319998 memory leak bug will
be encountered), add another worksheet:

CREATE TABLE [Excel 8.0;DATABASE=C:\New.xls;].TargetRange2 (
data_col DOUBLE
);

Attempt to INSERT the rows not already done:

INSERT INTO [Excel 8.0;DATABASE=C:\New.xls;].TargetRange2 (data_col)
SELECT T1.data_col FROM Test AS T1
LEFT JOIN [Excel 8.0;DATABASE=C:\New.xls;].TargetRange1 AS R1
ON T1.data_col = R1.data_col
WHERE R1.data_col IS NULL;

The remainder seems to have been added to the second sheet. Check the
control total:

SELECT COUNT(*) AS missing_row_tally
FROM Test AS T1
LEFT JOIN
(
SELECT data_col FROM [Excel 8.0;DATABASE=C:\New.xls;].TargetRange1
UNION ALL
SELECT data_col FROM [Excel 8.0;DATABASE=C:\New.xls;].TargetRange2
) AS DT1 ON T1.data_col = DT1.data_col
WHERE DT1.data_col IS NULL;

Jamie.

--