topic Loading different xls and CSV files in QlikView

Loading different xls and CSV files

qlikviewwizard — Wed, 01 Jul 2015 04:02:25 GMT

Hi All,

I have EMP CSV and xls files with the date timestamp. Daily this files will generate 12:00AM.

I want to use them in to load into QVD.

How to write the script.

Sample data:

EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO

7369, SMITH, CLERK, 7902, 1980-12-17 00:00:00.000, 800.00, NULL, 20

7499, ALLEN, SALESMAN, 7698, 1981-02-20 00:00:00.000, 1600.00, 300.00, 30

7521, WARD, SALESMAN, 7698, 1981-02-22 00:00:00.000, 1250.00, 500.00, 30

7566, JONES, MANAGER, 7839, 1981-04-02 00:00:00.000, 2975.00, NULL, 20

7654, MARTIN, SALESMAN, 7698, 1981-09-28 00:00:00.000, 1250.00, 1400.00, 30

7698, BLAKE, MANAGER, 7839, 1981-05-01 00:00:00.000, 2850.00, NULL, 30

7782, CLARK, MANAGER, 7839, 1981-06-09 00:00:00.000, 2450.00, NULL, 10

7788, SCOTT, ANALYST, 7566, 1982-12-09 00:00:00.000, 3000.00, NULL, 20

7839, KING, PRESIDENT, NULL, 1981-11-17 00:00:00.000, 5000.00, NULL, 10

7844, TURNER, SALESMAN, 7698, 1981-09-08 00:00:00.000, 1500.00, 0.00, 30

7876, ADAMS, CLERK, 7788, 1983-01-12 00:00:00.000, 1100.00, NULL, 20

7900, JAMES, CLERK, 7698, 1981-12-03 00:00:00.000, 950.00, NULL, 30

7902, FORD, ANALYST, 7566, 1981-12-03 00:00:00.000, 3000.00, NULL, 20

7934, MILLER, CLERK, 7782, 1982-01-23 00:00:00.000, 1300.00, NULL, 10

Thanks in advance.

Re: Loading different xls and CSV files

Siva_Sankar — Wed, 01 Jul 2015 04:09:07 GMT

Find the attached document.

Re: Loading different xls and CSV files

Siva_Sankar — Wed, 01 Jul 2015 04:10:04 GMT

Table:

Load * inline
[
EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO

7369, SMITH, CLERK, 7902, 1980-12-17 00:00:00.000, 800.00, NULL, 20

7499, ALLEN, SALESMAN, 7698, 1981-02-20 00:00:00.000, 1600.00, 300.00, 30

7521, WARD, SALESMAN, 7698, 1981-02-22 00:00:00.000, 1250.00, 500.00, 30

7566, JONES, MANAGER, 7839, 1981-04-02 00:00:00.000, 2975.00, NULL, 20

7654, MARTIN, SALESMAN, 7698, 1981-09-28 00:00:00.000, 1250.00, 1400.00, 30

7698, BLAKE, MANAGER, 7839, 1981-05-01 00:00:00.000, 2850.00, NULL, 30

7782, CLARK, MANAGER, 7839, 1981-06-09 00:00:00.000, 2450.00, NULL, 10

7788, SCOTT, ANALYST, 7566, 1982-12-09 00:00:00.000, 3000.00, NULL, 20

7839, KING, PRESIDENT, NULL, 1981-11-17 00:00:00.000, 5000.00, NULL, 10

7844, TURNER, SALESMAN, 7698, 1981-09-08 00:00:00.000, 1500.00, 0.00, 30

7876, ADAMS, CLERK, 7788, 1983-01-12 00:00:00.000, 1100.00, NULL, 20

7900, JAMES, CLERK, 7698, 1981-12-03 00:00:00.000, 950.00, NULL, 30

7902, FORD, ANALYST, 7566, 1981-12-03 00:00:00.000, 3000.00, NULL, 20

7934, MILLER, CLERK, 7782, 1982-01-23 00:00:00.000, 1300.00, NULL, 10
];

STORE Table into Table.qvd(qvd);

Re: Loading different xls and CSV files

robert_mika — Wed, 01 Jul 2015 04:15:10 GMT

Does this help?

This script will save your file with a datestamp

Let x = Timestamp(Now(),'YYYY-MM-DD hh.mm.ss TT');

T1:

LOAD EMPNO,

ENAME,

JOB,

MGR,

HIREDATE,

SAL,

COMM,

DEPTNO

FROM

(ooxml, embedded labels, table is Sheet1);

Store T1 into 'T1 '$(x).qvd(qvd);

Feeling Qlikngry?

How To /Missing Manual(18 articles)

Re: Loading different xls and CSV files

senpradip007 — Wed, 01 Jul 2015 04:16:05 GMT

PFA. Hope it will help you.

Re: Loading different xls and CSV files

qlikviewwizard — Wed, 01 Jul 2015 05:10:31 GMT

Hi shankarece ,

If I have 4 xls files and 5 CSV files, then how to load the data?

Re: Loading different xls and CSV files

qlikviewwizard — Wed, 01 Jul 2015 05:11:52 GMT

Hi senpradip007

Your file shows only for given sample data. If I have 4 xls files and 5 CSV files, then how to load the data?

Re: Loading different xls and CSV files

robert_mika — Wed, 01 Jul 2015 05:20:19 GMT

You probably need For Each Loop

loop through to load all files from a folder and its subfolders?

Re: Loading different xls and CSV files

qlikviewwizard — Wed, 01 Jul 2015 05:23:16 GMT

Thank You Robert_Mika. This is what I am looking for.

Re: Loading different xls and CSV files

jagan — Wed, 01 Jul 2015 05:31:05 GMT

Hi,

If you want to load all the files then try like below instead of going for loops

Data:

LOAD

FROM *.xlsx;

Concatenate(Data)

LOAD

FROM *.csv;

Hope this helps you.

Regards,

Jagan.

Re: Loading different xls and CSV files

qlikviewwizard — Wed, 01 Jul 2015 05:45:25 GMT

Thank you jagan