Replace file content in a CSV

[deesloop]

I'm having issues importing a CSV through MS VBA in excel.
Turns out folk have used commas within cells & also used linebreaks too.
I'd like to replace all commas in the cells and remove the line breaks

With the file being a CSV I cant just replace commas so thought of

Replacing "," with "!"
Then replacing all commas with a space
Then replacing "!" with "," to return to the file to a CSV format

Think I can get that to work with regexp and a folder watch trigger but cant see how to remove the un-needed line breaks?

Am I going about it the right way?
I have an old version (6.8) which may be hamstringing me

[Oleg]

...but cant see how to remove the un-needed line breaks?

How do you want to distinguish unnecessary line breaks from necessary ones?

Could you send the example of such CSV-file to me? I want to look at this.

Also note than since version 9.6 we have added CSV plugin. Actions allows you easily change CSV format and perform other transformations.

[deesloop]

Surplus line breaks are any line breaks that are not at the end of a row
I've sent a copy of the file

I would upgrade but support for VBA is very important for me and I dont want to take the risk that something will break.
It toook me ages to get the routine working and it has done so for at elast 5 years so I dont want to drop VBA support

Thanks in advance

[Oleg]

Surplus line breaks are any line breaks that are not at the end of a row
I've sent a copy of the file

The problem is that any line break is at the end of the line. CSV row is the single line in the text
If some field contains line break this means that format is broken. CSV format does not allow line breaks in the field.

I'm afraid that it's easier to correct the text manually in a text editor before automatic processing.

[Oleg]

I've sent a copy of the file

