topic Re: Read data from two files and generate a single file in Connectivity & Data Prep

Read data from two files and generate a single file

user8 — Tue, 21 Dec 2021 20:26:43 GMT

Hi,

I have two files and want to read specific columns and produce a single file based on the matching columns. For e.g.

--> File1

Emp No	Emp Name
123	Steve
456	Bryan

--> File2

Emp No	Product
123	Samsung
456	Apple

--> File3 (Desired output)

Emp No	Emp Name	Product
123	Steve	Samsung
456	Bryan	Apple

Is this possible and can you share the script?

Thank you.

Re: Read data from two files and generate a single file

ArjunChandra — Fri, 24 Apr 2020 04:50:01 GMT

If you find better answer please aslo share with me.

T1:
LOAD * INLINE [

Eno,EName
123,Steve
456,Bryan
];
Inner Join

LOAD * INLINE [

Eno,Product
123,Samsung
456,Apple
];
Try this

T1:
LOAD * inline [

Eno,Ename,Salary
123,Steve,55000
456,BRYAN,90000
];

T2:
LOAD * inline [

Eno,Product,UnitCost
123,Samsung,20000
456,Apple,60000
];

Temp:
LOAD Eno,Ename Resident T1;
Inner Join
Temp1:
LOAD Eno,Product Resident T2;

DROP Table T1;
DROP Table T2;

Re: Read data from two files and generate a single file

user8 — Fri, 24 Apr 2020 10:54:51 GMT

Below is my script.

File1 and File2 will change daily but when I reload the app it does not pick up the latest File1 and File2 located in lib://AttachedFiles. Any idea?

File1: Load number name From [lib://AttachedFiles/File1.xlsx] Store File1 into [lib://Folder/File1.qvd] (qvd); Inner Join Load number, product From [lib://AttachedFiles/File2.xlsx] Store File1 into [lib://Folder/File1.qvd] (qvd);

Re: Read data from two files and generate a single file

Saravanan_Desingh — Fri, 24 Apr 2020 13:24:41 GMT

Hi. Is this (lib://AttachedFiles) a SharePoint? If so, files are checked-in?

Re: Read data from two files and generate a single file

user8 — Mon, 27 Apr 2020 02:27:03 GMT

lib://AttachedFiles is not a sharepoint. See attached pic.