Formatting Text in a Text File that includes variables

[fdputhuff]

I have run into an interesting issue when writing to a text file. Sorry for the long post.

Background:

I am taking a large Excel spreadsheet, removing the unwanted columns, putting the remaining columns in the right order, and getting them set up for filtering.

I then set four filters and read the required row of columns A, B, C, and D, putting them into their own variables. Then I put all the populated cells in Column E into a list.

So I end up with four variables and a list of numbers.

Now I need to get the following lines written / appended into a text file. The blue text are my variables

update ipsi.standard_scf
set key_eng = '%NewBEMS%' where key_eng = '%CurrentBEMS%' and scf_type = '%ScfType%' and i_std_stand_id in (select i_std_stand_id
from ipsi.standard, ipsi.standard_scf
where stand_id = i_std_stand_id
and corpus_code = '%Corpus%' and doc_number in (
'Number from the list',
'Number from the list',
'Number from the list',
'Number from the list'
));

I am trying to use Write Text to File instead of Send Keys, because I am having issues popping back and forth between the Excel sheet and Notepad++. Send Keys also seems slower and more prone to have errors depending on the speed.

Write Text to File only allows one line of text per command. (As far as I can figure out)

So I have put the entire block of text above in a variable, and send that variable to the Write Text to File.

It writes the text, but it adds unexpected line breaks. Like so...

update ipsi.standard_scf
set key_eng = '2367267
' where key_eng = '2753017
' and scf_type = 'CUSTODIAN
' and i_std_stand_id in (select i_std_stand_id
from ipsi.standard, ipsi.standard_scf
where stand_id = i_std_stand_id
and corpus_code = 'BPS
' and doc_number in (
'Column E list

',
'Column E list

',
'Column E list

',
'Column E list

'
));

My guess is that the percent signs, or at least the 2nd one in each variable, is seen as a special character that is causing this unwanted line break.

Any ideas on how to get my text file to ultimately look like:

update ipsi.standard_scf
set key_eng = '2367267' where key_eng = '2753017' and scf_type = 'CUSTODIAN' and i_std_stand_id in (select i_std_stand_id
from ipsi.standard, ipsi.standard_scf
where stand_id = i_std_stand_id
and corpus_code = 'BPS' and doc_number in (
'Column E list',
'Column E list',
'Column E list',
'Column E list'
));

Frank D. Puthuff
Dell Technology

[VJR · Accepted solution]

Hi Frank,

To see if I understood better, is this how you are doing it?

On doing this it generated the output as follows without any new line

Be noted that when you copy something from Excel it adds a new line while pasting.

So check if the variables itself are holding the newline values.

To test, write just the variable values to a Text file and since you have Notepad++, turn ON Show symbol -> Show all characters from the View menu. If you see carriage return line feed then it has a newline which is not visible except in an editor like this.

[fdputhuff]

So...if I do find that I have new line characters in the variables, how do I remove them?

[VJR]

Hi @fdputhuff 

The solution will depend on where that is happening and how you have designed the flow. If it is happening at the source while reading the data, or later in the flow, also are you copying the data directly from Excel or are you reading it into a data table and so on.

Also what did you find out after writing the variable values to notepad++ editor? 

[fdputhuff]

I am going to call this a resolved issue.

Your thought about the newline characters being added was spot on.

I was able to use the SendKeys command to write my SQL into NotePad++ including some back spaces to remove the newlines.

I then select all and copy the finished code, Get Clipboard text, and store that result into a variable, append the variable to my text file.

------------------------------------------------------------------------

Now...as far as how I am creating my variables. As the desktop flow is running, it has either the Excel document or Notepad++ open and set as the active window. In Excel, once I have filtered a section the way I need, I select a Cell and Read that selected Cell. This read creates a variable, which I am then using to write the SQL in Notepad++.

I hope this answers your question.

If there was some way to make the newline(s) go away without having to open Notepad++, that would be a much cleaner run for me. Bouncing back and forth between active windows really heightens the possibility of a failure. Mainly because I am not familiar with all of my options for making sure the correct window is activated.

[VJR · Accepted solution]

Hi Frank,

I was able to use the SendKeys command to write my SQL into NotePad++ including some back spaces to remove the newlines.

Not sure what is the reason for you to make use of Sendkeys and copy pasting actions when there are direct PAD actions to interact with Excel as well as Text files, unless you are doing some kind of Citrix automation.

if there was some way to make the newline(s) go away without having to open Notepad++, that would be a much cleaner run for me.

Read the contents of this text file using "Read text from file".

Use Regular expression for the newline/carriage return to be replaced with a blank character.

After running the Flow the new lines are removed in the Replaced text and this can now be written back to the Text file using "Write Text to File"

You can also use a space instead of the blank string in the "Replace with" shown above. 

Good luck.