vlookup between 2 excel files in progress

S

shafee212

Member
can we do vlookup for 2 different csv flies using progress

i mean to say that we read two csv files such that the two csv files have a common field . so that we can just combine the two csv files in a single csv file using the relationship between two tables. it is a simple join

it uses same concept like "vlookup concept in ms excel "

also the program usses dynamic queries and buffers.
the two files are created dynamically . also vlookup is done dynamically

quick solution is required

thanks in advance
 
i can import them into temp tables.....that too ,,these temp tables have to be dynamic ....but how to combine these temp tables to a single temp tables ..with lookup done or join done dynamicallyyyy .. on the common field....also buffers neede to be created for temp tables and dynamic queries has to be passed .

my knowlegde about dynamic queries is limited.
 
do you need the temp-tables to be dynamics ... because the csv files will be different ?

for example, order's csv and orderline's csv one day and customer's csv and order's csv the other.

because if they're not, that is, if it's always the same data, same columns there would be no reason for it to be dynamic, infact in that case it should be static.
 
plzzzzzz look at the code of my program....this is a generic program that excepts a table name and accepts a csv file for which data has to be extracted from user

my req has been extended ....i want data from 2 tables.

kindly look at my code and give a way out so that i can extract data from 2 tables ie joining two tables

--------------------------------------------------------------

/*xxsample_prg..p - */
/*Copyright TSPL Inc. This is an unpublished work. */
/*V8:ConvertMode=Report */
/*V8:WebEnabled=No */
/*V8:RunMode=Character,Windows */
/******************************************************************************/
/* PROCEDURE NAME :p */
/* PROCEDURE TYPE : */
/* DESCRIPTION : */
/* : */
/* : */
/* INCLUDE FILES : {mfdtitle.i}, {mfquoter.i}, {gpselout.i}, {mfphead2.i} */
/* : {mfrtrail.i} */
/* CALLED BY : */
/* CALLED PROCEDURES : */
/* PARAMETERS PASSED : */
/* NOTES : */
/******************************************************************************/
/* CREATED BY : ECO#: */
/******************************************************************************/


{mfdtitle.i}
DEFINE VARIABLE cInputFile AS CHARACTER NO-UNDO.
DEFINE VARIABLE cInputFile1 AS CHARACTER NO-UNDO.
DEFINE VARIABLE j AS INTEGER INIT 1 NO-UNDO.
DEFINE VARIABLE m_line like mfc_char format "x(80)" NO-UNDO.
DEFINE TEMP-TABLE tttable
FIELD f1 AS CHAR FORMAT "x(20)" LABEL "SAP"
FIELD f2 AS CHAR FORMAT "x(20)" LABEL "MFG"
FIELD f3 AS CHAR FORMAT "x(3)" LABEL "FIX CHECK" init "no"
FIELD f4 AS CHAR FORMAT "x(20)" LABEL "FIX VALUE"
INDEX x1 f1 .
DEFINE TEMP-TABLE tt1 FIELD m1 AS CHAR LABEL "SAP" FIELD m2 AS CHAR LABEL "MFG" INDEX x2 m1.
DEFINE VARIABLE m_sap like f1 NO-UNDO.
DEFINE VARIABLE m_mfg like f2 NO-UNDO .
DEFINE VARIABLE m_fixcheck like f3 NO-UNDO .
DEFINE VARIABLE m_fixvalue like f4 NO-UNDO .
DEFINE VARIABLE tbl_name AS CHARACTER NO-UNDO.
DEFINE VARIABLE count AS INTEGER INIT 0 NO-UNDO.
DEFINE VARIABLE rec AS INTEGER INIT 0 NO-UNDO.
DEFINE VARIABLE m_line2 like mfc_char format "x(80)" NO-UNDO.
DEFINE VARIABLE m_line3 like mfc_char format "x(80)" NO-UNDO.
DEFINE VARIABLE m_line4 like mfc_char format "x(80)" NO-UNDO.
DEFINE VARIABLE m_line5 like mfc_char format "x(80)" NO-UNDO.
form
m_mfg
m_sap
with frame a width 80.
form
f1
f2
with frame p width 80.
ASSIGN cInputFile = '/home/shafeem/aa.csv'.

