Excel to AS/400 PF

2505 pts.
Tags:
.CSV files
AS/400
Microsoft Excel
I know this topic is widely discussed. But even after going through some of those discussion, I am not able to proceed. I have an Excel sheet which has one row header and one row of data, which includes both numeric as well as alpha data. I have opened it in notepad and but ',' separators after each fields and save it as .csv format and put it in the AS400 folder location using iseries navigator. Now I want to copy that stream file data onto a PF. I have created a file with same field length and attributes and used CPYFRMIMPF command. But all i can see in my pf is the first columns' HEADER. Where did I went wrong? Please assist..

Software/Hardware used:
AS400

Answer Wiki

Thanks. We'll let you know when a new response is added.

CPYFRMIMPF +                                           
   FROMSTMF(&IFSFILE) +                                
             TOFILE(&tofile) MBROPT(*REPlACE) +        
             RCDDLM(*ALL) FROMRCD(2) RPLNULLVAL(*FLDDFT)

Set the &IFSFILE to the path/filename.ext of the .csv.

Set the &tofile to the library/filename you want the data in.

FromRcd(2) tells the copy to start at record number 2 thus skipping the header line in the .csv and leaving it intact.

I hope this helps.

Nick

Discuss This Question: 11  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BigKat
    you need to remove the header row from the CSV, and make sure the fields line up in the same order in the PF and the spreadsheet.  You also may need to specify the option RPLNULLVAL(*FLDDFT) on the CPYFRMIMPF if you had any "empty" cells in the data row.
    8,100 pointsBadges:
    report
  • RamvishakRamesh
    Do I have to put the delimiter in the notepad?
    2,505 pointsBadges:
    report
  • BigKat
    if this is in an excel spreadsheet, make sure the columns are in the correct order (same as PF) then do a save as .CSV it will add the delimiters and separators as needed automatically.  Then just open the new .CSV file in notepad and remove the header row.  (alternately when you go to save the .CSV file, if you delete the header row before you save it, then you won't have to edit it in notepad.  
    8,100 pointsBadges:
    report
  • TomLiotta
    I have opened it in notepad and but ',' separators after each fields...   First, if it actually is an "Excel data sheet", you won't be able to open and modify it with Notepad to put commas between fields. It would take an extremely advanced person a long time to do that. It had to be some data that you exported to some kind of text file from Excel, rather than an "Excel data sheet". Notepad can only handle text files.   Why didn't you export from Excel into .CSV format instead the format you used? That would have put commas in the correct positions and would have put all data from the "Excel data sheet" in the proper .CSV data format. If you modified some text file, we have no way of knowing what the result was. It's not sufficient just to put commas between fields.   ...all i can see in my pf is the first columns' HEADER. Where did I went wrong?   You see the "header" name in the first field in your PF, and no other field has a good value, and only one record is in your PF.   Is that correct?   As shown in the 'Answer', you need to skip the header row when you run CPYFRMIMPF. But that still might not work. We don't know if the data in your text file (that you gave a .CSV extension to) is valid for a .CSV. And we don't know if the PF matches appropriately.   Most likely, the first field in your PF is a character field. That allowed the first header name to be stored in that field because the name is a character value. The second field in your PF might be a numeric field. That would stop the process because the second header field can't be stored in a numeric field.   It's possible that skipping the header row will work. We definitely can't be sure.   We don't know anything about your text file, so we can't predict what can be done with it. We don't know anything about your PF, so we can't predict what can be done with it. You didn't show us the CPYFRMIMPF command that you used, so we don't know what you tried. You didn't show us how you put the text file on your server, so we don't know what file system you stored it in -- we don't know what operations are valid. You didn't show us any of the job messages, so we don't know what the system told you about what went wrong.   Tom
    125,585 pointsBadges:
    report
  • RamvishakRamesh
    I cannot remove the header as it is uploaded by the user.  I have saved it as .csv format and copied it to the location I want ( through iseries navigator) . In my CL program, I am using a CPYFRMIMPF like this CPYFRMIMPF FROMSTMF(&FROMPATH) TOFILE(QTEMP/SGS9008PF) +               MBROPT(*REPLACE) RCDDLM(*LF) FROMRCD(2) +                 RPLNULLVAL(*FLDDFT) Since the first row in my excel is heading, I have used FROMRCD(2) to get the data. It copied 4 records. But that is non readable and also only one column of data it has. I have defined PF with all fields as characters with the same length as in excel for each fields.  My CPYFRMIMPF command is wrong somewhere? Please assist me.
    2,505 pointsBadges:
    report
  • RamvishakRamesh
    In my excel there are numeric fields, but I have declared even those fields as Character in my PF. There is an amount field in the excel which has a value stored like this 1,180.2890. I have declared even this amount field as character of length 16. Even though my file has only 2 record( one header and one data record) I can see 5 records in my PF. Is it because of the ',' the excel has for some amount fields and similar fields.?? How to deal with this?
    2,505 pointsBadges:
    report
  • TomLiotta
    I cannot remove the header as it is uploaded by the user.   You would remove the headers by using FROMRCD(2), which is what you say you did. That was correct.   But if a user uploaded it, why are you trying to modify it by inserting commas?   Earlier, you said:   ...one row of data, which includes both numeric as well as alpha data.   And that means you shouldn't do this:   I have defined PF with all fields as characters...   "Shouldn't", but not "can't".   Your CPYFRMIMPF has this:   ...RCDDLM(*LF)...   Most commonly, that would be RCDDLM(*CRLF). Why do you think it's only *LF?   It copied 4 records. But that is non readable...   How was the file uploaded? And what is its CCSID?   Tom
    125,585 pointsBadges:
    report
  • RamvishakRamesh
    Now for testing purpose, I have uploaded the file to the specific location using iseries navigator. Dragged the file to the location I want. Inorder to avoid errors, I declated the file field as all characters though the excel data is of both numeric and characters. User is uploading the excel which as an amount field and has value like this 1,180.2890. ie with ','.  When I made it is RCDDLM(*CRLF) there is no record copied.
    2,505 pointsBadges:
    report
  • TomLiotta
    User is uploading the excel which as an amount field and has value like this 1,180.2890.   First, what do you mean by "User is uploading the excel..."? It is not an Excel file. We know that because you can edit it to insert commas. You can't edit Excel files to 'insert commas'. You would have to be editting a plain text file. So exactly what is the file?   The user might have exported from Excel, but the requires setting a file type. What type is it?   And what do you mean by "User is uploading..."? Uploading from where? To where?   That's important because you say the final PF characters are unreadable. That often indicates a problem with encoding, e.g., ASCII v. EBCDIC. Also, you say "I have uploaded the file to the specific location using iseries navigator." Is the user uploading? Or are you uploading? Are there two upload sequences? How did you ensure proper encoding as you moved the file across the network?   Also, what does "1,180.2890" mean? Is that a single value? Two separate values? Three values? Do the numeric fields have commas in them?   If they do, they have to be removed in order to use a .CSV format.   Tom
    125,585 pointsBadges:
    report
  • RamvishakRamesh
    The user will put the files into an IFS folder in .csv format. For development purpose, they have mailed me a sample copy of the data as excel. I need to write a program which will copy the .csv files in that folder to a PF. Now the IFS folder has no data so am saving the sample excel data as .csv and moving to IFS folder using iseries Navigator. There is an amount field in the file with data like 1,180.2986
    2,505 pointsBadges:
    report
  • TomLiotta
    The user will put the files into an IFS folder in .csv format.   For development purposes, then, you should open the file that the user sends to you in Excel. And you should save it to the IFS in the proper location in .CSV format the same way that the user will save it. You should not be using Navigator to copy the file from your PC.   And you shouldn't be opening the file in Notepad and modifying it. If the user doesn't save it correctly, you need to ensure that the user knows the proper procedure.   If you delete the test file from the IFS now and let Excel create the .CSV format, it will be a lot easier to make sense of everything. The commas will be correct, the record delimiters will be correct, the encoding will be corret. Then we can be sure that the CPYFRMIMPF command is correct.   As it is, we don't know enough about the file in question to know how it should be handled.   Of course, you should also create an appropriate PF, rather than one with "all character" fields. Define character fields for character values and numeric fields for numeric values. Define all fields with proper lengths. You can't develop a process by using incorrect definitions. There will be errors that will obscure what is really happening.   To create the PF, I would use iSeries Navigator 'Run SQL scripts' and define it as a SQL table. It will be easier to drop the table and recreate it if you need to adjust data lengths or other attributes.   Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following