splitting excel file in automation process based on rows number

Toad Data Point & Toad Intelligence Central
1

My problem is that i have a table with 500000 rows and want to export all records into excel. The excel file must be divided into two excel files each with 250000 rows for example . Is there a way of achieving this automatically via Toad analysts or toad data point ? If so, how?

the steps of automation process

exporting SQL query to excel file

system counts rows if more than 250000 split the excel file into file with 250000 rows at most and create another excel file to put the rest of rows and so on

the system attach each excel file in mail message and send it to recipients

2

Hello thunderlights880,

Thank you for the post.

It is possible to do it - here is an idea to get you started with:

  1. add “Set variable” activity
  2. add variable “FileRow” of type integer and value 0
  3. add variable “FileRowCount” of type integer and value 0
  4. add “Execute script” activity
  5. (Now we will create two tables - one that contains all the data and second that will be filled later with always 250k rows of data extracted from the first table)
  6. DROP TABLE if exists myTempTable ---- the “if existst” part differs among providers… this should work in MySQL for example
  7. CREATE TABLE myTempTable (id with primary key…, then your columns here)
  8. INSERT INTO myTempTable SELECT <your_large_data>) (let’s say we have 2.000.000 rows (so 8 excel fiels if one should be per 250.000)
  9. DROP TABLE if exists myTempTableOut
  10. CREATE TABLE myTempTableOut (id with primary key…, then your columns here)
  11. add “While…” activity (for loop)
  12. the condition would be #FileRow# == 0 or #FileRowCount# > 0
  13. add “Set variable value” activity into the while branch
  14. increment the variable FileRow by 1 (i.e. every new loop iteration will increase the file row variable)
  15. add “Execute script” activity into the While branch
  16. change the row count variable name to “FileRowCount” (originally it’s something like Execute_1_RCOUNT)
  17. TRUNCATE TABLE myTempTableOut
  18. INSERT INTO myTempTableOut SELECT TOP 250000 * FROM myTempTable
  19. DELETE FROM myTempTable WHERE id in (SELECT DISTINCT id from myTempTableOut)
  20. SELECT id from myTempTableOut
  21. add “Log comment” activity into the while branch
  22. this will be just for diagnostics
  23. add text “file row: #FileRow#, output row count: #FileRowCount#”
  24. you need to ensure that it will give you the exact row count that is currently present inside the myTempTableOut… (the last select statement ensures to fill the variable)
  25. add “Select to file” activity into the while branch
  26. SELECT * FROM myTempTableOut
  27. specify output type Excel and for the file path use “myOutputFile_#FileRow#”
  28. the above step ensures that each new file generated will get _1 _2 _3 etc. in the ending
  29. add “Archive” activity at the end of the script
  30. zip all files that match the pattern myOutputFile_*.xlsx
  31. add “Send email” activity at the end of the script
  32. attach the zipped archive file
    Hope it makes sense (i didn’t test it, i only wrote it from my head)

Martin

3

can you provide me with a little SQL query example for steps 2 and 5 please so i could figure it out ?

4

Suppose that i have already TempTable and data is inserted

5

i tried your way but unfortunately it did not work

6

Alright let me try to make a sample script for you then, will get back to you, hopefully soon

7

Here is the sample script which works for me. I used “region” table with 5 rows where “region_id” is the primary key… The first and second activity is about initializing the environment… i commented there the create table scripts…

Please change the connection, queries, paths and email info…

Go ahead and study it and let me know about the result,

Martin
script.tas (24.6 KB)

8

i appreciate your effort but after i did it with the edits you mentioned it showed errors and didn’t work ;/

9

I believe that would be expected due to many changes needed. Could you please be more specific what errors do you get? Maybe sending me a sample script would help me to determine the root cause.

If you believe it’s sensitive information, you can use my email, we can keep it “offline”: Martin.Holkovic@quest.com

10

Dear @Martin.Holkovic,

I would like to seperate files based on the size. Each file should have max 10 MB. Is there any option to do that in TOAD.

I have 900k rows and it is around 80 MB so I would like to export the data with max 10MB files.

Is it possible to do that?

11

Hi,
Natively we are not supporting this feature. But you can export it to one file and then split it by some external tool (like 7zip) or use zip from OS. You can create command for it and run it in one automation script - with Run Program activity

something like:
zip -r -s 10m archive.zip "C:\FolderWithExportedFile"