Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You probably need For Each Loop
loop through to load all files from a folder and its subfolders?
Find the attached document.
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);
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?
PFA. Hope it will help you.
Hi shankarece ,
If I have 4 xls files and 5 CSV files, then how to load the data?
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?
You probably need For Each Loop
loop through to load all files from a folder and its subfolders?
Thank You Robert_Mika. This is what I am looking for.
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.