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
Replace file content in a CSV
Re: Replace file content in a CSV
How do you want to distinguish unnecessary line breaks from necessary ones?...but cant see how to remove the un-needed line breaks?
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.
Oleg Yershov
Re: Replace file content in a CSV
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
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
Re: Replace file content in a CSV
The problem is that any line break is at the end of the line. CSV row is the single line in the textSurplus line breaks are any line breaks that are not at the end of a row
I've sent a copy of the file
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 Yershov
Re: Replace file content in a CSV
The task for correction is below. But please note that it is not universal algorithm. But it will work in your case.I've sent a copy of the file
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"
Oleg Yershov