ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Moving csv file with special characters from pc to machine .

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Moving csv file with special characters from pc to machine .

    Hello all!
    trying to fix the problem but it seems that I am missing or messing something ...

    let say I want to send test1.csv file with special characters to machine (for a test: pub400.com) - CSV file encoding utf-8.

    >>test1.csv content:
    wartość1
    wartość2
    wartość3


    >> transfer file to iseries
    C:\Users\Admin>ftp pub400.com
    Connected to pub400.com.
    220-QTCP at PUB400.COM.
    220 Connection will close if idle more than 16 minutes.
    501 OPTS unsuccessful; specified subcommand not recognized.
    User (pub400.comnone)):
    331 Enter password.
    Password:
    230 logged on.
    ftp> lcd >> path to file on pc <<
    Local directory now >> path to file on pc <<
    ftp> quote type c 1153
    200 Representation type is CCSID 1153.
    ftp> put file1.csv
    200 PORT subcommand request successful.
    150 Sending file to /home/myid/file1.csv
    226 File transfer completed successfully.
    ftp: 25 bytes sent in 0.23Seconds 0.11Kbytes/sec.

    >>on machine's side
    CHGJOB LANGID(PLK) CNTRYID(PL) CCSID(1153)

    >>and checking file on ifs side
    ls -laS
    total: 5.552 megabytes
    273 drwx--s--- 2 usr 0 8192 Nov 14 11:00 .
    37 drwx--sr-x 15423 QSYS 0 5652480 Nov 14 09:58 ..
    1153 -rwx--x--- 1 usr 0 28 Nov 14 11:00 test1.csv
    $

    >> creating pf and compile
    *************** Beginning of data *************************************
    R FILE1
    VALUES 30A CCSID(1153)
    ****************** End of data ****************************************


    >> column check: dspffd usrLib/file1
    Record length . . . . . . . . . . . . . . . : 30
    Field Level Information
    Data Field Buffer Buffer Field Column
    Field Type Length Length Position Usage Heading
    VALUES CHAR 30 30 1 Both VALUES
    Coded Character Set Identifier . . . . . : 1153


    >> copy stream data to pf
    CPYFRMIMPF FROMSTMF('/home/user/test1.csv') TOFILE(userLib/FILE1) RCDDLM(*CRLF) RPLNULLVAL(*FLDDFT)


    >> check file: runqry *n usrLib/file1

    Position to line . . . . .
    Line ....+....1....+....2....+....3
    VALUES
    000001 Î/%ÍÁ De Î/%ÍÁ Dg Î/%ÍÁ E[
    ****** ******** End of report ********




    Cheers
    Artur

  • #2
    Not an expert in this by any means - but my first thought was why are you using code page 1153 for the FTP transfer? Surely if the file is in UTF8 you need it to be UTF8 in the IFS. Then the translation to 1153 occurs during the CPYF.

    Comment


    • #3
      hi, thx for answering , ok i've tried this way..

      C:\Users\Admin>ftp pub400.com
      Connected to pub400.com.
      220-QTCP at PUB400.COM.
      220 Connection will close if idle more than 16 minutes.
      501 OPTS unsuccessful; specified subcommand not recognized.
      User (pub400.comnone)): usrprf
      331 Enter password.
      Password:
      230 usrprflogged on.
      ftp> lcd >>path to dir<<
      Local directory now >>path to dir<<
      ftp> binary
      200 Representation type is binary IMAGE.
      ftp> put test1.csv
      200 PORT subcommand request successful.
      150 Sending file to /home/usrrpf/test1.csv
      226 File transfer completed successfully.
      ftp: 34 bytes sent in 0.25Seconds 0.14Kbytes/sec.


      then:
      Selection or command
      ===> CPYFRMIMPF FROMSTMF('/home/a210213/test1.csv') TOFILE(A2102131/FILE2) TOCCSID(1153) RCDDLM(*CRLF)

      checking output:
      Position to line . . . . .
      Line ....+....1....+....2....+....3
      VALUES
      000001 wartoÅ Ä 1
      000002 wartoÅ Ä 2
      000003 wartoÅ Ä 3
      ****** ******** End of report ********

      maybe it's because 5250 emulator ? i have coding page set to 37..

      Artur

      Comment


      • #4
        Well it is a heck of a lot closer!

        1) What codepage is the IFS file? Did it create it as UTF-8 (1208) ?
        2) Have you viewed the hex value of the characters in the IFS file? This will tell you if the data made it to the IFS intact. If data is intact but code page is wrong just change the code page on the file and try the copy again.
        3) View the PF with DSPF and look at the hex values (F10) that will show you if it is a display or translation problem.

        Comment


        • #5
          1) not really it's 819..
          > ls -laS test*
          819 -rwx--x--- 1 usrprf 0 34 Nov 15 16:37 test1.csv
          $

          2)
          file (hex) from pc:
          77 61 72 74 6f c5 9b c4 87 31 0d 0a
          77 61 72 74 6f c5 9b c4 87 32 0d 0a
          77 61 72 74 6f c5 9b c4 87 33

          file (hex) from ifs:
          - - - - + - - - - * - - - - + - - - - * ----+----*----+----*
          405C5C5C 5C5C5C5C 5C5C5C5C 5CC28587 89959589 ************Beginni
          77617274 6FC59BC4 87310D0A warto Ä 1
          77617274 6FC59BC4 87320D0A warto Ä 2
          77617274 6FC59BC4 87330D0A warto Ä 3
          405C5C5C 5C5C5C5C 5C5C5C5C 5CC59584 40968640 ************End of



          3) dspf + F10
          Display Physical File Member
          File . . . . . . : FILE2 Library . . . . : A2102131
          Member . . . . . : FILE2 Record . . . . . : 1
          Control . . . . . Column . . . . . : 1
          Find . . . . . . .
          * . . . + . . . . 1 . . . . + . . . . 2 . . . . + . . . . 3 *...+....1....+....2....+....3
          A68199A3 96673B4A 17F1 4040 40404040 40404040 40404040 40404040 4040 *warto [ 1 *
          A68199A3 96673B4A 17F2 4040 40404040 40404040 40404040 40404040 4040 *warto [ 2 *
          A68199A3 96673B4A 17F3 4040 40404040 40404040 40404040 40404040 4040 *warto [ 3 *


          Additionally, I've checked on ibm i website "supported ccsid mappings" section (link:https://www.ibm.com/support/knowledg...idmappings.htm) and from ccsid 819 I should be able to go to : 00870 or 01153

          I found that utf-8's not had all special character (for ccsid 870 or 1153) , the file needs to be set to utf-16 or ucs-2..

          , and then (https://www.ibm.com/support/pages/i5...nicode-support)

          "i5/OS FTP does not support transfer of data in all Unicode formats. Note that FTP is not listed as an
          application supporting Unicode in the iSeries Information Center article specifying i5/OS Unicode support,
          "Unicode on i5/OS" (or "Unicode on OS/400" in pre-V5R4 versions).

          Specifically, i5/OS FTP cannot correctly perform data transfers where the file or directory data send on the
          network is in UCS-2 or UTF-16 formats (CCSIDs 13488, 61952, or 1200).

          The i5/OS FTP design properly supports UTF-8 data (CCSID 1208), and many i5/OS users successfully
          perform FTP transfers using UTF-8; however, IBM has not formally tested this support, and cannot guarantee
          that it will work in all cases."

          .... so ftp seems not an option.

          Comment


          • #6
            OK so the PC encoding is correct. As is the IFS - so simply change the IFS code page to 1208 and try again.

            Comment


            • #7
              The data in your file appears to be in UTF-8 (CCSID 1208), but you have it marked as iso-8859-1 (CCSID 819). Its very much like making a jar of strawberry jam and putting a label on it that says "raspberry". The label doesn't match the contents. Change the CCSID to 1208.

              Comment


              • #8
                Finally

                file test1.csv utf-8, with data:
                wartość1
                wartość2
                wartość3

                0) on machine:
                5250 char code: 870
                job ccsid: 870


                1) sending file
                C:\Users\Admin>ftp pub400.com
                Connected to pub400.com.
                220-QTCP at PUB400.COM.
                220 Connection will close if idle more than 16 minutes.
                501 OPTS unsuccessful; specified subcommand not recognized.
                User (pub400.comnone)): usrprf
                331 Enter password.
                Password:
                230 usrprf logged on.
                ftp> lcd pathToFile
                Local directory now pathToFile.
                ftp> binary
                200 Representation type is binary IMAGE.
                ftp> put test1.csv
                200 PORT subcommand request successful.
                150 Sending file to /home/usrprf/test1.csv
                226 File transfer completed successfully.
                ftp: 34 bytes sent in 0.26Seconds 0.13Kbytes/sec.
                ftp>


                2) changing ccsid to 1208
                > setccsid 1208 test1.csv
                $
                > ls -laS
                total: 5.624 megabytes
                273 drwx--s--- 2 usrprf 0 8192 Nov 16 12:58 .
                37 drwx--sr-x 15465 QSYS 0 5718016 Nov 16 15:39 ..
                1208 -rw------- 1 usrprf 0 64 Nov 16 09:24 .sh_history
                1208 -rwx--x--- 1 usrprf 0 34 Nov 16 20:02 test1.csv

                3) compile PF
                * Beginning of data **************************
                R FILE1
                FIELD1 10A CCSID(1208)
                **** End of data *****************************

                4) copy
                ===> CPYFRMIMPF FROMSTMF('/home/usrprf/test1.csv') TOFILE(usrprf/FILE3) TOCCSID(1153) RCDDLM(*CRLF)

                5) checking file
                Line ....+....1
                FIELD1
                000001 wartość1
                000002 wartość2
                000003 wartość3


                Thank you

                Comment


                • #9
                  Glad you got it sorted. If only folks had envisaged the WWW back in the 1950s we might never have had this whole codepage mess!

                  Comment

                  Working...
                  X