FOR EACH tttable NO-LOCK:
DELETE tttable.

END.

INPUT FROM VALUE(cInputFile).
IMPORT UNFORMATTED m_line.
INPUT CLOSE.
REPEAT:

CREATE tttable.
ASSIGN f1 = ENTRY(j, m_line, ",") NO-ERROR.
IF ERROR-STATUS:ERROR THEN
LEAVE.
ASSIGN j = j + 1.
END.
FOR EACH TTTABLE no-lock:
DISPLAY f1 .

END.

/*************************************************************************/
/************* SECOND PART MAPPING FILE***********************************/

ASSIGN cInputFile1 = '/home/shafeem/map.csv'.
FOR EACH tt1 NO-LOCK:
DELETE tt1.

END.

INPUT FROM VALUE (cInputfile1).

REPEAT:
CREATE tt1.
IMPORT delimiter "," tt1.
END.

INPUT CLOSE.
FOR EACH tt1 NO-LOCK:
DISPLAY m1 m2.
END.

/********************************************************************************/
/*******************Vlookup Concept**********************************************/
FOR EACH tttable NO-LOCK:
FIND FIRST tt1
WHERE tttable.f1 = tt1.m1 NO-LOCK NO-ERROR.
IF AVAILABLE tt1 THEN
DO:
ASSIGN f2 = m2.
/* DISPLAY tttable.f1 tttable.f2 . */
END.
/* ELSE
DO:
DISPLAY tttable.f1 tttable.f2 .
END. */
/* UPDATE tttable.f2 WITH FRAME b. */
END.
repeat :
FOR EACH tttable NO-LOCK:
DISPLAY f1 f2 f3 f4 with frame p 10 down .
down with frame p.

END.
UPDATE
m_sap
m_mfg
m_fixcheck
m_fixvalue
WITH FRAME z EDITING :
IF FRAME-FIELD = "m_sap" THEN
DO :
{mfnp.i tttable
m_sap
f1
m_sap
f1
x}

IF recno <> ? THEN
DISPLAY f1 @ m_sap f2 @ m_mfg f3 @ m_fixcheck f4 @ m_fixvalue WITH FRAME z WITH side-label.

END. /******end of do******/

ELSE
DO:
READKEY.
APPLY LASTKEY.
END. /******** end of do *******/
END . /*******end editing*********/

FIND FIRST tttable WHERE f1 = m_sap NO-LOCK NO-ERROR.
IF AVAILABLE tttable THEN
DO:
ASSIGN f2 = m_mfg.
END.
END.

/*****************************************************************************/
/* define varaible qh as widget-handle. */
/* define varaible numvar as integer initial 10. */
define variable tth As handle no-undo.
define variable qry_hndl as handle no-undo.
define variable tablbuf_hndl as handle no-undo.
define variable tablbuf_hndl2 as handle no-undo.
define variable qry_hndl2 as handle no-undo.
define variable v_tablename as char no-undo .
define variable v_space as char init " " no-undo .
define variable i as integer no-undo .
create query qry_hndl.
create query qry_hndl2.
define variable count1 as integer no-undo .
define variable qry_hndl1 as handle no-undo.
FOR EACH tttable no-lock:
IF count = 0 THEN DO:
m_line2 = f2.
END.
ELSE
DO:
m_line2 = m_line2 + "," + f2.
END.
ASSIGN count = count + 1.
END.
create temp-table tth.
update v_tablename.
create buffer tablbuf_hndl for table v_tablename.
qry_hndl:set-buffers(tablbuf_hndl).
qry_hndl:query-prepare("for each" + v_space + v_tablename).
qry_hndl:query-open().
tth:CREATE-LIKE(v_tablename).
tth:temp-table-prepare(v_tablename).
tablbuf_hndl2 = tth:DEFAULT-BUFFER-HANDLE .
count1 = 1.
assign i = 1 .


