Re: prob: to convert data from an excel sheet onto the as400 environment.

Pete - all that work is why we have our RPG2SQL product, which also has native 
Excel functionality (non-SQL). For one-offs, sure, but for production work, 
what you describe is daunting.

Regards
Vern

-------------- Original message -------------- 
From: Pete Hall <pbhall@xxxxxxxxxxxxx> 

For a one off situation, the process I use is like this: 
1. Save the worksheet as a tab delimited text file on a network share or 
my iSeries home directory, where it's accessible to the iSeries. 
2. Delete header rows and any blank rows at the end of the file 
3. Remove/normalize quotes - Excel will enclose strings containing 
embedded quote marks with quotes, and double the embedded quotes. I use 
a little awk script to fix them. I've included it at the end of this 
message. 
3. Create a database table that matches the fields in the tab delimited 
file. 
4. Use the CPYFRMIMPF command to copy the tab delimited file to the 
database, something like this: 
CPYFRMIMPF FROMSTMF('/home//') 
TOFILE(ARGETLIB/TARGETFILE) 
MBROPT(*REPLACE) 
RCDDLM(*CRLF) 
STRDLM(*NONE) 
FLDDLM(*TAB) 
DATFMT(*USA) 
RPLNULLVAL(*FLDDFT) 

Here's the awk script: 
BEGIN { 
# Tab delimited file 
FS="\t"; 
OFS="\t"; 
} 
/./ { 
# For each field in the current record 
for (i=1;i<=NF;i++) { 
# Remove the first and last character from the field 
# only if both are quotes. 
if (($i ~ /^\"/) && ($i ~ /\"$/)) { 
# Remove the beginning quote 
gsub("^\"","",$i); 
# Remove the ending quote 
gsub("\"$","",$i); 
} 
# Replace multiple contiguous quotes with a single quote 
gsub("\"+","\"",$i); 
} 
print; 
} 

-- 

Pete Hall 
pbhall@xxxxxxxxxxxxx 
http://www.pbhall.us/ 



sunny singh wrote: 

good morning everybody 

i have a prob.suppose i have my data on an excel sheet and i want to use 

that data data in my rpg program. how can i copy that data into the pf.or 
what 
should be the best method to do so. 

please help me out o find the right answer. 
thank you 



--------------------------------- 
Get your email and more, right on the new Yahoo.com 

-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
or email: MIDRANGE-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/midrange-l.