Excel - Convert Task - VisualCron - Forum

Community forum

hjohnson
2019-04-01T15:28:38Z
I am in need of changing the Lf of a text file to be CrLf.
I have tried the regex replace function, excel convert / create and have not had luck.
Has anyone had success in this conversion?
Gary_W
2019-04-01T16:14:24Z
If you are moving a file from Unix to Windows, see if you can make use of the unix2dos command first.
If you are unable to make use of that, give this a try:
- read in your file
- create write task file
set line break to no line break
value is {REGEX(Replace|{TASK(PrevTask,StdOut)}|\n|
)}

Notice the carriage return embedded in there. This treats STDOUT from the previous task as one large chunk of text and replaces LF's.



hjohnson
2019-04-05T20:42:00Z
This worked for one of my files, but I am getting an error in argument for another. I think it could be that the file it works on is a text file. The one it doesn't work on is a pipe delimited csv. I need to get ride of the pipes and replace them with tabs but that can be done after changing the LF's to CrLf.

Or is there a better way to format the csv file that replaces both the pipes and the LF's?

Thanks for your help!
Gary_W
2019-04-08T14:04:18Z
Please show an example line and the command you are using.
hjohnson
2019-04-08T15:13:12Z
ok so the format of the file is:

data|data|data|data
data|data|data|data
data|data|data|data

I am using just "data" as the filler since the file has PHI in it, and I don't want to paste that out here. There is an LF at the end of the lines, in the file. Pipe delimited csv file.

I am reading the file in, to get the output, and then writing it with the value:
{REGEX(Replace|{TASK(930d34e6-66c4-435d-9fa9-1145b3c3efe1,StdOut)}|\n|
)}

The above should at least change the line feeds to CrLf's, like it does for the text file from my original post, but it does not.
I tried changing the pipes of the value to comma's in hopes that then I could specify to replace the pipes, but that did not work.
Gary_W
2019-04-08T15:25:34Z
I suggest you open this file in an editor that allows you to see the contents in HEX, to confirm your assumptions as to the contents so there is no doubt. When things go strange and not what you expect and don't make any sense, go back to the beginning and make sure everything is really what you think it is! I use GVIM for windows (I'm old-school heh) and it has an option (tools/convert to HEX) that lets you see the file in HEX so you will be able to exactly the characters you are dealing with. That's your starting point. Carriage returns will be HEX 0D (decimal 13) and line feeds will be 0A (decimal 10).

So for your example above you'd see (note the 0d0a at the end of the lines):

0000000: 6461 7461 7c64 6174 617c 6461 7461 7c64  data|data|data|d
0000010: 6174 610d 0a64 6174 617c 6461 7461 7c64  ata..data|data|d
0000020: 6174 617c 6461 7461 0d0a 6461 7461 7c64  ata|data..data|d
0000030: 6174 617c 6461 7461 7c64 6174 610d 0a    ata|data|data..


Do this with your actual file and see what is really terminating your lines.
hjohnson
2019-04-08T15:52:59Z
I converted the file from ASCII to HEX in notepad++. There are no 0D's, only 0A's.
Gary_W
2019-04-08T17:15:17Z
I believe I recall what to do from my own 2 year old post. The replace looks at the STDOUT blob of text as just that, not a line at a time. So, logically you want to replace every occurrence of a string of characters that ends with a LF with a CRLF. Not just the LF itself.
From this post: https://www.visualcron.c....aspx?g=posts&t=7062 

{REGEX(Replace|{TASK(PrevTask,StdOut)}|(.*)(\n.*)|$1
)}

The parenthesis defines a "remembered" group. So the regex means, look for and remember a string of all characters that are followed by a newline and the rest of the string. Replace with the string of all characters and a carriage return/line feed (since you are in the windows world). This is then repeated from that point as long as the match is found. You may need another carriage return at the end as well (see the other post). Note you have to press the Enter key for the carriage return to get the actual character. If you were to use \r then you would get the 2 character string of a backslash followed by the letter r as the "replace with" argument of the regex replace function allows only a string, not special shortcut characters.

Anyway, please have a look at the other post, then try this.
Gary_W
2019-04-09T18:28:17Z
I know what it is! The data that you are referring to in the {TASK(930d34e6-66c4-435d-9fa9-1145b3c3efe1|StdOut)} part of your function contains pipes as delimiters. The pipe is also the separator for the components of VC functions. You have to tell VC to use alternate character for its separator in the REGEX-REPLACE function else it doesn't know where the arguments end and the data begins (or vice-versa?) once the StdOut variable is resolved. To do that, put the alternate character you want to use before the function name, then use that character as the separator instead of the normal pipe. Here I'll use a comma:

{REGEX(,Replace,{TASK(a3cb4b61-e7e2-4ce3-9cd4-92a7334e25ed|StdOut)},(.*)(\n.*),$1
)}

BAM!
hjohnson
2019-04-09T21:21:29Z
Funny thing is that I had switched out the pipes for commas, in the function, in the "original" way. 🤣

I am going to try it with the groupings and see how it works. Just because I want to see. 😁
Scroll to Top