output to "/home/shafeem/source21.csv".
mainloop:
repeat:
qry_hndl:get-next().
if qry_hndl:query-off-end then leave .
DO i = 1 TO tablbuf_hndl:NUM-FIELDS:
IF i = 1 THEN DO:
m_line4 = tablbuf_hndl:buffer-field(i):buffer-value().

END.
ELSE
DO:
m_line4 = m_line4 + ";" + tablbuf_hndl:buffer-field(i):buffer-value().
END.
end.
put unformatted
m_line4 skip .
/*do:
PUT UNFORMATTED
tablbuf_hndl:buffer-field(1):buffer-value() skip. */
/* do i = 1 to tablbuf_hndl:num-fields:
put unformatted
tablbuf_hndl:buffer-field(i):NAME
tablbuf_hndl:buffer-field(i):buffer-value() + ";" skip.

end. */

tablbuf_hndl2:BUFFER-CREATE.
tablbuf_hndl2:BUFFER-COPY(tablbuf_hndl).

END.
output close.


qry_hndl2:SET-BUFFERS(tablbuf_hndl2).
qry_hndl2:QUERY-PREPARE("for each" + v_space + v_tablename).
qry_hndl2:QUERY-OPEN().

i = 1 .
output to "/home/shafeem/tgt4.csv".



DO i = 1 TO tablbuf_hndl2:NUM-FIELDS:
IF LOOKUP( tablbuf_hndl2:BUFFER-FIELD(i):NAME, m_line2 ) > 0 then
do:
IF i = 1 THEN DO:
m_line5 = tablbuf_hndl2:buffer-field(i):name.

END.
ELSE
DO:
m_line5 = m_line5 + ";" + tablbuf_hndl2:buffer-field(i):name.
END.
/* ASSIGN i = i + 1. */
end.
end.

put unformatted m_line5 skip.
/*assign i = 1. */

rec = 0 .
repeat:
qry_hndl2:get-next().
rec = rec + 1 .
if qry_hndl2:query-off-end then leave.
DO i = 1 TO tablbuf_hndl2:NUM-FIELDS:
IF LOOKUP( tablbuf_hndl2:BUFFER-FIELD(i):NAME, m_line2 ) > 0 then
do:
IF i = 1 THEN DO:
m_line3 = tablbuf_hndl2:buffer-field(i):buffer-value().

END.
ELSE
DO:
m_line3 = m_line3 + ";" + tablbuf_hndl2:buffer-field(i):buffer-value().
END.
/* ASSIGN i = i + 1. */
end.
END.

/* IF LOOKUP( tablbuf_hndl2:BUFFER-FIELD(i):NAME, m_line2 ) > 0 then */
PUT UNFORMATTED
m_line3 skip.

/*tablbuf_hndl2:BUFFER-FIELD(i):NAME + ";" */
/* tablbuf_hndl2:BUFFER-FIELD(1):BUFFER-VALUE() ";" tablbuf_hndl2:BUFFER-FIELD(2):BUFFER-VALUE() skip. */
/*SKIP. */


end.
output close.
j = j - 1.
display rec j with frame b.
qry_hndl:query-close().
qry_hndl2:query-close().
delete object qry_hndl.
delete object qry_hndl2.
delete object tablbuf_hndl.
----------------------------------------------



final data is extracted in tgt4 file



either i create bufferhandle for another table

and join the buffer

but i dont know how to write a dynamic query handle to join the two tables data. ( i mean two bufffer as tables contents are stored in buffres)

this could be one soln

another one could be that we create two diffrenet csv from different files

and input them to temptables (has to be dynamic) ...lookup these temp tables (join them?) and out put the whole joined temp table to a csv file.


but i really dont know how to do this things

can u suggest me how to do ......
 
You must log in or register to reply here.
Back
Top