Is there a way in VisualCron to reference a .CSV report file, then open the file in Notepad/Textpad and save it in Unicode so that we can prevent Excel from corrupting symbols often found in files using a language other than English?

Is there a way in VisualCron to reference a .CSV report file, then open the file in Notepad/Textpad and save it in Unicode so that we can prevent Excel from corrupting symbols often found in files using a language other than English?

I do not know if i'm understanding your question correctly, but natively I am not aware of a way to basically convert a file from ANSI/etc... into Unicode.

You could always try a powershell method (Powershell task with these 2 lines as the code) such as this:

$Content = get-content c:\path\to\source.csv
$Content | out-file -filepath c:\path\to\destination.csv -encoding Unicode

This will read in (as text, it won't care if its CSV or whatever, its just strings) and then output the file in Unicode format.

Brian

Hi Brian,
Thank you for the info, however we are on version 4 of VC so we don't have that task type. I did try to take your code and copy it to a .vbs file and use the Execute Script but that didn't work either :(

And just to reconfirm what I'm trying to do. I have an application that creates an export file of type .csv of product descriptions which can be in English, French, Chinese, etc. If we open the report in Excel the product descriptions are corrupted so we need a way to open the .csv file in Notepad and then save it in Unicode and we'd like to do that automatically before a user accidentally opens the file in Excel and then corrupts it which ends up delivered to our client who is unhappy.

Jane

Hi Brian,
Thank you for the info, however we are on version 4 of VC so we don't have that task type. I did try to take your code and copy it to a .vbs file and use the Execute Script but that didn't work either :(

And just to reconfirm what I'm trying to do. I have an application that creates an export file of type .csv of product descriptions which can be in English, French, Chinese, etc. If we open the report in Excel the product descriptions are corrupted so we need a way to open the .csv file in Notepad and then save it in Unicode and we'd like to do that automatically before a user accidentally opens the file in Excel and then corrupts it which ends up delivered to our client who is unhappy.

Jane

Any chance you could attach an example file that doesn't have any proprietary info in it?

And... version 4... hm. You could use the execute task, but to use powershell you'd have to do it a little differently:

Command: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Arguments: c:\path\to\script.ps1

Save a file named c:\path\to\script.ps1 (pick your path) and put the 2 lines of code I gave you before inside that file.

That way it'll launch powershell and use the script referenced. See if that works. Now... version 4, was that not back when upgrades were for 'life'? I highly recommend upgrading to 6.x as the options just get better and better. We still have over 140 jobs running on 4.6 but are slowly migrating them to a new 2008 server running 6.1.4 after testing them all (new server is 2008 R2)


Brian

The forum won't allow me to attach a .csv file, so I just updated the file extension on my attachment from .csv to .txt. Our customer has a value of È within a description, if you open this file in Excel it changes it to ├ê so we need to be sure to take the .csv extract and save it in unicode to prevent this before delivery to our client.

The forum won't allow me to attach a .csv file, so I just updated the file extension on my attachment from .csv to .txt. Our customer has a value of È within a description, if you open this file in Excel it changes it to ├ê so we need to be sure to take the .csv extract and save it in unicode to prevent this before delivery to our client.

As far as I can tell, my original suggestion should work. I saved your testExport.txt file as d:\VisualCronTemp\testExport.csv

I then ran the following commands in powershell:

$Content = get-content d:\VisualCronTemp\testExport.csv
$Content | out-file -filepath d:\VisualCronTemp\Output.csv -encoding Unicode

I was still able to read the CSV file in using powershell:

import-csv d:\VisualCronTemp\Output.csv

So, I then created a PS1 file in the temp folder:
D:\VisualCronTemp\Convert.ps1 containing the 2 lines of code in blue above

Using my VC server running version 4.6.17 (yeah, I should really upgrade it haha, but i'm moving everything slowly over to the new server running VC 6.1.4) I went ahead and created a job with 1 task, an Execute Task, which I did a screenshot of and that is attached.

Command:
c:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Arguments:
D:\VisualCronTemp\Convert.ps1

Start In:
D:\VisualCronTemp\


Ran the job, and it did exactly as I expected. Hopefully this works for you.
Oh, and if you haven't yet installed powershell, do so, and you'll also need to make sure and set the executionpolicy to 'RemoteSigned':

Launch PowerShell
type this:
set-executionpolicy remotesigned

If prompted, select Yes.

Brian

Thanks Brian for the info. I'm not sure I can get the Powershell software added to the server; however I'll see if there's a MS-DOS command equivalent that I can put in a .bat file. We have to do that quite often here (use MS-DOS in a .bat file) since we don't have Version 6 yet.

Thanks Brian for the info. I'm not sure I can get the Powershell software added to the server; however I'll see if there's a MS-DOS command equivalent that I can put in a .bat file. We have to do that quite often here (use MS-DOS in a .bat file) since we don't have Version 6 yet.

If you're running Server 2003, i'd get that installed. There's no reason a Server OS shouldn't have it.

Anyways, You could use VB Script.

You can put the following code in a VBS file and call that instead.

Command:
cscript c:\VisualCronTemp\Convert.vbs

Just change the file name you want to convert. It does replace it, btw, so if you want to test on a real file, make sure you have a backup copy :)



Dim system, file, contents, newFile, ts, FileToConvert
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

FileToConvert = "d:\VisualCronTemp\testExport.csv"

Set system = CreateObject("Scripting.FileSystemObject")
Set file = system.GetFile(FileToConvert)
Set ts = file.OpenAsTextStream(ForReading, TristateUseDefault)
contents = ts.ReadAll()
ts.Close

system.CreateTextFile FileToConvert
'Create a file
Set newFile = system.GetFile(FileToConvert)
Set ts = newFile.OpenAsTextStream(ForWriting, TristateTrue)
ts.Write contents
ts.Close

Hopefully this works. It did at quick glance, though I didn't compare the powershell output to this.

Brian

I just compared them. I think the VBS method might jack things up a little. Though i'm not sure how the output SHOULD look.

I'm still going to highly recommend powershell :)
=====================
EDIT: I did what I should have done and used a program to compare differences in the files.
Powershell Result = Notepad Result
VBS = not clean, characters changed
=====================

Brian

Hi Brian,
It did come close however its still not liking that tick mark over the E which is pretty common for our Canadian (Quebec) customers. It changed things to an A with a symbol. I'll see what I can do about the PowerShell software. Thanks for your time, I really appreciate it. I'll keep looking at VB/VBA to see if I'm missing something 🙂

Hi Brian,
It did come close however its still not liking that tick mark over the E which is pretty common for our Canadian (Quebec) customers. It changed things to an A with a symbol. I'll see what I can do about the PowerShell software. Thanks for your time, I really appreciate it. I'll keep looking at VB/VBA to see if I'm missing something :)

You're welcome. I've been searching and searching for a good way to do it via VBS and nothing is obvious. Amazing how PowerShell can do it in 2 lines haha.

As an FYI, for anyone else reading, PowerShell is treated as a microsoft update that can get applied to 2003 servers that provides a new command line/scripting environment. It comes preinstalled on Server 2008.

It is entirely possible its already installed. You can tell for sure by clicking START--Run. And then just typing 'powershell' It will either open a command prompt or it'll complain. Anyway....

Here's the link to download PowerShell v1.0 for 2003/XP:
http://support.microsoft.com/kb/926139/  (Downloads are about halfway down the page)

PowerShell 2.0 is out as well (3.0 is almost out haha), but v1.0 should be all you need to something basic like this to work.

I'm personally giving up on VBS, at least as it relates to this.... 🙂 I can do alot with it, but i'm SOOOOO much more comfortable in PowerShell these days.

Brian

Thanks Brian. I sent in a support ticket this am to try to get it installed. I really appreciate your help, you went way above and beyond helping us out!

No problem at all. I find these forums keep me on my toes. I usually use the questions here to help/force me to learn new things. So far, its working. haha

Also, not that 3rd party PAID (yuck!! haha) utilities are somewhere I like to send people, but there is a conversion utility named "Character Set Converter" that is command line and had the same result as Notepad and Powershell, but it runs in a DOS Command prompt:

http://www.kalytta.com/tools.php 

cscvt.exe testExport.csv /dcs=UTF-16 /output=.\Output.csv

Brian

I created a Feature Request for 'encoding'. FYI

http://www.visualcron.co...aspx?g=posts&m=13491