Slow xls load only in QV Server

[cristian_av]

Hi

I have a script that loads data from about 8 xls files. For some reason, it is really slow in QV Server.

When I run the script in my computer, it takes a less than a minute to load the data

When I load it in the QV Server, by openin QV Desktop on the server, it takes about 15 minutes only in the xls.

I'm always running the same file with the same data.

I've checked the CPU (2-4%), RAM (10 Gb Free) and Network (2 Mb/s usage) of the server, but it is fine.

The xls files are on a second server. To test connectivity, I've copied the xls to the QV Server, and it took only 3 seconds.

Reload in my computer: (about 3 minutes in reload all the script)

Reload in Server: (about 17 minutes in reload all the script)

-Slow only in the part that loads the xls files.

This is the part of the code that is causing the problem in the server:

SET RepositorioXLS = '\\HV-SERVER-CM\comex$\Archivos ASOEX';

CargaAsoex:

LOAD * INLINE [CampoTemporal];

FOR each File in filelist(RepositorioXLS& '\T*--*-*-* al *-*-*.xls')

CONCATENATE (CargaAsoex)

LOAD right(left(mid('$(File)',index('$(File)','\',-1)+1),5),4) as %pate_tempor,

ESPECIES,

    VARIEDAD,

    CAJAS,

    KG.,

    EXPORTADOR,

    [REGION DESTINO],

    [PUERTO DESTINO],

    [PAIS DESTINO],

    RECIBIDOR,

    NAVE,

    [TIPO NAVE],

    SEMANA,

    FECHA,

    [PUERTO EMBARQUE],

      FileTime('$(File)') as FechaArchivo

FROM [$(File)] (biff, embedded labels, table is Asoex$)

Where right(left(mid('$(File)',index('$(File)','\',-1)+1),5),4)>=$(vAñoInicio)-3;

NEXT File

DROP FIELD CampoTemporal;

[Gysbert_Wassenaar]

Sounds like a slow network connection. Have you tested with the excel files located in a directory on your QV server?

[cristian_av]

I've just tried it.. with no luck!

Any other idea?

Thanks!!

[Gysbert_Wassenaar]

What do you mean 'with no luck'? You can't put the excel files on the server? The reload fails? Or it all works, but just as slow as before?

[cristian_av]

Sorry!

All works, but just as slow!

Finally I've changed the format to xlsx and it works fine. Don't know why.

Acoording to this, it seems to be something tricky with xls (biff) files.

Bad Zip File Error from reading XLS files

[Gysbert_Wassenaar]

Glad you found a solution.