Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

Auto download of EXCEL to iSeries

Collapse
X
Collapse
 
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
Previous template Next
  • #1

    Auto download of EXCEL to iSeries

    One method would be to use the CPYTOIMPF command. If you never have used this command there are a few things you should know:
      [*]The first time the command is used, there is no EBCDIC to ASCII translation performed. You must use the CPY command to translate the file to PCASCII. All subsequent uses (to the same target file) will be translated.[*]Specify the STMFCODPAG(*PCASCII) RCDDLM(*CRLF) parameters.[*]Use CSV as the suffix of your file, as in myfile.csv.[/list]Dave
    Tags: None
  • #2
    Auto download of EXCEL to iSeries

    Hello Sky, I you are searching for a professional product, have a look for **iGetExcel** It reads your Excel file native at the AS400! Comes with interactive and Batch-mode also. More at www.goering.us Regards Andreas Goering GOERING iSeries Solutions

    Comment

    • #3
      Auto download of EXCEL to iSeries

      I saved my excel spreadsheet as BUDGET.CSV on shared IFS file and created a DDS file on my iSeries with same number of columns (fields) and with max length of 40 ALWNULL. Then use the command: CPYFRMIMPF FROMSTMF('QDLS/javatest/Budget.csv') TOFILE(RODTOOLS/PCBUDGET) MBROPT(*ADD) RCDDLM(*LF) ERRRCDOPT(*REPLACE) but I records were not copied and I have a message listed below. "File QACP563208 in library QRECOVERY was created." "0 records copied from member QACP563208." I am doing this the first time and I was wondering if somebody has done this before. Any help will be greatly appreciated. TIA Sky

      Comment

      • #4
        Auto download of EXCEL to iSeries

        Sky: The cpyfrmimpf first creates a "temporary" file (the qrecovery file) before performming the actual "translation" from ASCII to EBCIDIC. Looking at your cpyfrmimpf command, I can suggest this: You may need to insert a leading "/" slash before QDLS. I am doing this type of import and this is what my command looks like: CPYFRMIMPF FROMSTMF('/TEMP /PEHIPCLR.CSV') TOFILE(QTEMP/EASCLRCHK) RCDDLM(*ALL) Now, why do I have the leading /? As I was working through this, I read the help on the 0 records copied message(same as you got) and it directed me to run command WRKLNK. I did, and it displayed the "path" to my file(the .csv file) as /TEMP /PEHIPCLR.CSV. Don't know why it looks like this, it just does!!!! My implementation narrative is this: Our accounting staff extract a file from an SQL db using crystal reports and I have directed them to land the file in the TEMP folder in the root directory of the IFS. then they sign on to the iSeries and select a menu option that runs this command, imports to a DB2 table from which I use in a COBOL program. I spent several hours trial and error before I got it right. hth

        Comment

        • #5
          Auto download of EXCEL to iSeries

          Well, it works but I have to do a lot of mapping on specially on numeric data, I have to convert the char to dec format. Thanks a lot for your help. Sky

          Comment

          • #6
            Auto download of EXCEL to iSeries

            Sky: What extention is the file that you are pulling to the iSeries? If it is .csv, I am not sure why you are having to "translate " the numeric data. If you can have the excel application save the spreadsheet as a .csv, maybe your formatting problem will solve itself. Let me know if that helps.

            Comment

            • #7
              Auto download of EXCEL to iSeries

              The file extension was .csv but when I copied it to my iSeries PF which I assigned as alpha 40 alwnull the numbers was left justified and negative values enclosed in parenthesis () with comma, so it needs to convert to numeric. I will probably do it in RPGIV and use %BIF's. I tried to create PF with numeric but CPYFRMIMPF dont work. Sky

              Comment

              • #8
                Auto download of EXCEL to iSeries

                Sky: I don't understand why the cpyfrmimpf did not work(convert to numeric). I was able to use the cpyfrmimpf directing the import to a DB2 qsys.lib table that had numeric(packed decimal) data fields, no problem. The only weird thing was I had to extend the length of the numeric fields by two in every case. If you wnat to, send an email to my box at mcpressonline with your contact info and I can call you and we can discuss. Bentley

                Comment

                • #9
                  Auto download of EXCEL to iSeries

                  The message I received when I copied to my DDS which has numeric field was "Pointer not set for location referenced." My spreadsheet has 16 colums where the the column 1 to 3 are characters and column 4 to 16 are numeric values. I could do it if I make all the fields character with max 40 and keyword ALWNULL. here is the dds file DDS file: my email rcortejo@qualicaps.com 0004.00 A R PCBUDGET 0005.00 A FIELD1 40 ALWNULL 0006.00 A FIELD2 40 ALWNULL 0007.00 A FIELD3 40 ALWNULL 0008.00 A FIELD4 11P 0 ALWNULL 0009.00 A FIELD5 11P 0 ALWNULL 0010.00 A FIELD6 11P 0 ALWNULL 0011.00 A FIELD7 11P 0 ALWNULL 0012.00 A FIELD8 11P 0 ALWNULL 0013.00 A FIELD9 11P 0 ALWNULL 0014.00 A FIELD10 11P 0 ALWNULL 0015.00 A FIELD11 11P 0 ALWNULL 0016.00 A FIELD12 11P 0 ALWNULL 0017.00 A FIELD13 11P 0 ALWNULL 0018.00 A FIELD14 11P 0 ALWNULL 0019.00 A FIELD15 11P 0 ALWNULL 0020.00 A FIELD16 11P 0 ALWNULL

                  Comment

                  • #10
                    Auto download of EXCEL to iSeries

                    Is there a way to automatically download excel spreadsheet to the iseries. We have data entered on Excel and what I am doing right now, I manually download the excel thru the CA express wizards which is really cool and then populate and update our BPCS journal file. I want to automate this process so that the user can do it. Any help will be appreciated. Thanks Sky

                    Comment

                    • #11
                      Auto download of EXCEL to iSeries

                      Hi Friends, yes, I'm CEO of GOERING iSeries Solution, so somebody may understand this as a sales campaign... In fact our mission is to help people in providing SOLUTIONS. I dont know what your time is calculated by costs, but I guess you have tried many hours (or days?) on that single problem.... We offer our tool iGetExcel starting at 650$ (Processor tier pricing). So for most of our customers it pays for itself within a couple of days. It's quite easy with iGetExcel to convert Excel field types to AS/400's database types. And: It is a full native solution! Just submit it to batch, add a job to JOB Scheduler.... That's all so far, thanks for giving me the chance to place this information. Kind Regards Andreas Goering GOERING iSeries Solutions http://www.goering.us

                      Comment

                      Previous template Next
                      Working...
                      X