PC SOFT

FORUMS PROFESSIONNELS
WINDEVWEBDEV et WINDEV Mobile

Accueil → WINDEV 2024 → [WDXX] Merge Excel files
[WDXX] Merge Excel files
Débuté par Michael Drechsel, 17 oct. 2014 10:16 - 11 réponses
Posté le 17 octobre 2014 - 10:16
Hi,

I use tabletoexcel to export a large table to excel. I use WD14 and it takes a long time to export all rows in one excel file.
If I split it in 20.000-row pieces the process run faster. Is there any way to merge these excel files after that in one file with any windev version ??
Posté le 17 octobre 2014 - 11:24
Hi Michael

for a big file, there is a significant degradation in performance with the excel functions... So it may be faster (you'll have to test, of course), to export as CSV first then import into excel using OLE automation.

Best regards
Posté le 17 octobre 2014 - 11:30
Hi Fabrice,

I test all availible table2anything functions in WD14, nothing works for the whole table. The only way to get a good performance is to cut it in pieces.

Ok, the result are 5 files, so the user must use excel to merge they together.
Posté le 17 octobre 2014 - 14:52
Hi
You say it is too slow.
How long is this treatment ? And what is the maximum delay you accept ?

Build a loop : for each line , string +=[RC] + content of the line ( separator TAB for columns)

Then copy/paste this string to Excel ;
Should be less than 5 sec.
Posté le 20 octobre 2014 - 16:03
Probably you can do it using TableToExcel function just use taMerge option.

TableToExcel(your_table , ".....\your_xls.xls" ,taMerge , 20001,40000) and so on.
Posté le 20 octobre 2014 - 16:24
Hi Paulo,

nope.

The help says:

If the XLS file already exists, merges the existing content with the new content: the formulas, the formatting, ... of the existing cells is kept. The existing data is not kept. Only the data found in the additional columns of the XLS file is kept.

But I solved the problem with the built in excel functions ( I copy the whole content from file 1,2,3 into the final file)
It works pretty cool.
Posté le 24 octobre 2014 - 11:53
[!!!!IMPORTANT UPDATE !!!!]

Although the support *.XLSX Files (which can have approx. 1 Million rows) it seems that internally run the old excel engine. If your Table has more than 65.000 rows (the old specification for *.xls files) the "xlssave" and "xlsclose" instruction crashes or damage the outpute file.
Posté le 24 octobre 2014 - 12:05
I have the same problem in WD14, i didn't test in WD18 yet.

In my case i generate CSV files instead using tabletotext
Posté le 24 octobre 2014 - 12:20
I did one quick test in V14 & V18 with tabletoexcel using one table with 68995 lines and the result was:
V14 - the excel only have 65536 lines even if i use xlsx in the extension as sugested in the help
V18 - The excell have all the lines
Posté le 24 octobre 2014 - 13:13
Hi Paulo,

In V19 my table with 105.000 lines crashes.
Posté le 24 octobre 2014 - 15:29
In WD14 "tabletotext" it takes also (like tabletoexcel) a loooong time.

Finaly the only way is to split it into 20.000 line pieces and merge it together with excel.
Posté le 16 janvier 2019 - 07:35
I Think you should Try Some Dedicated Excel Tool Like Synkronizer. It is a Microsoft Excel Add-in which is used for Compare & Merge Large amount of Excel Sheet data.
https://www.synkronizer.com/compare-excel-tables-features/merge