https://netsuiteprofessionals.com logo
Join Slack
Powered by
Hi All! SFTP-CSV issue: I have created a Map/Redu...
# suitescript
e
Hi All! SFTP-CSV issue: I have created a Map/Reduce that connect successfully to a SFTP Server, I get a CSV file with the 
connection.download
method, however I'm dealing with special accented characters (French) like this: Théorêt. I save the the CSV file in the file cabinet as is, just setting a new name and the right folder, by default, I'm seeing this is saved as 
UTF_8
encoding, however once I load the file in the next stage, the characters are not recognized. They displayed like this Th�or�t using the 
log.debug
Opening the file in Excel, Numbers and VSCode the characters display correctly, it is just in the iterator native method that the characters are wrong. I did some troubleshooting, I'm logging what I'm getting with the 
connection.download
which is something like this: 
{"type":"file.File","id":null,"name":"myfile.csv","description":null,"path":"myfile.csv","url":null,"folder":-1,"fileType":"CSV","isText":true,"size":752,"encoding":null,"isInactive":false,"isOnline":false}
So the encoding is 
NULL
originally but NetSuite set it as 
UTF_8
what for me seems right, before saving the file I'm also doing a 
getContents
and with the string returned I'm using a 
indexOf
to check for special characters but it returns 
-1
so they are not found. Do you have any suggestions to be able to read the CSV data in the right way? I have an approach to handle the special characters but I'm not able to perform it since I cannot read the values correctly from the beginning.
b
what did vscode say the encoding was
e
Well, actually I just realized VSCode is not able to read the characters, neither excel, just Numbers (MAC) can.
b
fair chance the file is not actually utf-8
you probably want to find what encoding numbers is using to read the file
✅ 1
b
@eminero did you drop the file in the SFTP folder? Can you download independently and validate if the contents are indeed encoded?
Try downloading it and saving it with TXT extension instead of CSV and see if that outputs the right contents. If it does then rename it to CSV and look again.
I had a similar case in which the file in the SFTP server didn't have an extension and it was downloading it with a screwed up encoding so saving it as TXT and then renaming worked for me
e
Awesome! Thanks for the suggestions both! I'm gonna keep trying
So, I'm gonna keep playing with this but just FYI and in case you have more suggestions, after running 
file -I myfilename
in the terminal, it turns out that it says: 
text/csv; charset=iso-8859-1
for some files and then 
text/csv; charset=us-ascii
for others. Does it mean the end users are kind of editing and saving the files using a different program? I was told they are generated automatically for another system and then, the end users just upload them to the SFTP Server, but I have my doubts about it.
a
umm you might want to try using ISO_8859_1 instead of utf-8 in NetSuite then
ascii base chars will be the same for either so that's less of an issue
alternatively tell whoever is generating the file that its 2023 and using unicode would be nice 😂
🤣 1
e
Thanks @Anthony OConnor I'm trying that right now, regarding the latter, well I imagine this is a legacy system and it would not be possible to get upgraded (if it works for them, then they will not touched haha)
@borncorp Once I edit the file and set the charset = UTF_8 in Numbers, then uploading the file to the file cabinet, the script is able to read the special characters, however when I try to set the encoding to UTF_8 before saving the file, then the content is not read correctly, I'm not quite sure if I will be able to do the update via scripting or if at the end, I need to manual user interaction to save the file in the correct encoding.
b
your file is really just binary data, except it uses an encoding to change it into text. numbers (or really any of the 3 options you gave) can guess the encoding to convert the binary to text, at which point you can do the opposite to change the text back into binary using whatever encoding you wish
that wont really help you in netsuite, you need to get the encoding correct in the first place
✅ 1
a
@eminero sorry for irrelevant question since you mention the SFTP
What authentication type you are using , I want to just check if it is same issue as we are facing
e
@battk that's what I wanted to double check to ask the client to save it correctly before uploading it to the SFTP.
@aaz it is not FTP, it is SFTP which is different to work in NetSuite, but I'm using the secret keys (private/public) to authenticate using the 
N/module
.
a
Copy code
connection = sftp.createConnection({
                    username: sftpUser,
                    url: serverURL,
                    directory: directory,
                    hostKey: hostKey,
                    keyId: keyId
                });
I am using N/SFTP module
e
What issue are you getting? In order to use SFTP module, you need to make sure your server is SFTP, if it is just FTP (not secure) then the module won't work and you need to go in other way.
a
it is OMS , it does work
I mean it was working still yesterday
and this connection was establish more than year back
so it is suddenly stopped working
for you have tried file.Encoding.WINDOWS_1252;
e
Yeah, I'm gonna try all the possible encoding supported and see if I got lucky. Related to your issue, try to connect to the server using Filezilla, it has an option to use private/public keys, so you are sure the credentials are still right and valid. However, I do not know yet what is the exact error message you are getting.
a
intresting thing is , I am able to access the sever using Winscp
raised this with NetSuite , lets see
✅ 1
13 Views
Open in Slack
PreviousNext
Powered by