need help transferring file containing special characters!!!

[caren]

Not sure if I'm posting this on the right thread or not...but I'm hoping someone here may have a solution for us. My manager is attempting to transfer a .csv file to the iSeries. Nothing unusual about doing that except that the data in the .csv file contains special characters such as tilde, umlat, etc. that are related to spelling cities in languages other than English. He needs to keep these special characters when transferring the data to our iSeries but the transfer keeps failing. He, and now I (since he's great at dumping things like this!) have tried many different ways but always get the same result of it not transferring. We've tried using DBCS for this but still are not getting the results we expect.

Here is one example:
BEL Belém BR Brazil

We want the data in the iSeries to be Belem, BR with the special character over the 2nd E in Belem to be retained.

Does anyone know of a way to get this data from a .csv file on a PC to a PF on the iSeries???

[jamief]

Re: need help transferring file containing special characters!!!

are you saying this doesnt work?

DDS

       R TESTR                                          
         ONE           20          TEXT('One')          
         TWO           20          TEXT('Two')          
         THREE         20          TEXT('Three')        
         FOUR          20          TEXT('Four') 

Command

CPYFRMIMPF FROMSTMF('jamie/test.csv') TOFILE(JAMIELIB/TESTP) 
   RCDDLM(*CRLF) 

[caren]

Re: need help transferring file containing special characters!!!

Thanks Jamie...

We tried your solution but we receive a CPE3025 error message. We have the .csv file on c: drive on PC. We cannot seem to get it onto the iSeries with the special characters in the city names. Is there somewhere else we should try to put the .csv file before attempting to run the CPYFRMIMPF command?

Caren

[jamief]

Re: need help transferring file containing special characters!!!

Why cant you place this on the IFS....Am I missing something?
I placed the above .csv (changed to .txt so I could post) on a folder on IFS.
Then from command line I ran the CPYFRMSTMF.
It worked as you can see from the image ... the data is on the systemi.

jamie

[RBOrrell]

Re: need help transferring file containing special characters!!!

How are you putting the file on the IFS? FTP? Windows? CA? Perhaps a coversion is happening during that step.

[caren]

Re: need help transferring file containing special characters!!!

As soon as I relayed your solution about the IFS to my manager, the light bulb went on in his head...however, said light bulb dimmed very quickly. We moved the file to the IFS (first trying it as .csv and then as .txt). We then tried to run the CPYFRMSTMF. We were able to see some results but not the records with special characters. We verified that the data came onto the IFS accurately. And for the records that were copied, the results were also accurate. However, we still have a problem with records containing special characters. I guess we'll just have to keep trying various solutions until one works. Thanks for assistance and if you happen to think of anything else, please let me know.

[jamief]

Re: need help transferring file containing special characters!!!

Which special characters are you missing.....
can you supply me with a sample of this data?

Jamie

[caren]

Re: need help transferring file containing special characters!!!

Below are a few of the records that contain special characters along with some without special characters. None of the records with special characters are coming through when we use the copy command (although we are farther along than we were before).

COU La Coruña ES Spain
DAJ Darién Jungle PA Panama
BEL Belém BR Brazil
IBZ Ibiza ES Spain
ILS Les Saintes GP Guadeloupe
INC Incheon (Seoul) KR South Korea


Thanks for checking into this. We tried to follow your example, Jamie, but still were not successful.

[jamief]

Re: need help transferring file containing special characters!!!

is this a csv file? In your example I dont see ","

what is the CCSID of the folder you are using.. and how are you getting it there?

jamie

[caren]

Re: need help transferring file containing special characters!!!

Thanks for your efforts Jamie. We managed to get it there (brute force is how my manager explained it). Our work around method ending up being a file transfer....which caused the records with special characters to show garbage. My manager then went into the spreadsheet and copied the records with special characters and, using DFU...(yeah...he's old school)...he manually inserted the corrected values into the table on the iSeries. Luckily, he only had a small subset of data that he needed to work with for this effort.

[jamief]

Re: need help transferring file containing special characters!!!

okay just to make sure it wasnt an ftp thing...
I created a new .txt file with your data from above.....
I am assuming that your data is CSV as you stated even though I dont see commas..
anyway... I droped the new .txt file onto my c: drive for ease and then FTP to the IFS

331 Enter password.
Password:
530 Log on attempt by user FLANARY rejected.
Login failed.
ftp> user
Username flanary
331 Enter password.
Password:
230 FLANARY logged on.
ftp> bin
200 Representation type is binary IMAGE.
ftp> quote site namefmt 1
250 Now using naming format "1".
ftp> cd /jamie
250 "/jamie" is current directory.
ftp> put test.txt (replace
200 PORT subcommand request successful.
150 Sending file to /jamie/(replace
226 File transfer completed successfully.
ftp: 177 bytes sent in 0.00Seconds 177000.00Kbytes/sec.
ftp> put test.txt

Then ran this

CPYFRMIMPF FROMSTMF('jamie/test.txt') TOFILE(JAMIELIB/TESTP)
    RCDDLM(*CRLF) 

and got the data to work -- see image.. MY CCSID for folder Jamie = 437

[Mercury]

Re: need help transferring file containing special characters!!!

The text encoding of PC file is prob the culprit. Change it to ANSI or Windows-1252 (ASCII) using say Notepad++.

Follow these steps :

1/ create a new empty file with the correct text encoding
2/ copy the data from the csv file into the clipboard
3/ paste into the newly created PC file
4/ delete the stream file created in the IFS if any
5/ ftp the new file to the IFS in BINARY mode
6/ check the data in the IFS file using DSPF '/path/.../NewFile.csv'
7/ if ok CPYFRMIMPF into the DB.

It's at least worth giving a try.

BTW, csv = Comma Separated Values. As Jamie said, where are the commas in the samples you have posted above ?

[RBOrrell]

Re: need help transferring file containing special characters!!!

The text encoding of PC file is prob the culprit. Change it to ANSI or Windows-1252 (ASCII) using say Notepad++.

This was the reason for my question about how he was getting the file on the IFS in the first place. There are a number of ways to do it, each may have it's own got-chas.

[FaStOnE]

Re: need help transferring file containing special characters!!!

Just convert everything to English and leave out all the special characters... that will take care of it! (We don't need all those special characters anyway...)

[Mercury]

Re: need help transferring file containing special characters!!!

Geez, ostrich policy !