Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
10
Erster Beitrag:
vor 3 Jahren, 4 Monaten
Letzter Beitrag:
vor 3 Jahren, 4 Monaten
Beteiligte Autoren:
Michael Drechsel, Paulo Oliveira, Fabrice Harari

[WDXX] Merge Excel files

Startbeitrag von Michael Drechsel am 17.10.2014 08: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 ??

Antworten:

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

von Fabrice Harari - am 17.10.2014 09:24
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.

von Michael Drechsel - am 17.10.2014 09:30
Probably you can do it using TableToExcel function just use taMerge option.

TableToExcel(your_table , ".....\your_xls.xls" ,taMerge , 20001,40000) and so on.

von Paulo Oliveira - am 20.10.2014 14:03
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.

von Michael Drechsel - am 20.10.2014 14:24
[!!!!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.

von Michael Drechsel - am 24.10.2014 09:53
I have the same problem in WD14, i didn't test in WD18 yet.

In my case i generate CSV files instead using tabletotext

von Paulo Oliveira - am 24.10.2014 10:05
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

von Paulo Oliveira - am 24.10.2014 10:20
Hi Paulo,

In V19 my table with 105.000 lines crashes.

von Michael Drechsel - am 24.10.2014 11:13
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.

von Michael Drechsel - am 24.10.2014 13:29
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.