The task for correction is below. But please note that it is not universal algorithm. But it will work in your case.
Algorithm is simple: if line of the test does not begins with quote char (") then is suppress end-of-line symbols in new text.
Next you can load this text into EXCEL without problem

Code: Select all

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|444033236
Comment=STRINGLIST
ContinueOnError=INTEGER|0
DisableOnError=INTEGER|0
DoNotStopWhenShutdown=INTEGER|0
ExternalName=STRING|"Task1453"
Hide=INTEGER|0
ID=INTEGER|1579302285
LogOnAsUser=INTEGER|1
Name=STRING|"CSV correction (remove extra linebreaks)"
OnErrorTaskID=INTEGER|0
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
StepPause=INTEGER|0
ToLog=INTEGER|3
UnicodeFormat=INTEGER|1
WriteGeneralLog=INTEGER|0

[Actions]
Action1=FOLDER
Action10=FOLDER
Action11=FOLDER
Action12=FOLDER
Action13=FOLDER
Action14=FOLDER
Action15=FOLDER
Action16=FOLDER
Action17=FOLDER
Action18=FOLDER
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
Action7=FOLDER
Action8=FOLDER
Action9=FOLDER

[Actions\Action1]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""Source"" with value ""D:\systemp\Downloads\80.csv"""
Params=FOLDER

[Actions\Action1\Params]
_rt_variables_produced=STRING|"Source"
expand=STRING|"0"
linecount=STRING|"1"
varname=STRING|"Source"
varvalue=STRING|"D:\systemp\Downloads\80.csv"

[Actions\Action10]
ActionID=STRING|"A_FLOW_IF"
Enabled=INTEGER|-1
Name=STRING|"If Then // if begins with """
Params=FOLDER

[Actions\Action10\Params]
case=STRING|"0"
connection=STRING|"0"
count=STRING|"1"
operator=STRING|"0"
type=STRING|"0"
value1=STRING|"{pos}"
value2=STRING|"1"

[Actions\Action11]
ActionID=STRING|"A_MISC_COMMENT"
Enabled=INTEGER|-1
Name=STRING|"//put line break"
Params=FOLDER

[Actions\Action11\Params]
comment=STRING|"put line break"

[Actions\Action12]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""txt"" with value ""{txt}{eol}{line}"""
Params=FOLDER

[Actions\Action12\Params]
_rt_variables_produced=STRING|"txt"
expand=STRING|"1"
linecount=STRING|"1"
varname=STRING|"txt"
varvalue=STRING|"{txt}{eol}{line}"

[Actions\Action13]
ActionID=STRING|"A_FLOW_ELSE"
Enabled=INTEGER|-1
Name=STRING|"Else"

[Actions\Action14]
ActionID=STRING|"A_MISC_COMMENT"
Enabled=INTEGER|-1
Name=STRING|"//put space"
Params=FOLDER

[Actions\Action14\Params]
comment=STRING|"put space"

[Actions\Action15]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""txt"" with value ""{txt} {line}"""
Params=FOLDER

[Actions\Action15\Params]
_rt_variables_produced=STRING|"txt"
expand=STRING|"1"
linecount=STRING|"1"
varname=STRING|"txt"
varvalue=STRING|"{txt} {line}"

[Actions\Action16]
ActionID=STRING|"A_FLOW_ENDIF"
Enabled=INTEGER|-1
Name=STRING|"End If"

[Actions\Action17]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"

[Actions\Action18]
ActionID=STRING|"A_FILE_TEXTWRITE"
Enabled=INTEGER|-1
Name=STRING|"Create text file {Target}"
Params=FOLDER

[Actions\Action18\Params]
encode=STRING|"0"
fileexists=STRING|"0"
filname=STRING|"{Target}"
line0=STRING|"{txt}"
linecount=STRING|"1"
suppress=STRING|"0"

[Actions\Action2]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""Target"" with value ""D:\systemp\Downloads\80-1.csv"""
Params=FOLDER

[Actions\Action2\Params]
_rt_variables_produced=STRING|"Target"
expand=STRING|"0"
linecount=STRING|"1"
varname=STRING|"Target"
varvalue=STRING|"D:\systemp\Downloads\80-1.csv"

[Actions\Action3]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""txt"" with value """""
Params=FOLDER

[Actions\Action3\Params]
_rt_variables_produced=STRING|"txt"
expand=STRING|"0"
linecount=STRING|"0"
varname=STRING|"txt"

[Actions\Action4]
ActionID=STRING|"A_LOOP_TEXT"
Enabled=INTEGER|-1
Name=STRING|"Text Loop"
Params=FOLDER

[Actions\Action4\Params]
_rt_variables_produced=STRING|"line"
delimiter_no=STRING|"0"
destvar=STRING|"line"
file=STRING|"{Source}"
linecount=STRING|"0"
quote_char=STRING|"0"
sourcetext=STRING|"0"
strict_delim=STRING|"1"

[Actions\Action5]
ActionID=STRING|"A_FLOW_IF"
Enabled=INTEGER|-1
Name=STRING|"If Then // 1-st line save as it is"
Params=FOLDER

[Actions\Action5\Params]
case=STRING|"0"
connection=STRING|"0"
count=STRING|"1"
operator=STRING|"7"
type=STRING|"0"
value1=STRING|"{txt}"

[Actions\Action6]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""txt"" with value ""{line}"""
Params=FOLDER

[Actions\Action6\Params]
_rt_variables_produced=STRING|"txt"
expand=STRING|"1"
linecount=STRING|"1"
varname=STRING|"txt"
varvalue=STRING|"{line}"

[Actions\Action7]
ActionID=STRING|"A_LOOP_CONTINUE"
Enabled=INTEGER|-1
Name=STRING|"Continue"

[Actions\Action8]
ActionID=STRING|"A_FLOW_ENDIF"
Enabled=INTEGER|-1
Name=STRING|"End If"

[Actions\Action9]
ActionID=STRING|"A_STR_POS"
Enabled=INTEGER|-1
Name=STRING|"STR Pos"
Params=FOLDER

[Actions\Action9\Params]
_rt_variables_produced=STRING|"pos"
case=STRING|"0"
from=STRING|"1"
search=STRING|""""
source=STRING|"{line}"
variable=STRING|"pos"