filter doubles in CSV

rice1973 · Post by **rice1973** » Wed May 19, 2010 6:16 am

Hi,Can i filter out doubles lines in a CSV by filtering in a certain column?Example; "3743","187.80.037","Terral","010037420110","20","99.95","99 .95" "3743","187.80.037","Terral","010037420110","20","99.95","99 .95" "3743","187.80.037","Terral","010037420110","20","99.95","99 .95" "3743","187.80.037","Terral","010037420110","20","99.95","99 .95"Can that be filtered on the first column, "3743"? Result should be only one line; "3743","187.80.037","Terral","010037420110","20","99.95","99 .95"

Post by **Oleg** » Wed May 19, 2010 7:12 am

Are repetitive lines going in succession?

You are forgetting that CSV is only text file and you can't apply SQL expression features.
But if the lines are going in succession I think that it's possible to do something.

rice1973 · Post by **rice1973** » Wed May 19, 2010 7:20 am

Hi,There a a few differences in the lines i only pasted the first 7 columns, i cannot change this in the SQL query. I need this done in the textfile.

Post by **Oleg** » Wed May 19, 2010 8:17 am

See the example below, please.
The task converts file C:\incoming\1.csv to C:\incoming\2.csv and remove all repetitive rows (by 1-st column, of course)

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************

[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|1380072790
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task943"
Hide=INTEGER|0
ID=INTEGER|1152474621
LocalVariables=STRING|"OLDVALUES,c_line,new_text"
LogOnAsUser=INTEGER|1
Name=STRING|"""Distinct rows"" in CSV file"
OnErrorTaskID=INTEGER|0
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
WriteGeneralLog=INTEGER|0

[Actions]
Action1=FOLDER
Action10=FOLDER
Action11=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 ""OLDVALUES"" with value """""
Params=FOLDER

[Actions\Action1\Params]
expand=STRING|"0"
varname=STRING|"OLDVALUES"

[Actions\Action10]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|-1
Name=STRING|"Show ""{NEWTEXT}"""
Params=FOLDER

[Actions\Action10\Params]
icon=STRING|"1"
msg0=STRING|"{NEWTEXT}"
msgcount=STRING|"1"
playsound=STRING|"0"
showmessage=STRING|"1"

[Actions\Action11]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|-1
Name=STRING|"Show ""{OLDVALUES}"""
Params=FOLDER

[Actions\Action11\Params]
icon=STRING|"1"
msg0=STRING|"{OLDVALUES}"
msgcount=STRING|"1"
playsound=STRING|"0"
showmessage=STRING|"1"

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

[Actions\Action2\Params]
expand=STRING|"0"
varname=STRING|"NEWTEXT"

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

[Actions\Action3\Params]
destvar=STRING|"C_LINE"
file=STRING|"C:\incoming\1.csv"
linecount=STRING|"0"
sourcetext=STRING|"0"

[Actions\Action4]
ActionID=STRING|"A_FLOW_IF"
Enabled=INTEGER|-1
Name=STRING|"If Then"
Params=FOLDER

[Actions\Action4\Params]
case=STRING|"0"
operator=STRING|"9"
type=STRING|"0"
value1=STRING|"{c_line(0)}"
value2=STRING|"{oldvalues}"

[Actions\Action5]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""NEWTEXT"" with value ""{NEWTEXT}{C_LINE}{EOL}"""
Params=FOLDER

[Actions\Action5\Params]
expand=STRING|"1"
varname=STRING|"NEWTEXT"
varvalue=STRING|"{NEWTEXT}{C_LINE}{EOL}"

[Actions\Action6]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""OLDVALUES"" with value ""{oldvalues}{C_LINE(0)}{EOL}"""
Params=FOLDER

[Actions\Action6\Params]
expand=STRING|"1"
varname=STRING|"OLDVALUES"
varvalue=STRING|"{oldvalues}{C_LINE(0)}{EOL}"

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

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

[Actions\Action9]
ActionID=STRING|"A_FILE_TEXTWRITE"
Enabled=INTEGER|-1
Name=STRING|"Create text file C:\incoming\2.csv"
Params=FOLDER

[Actions\Action9\Params]
fileexists=STRING|"0"
filname=STRING|"C:\incoming\2.csv"
line0=STRING|"{newtext}"
linecount=STRING|"1"
suppress=STRING|"0"

rice1973 · Post by **rice1973** » Wed May 19, 2010 9:52 am

Hi,Almost ok.. After the task al the lines appear in the file is one big chuck of text.The end of each line is now an "enter". This is now gone. So there is no layout anymore.p.s. i can ad an "end"symbol to each line wich you can filter on if that is easier?Please advice.......

Post by **Oleg** » Wed May 19, 2010 10:42 pm

See my task, please.
It adds end-of-line symbols ({EOL}) at the end of each line.

Also I have looked at my test files. Everything is OK.
It's an ordinary text files

rice1973 · Post by **rice1973** » Thu May 20, 2010 1:40 am

Hi,Checked again.. Still not working? Don't know why.Can i email you the CSV? So that you can take a look?ps; In the file are more then 1 double line.Example;"3743","187.80.037","Terral","010037420110","20","99.95","99 .95""3743","187.80.037","Terral","010037420110","20","99.95","99 .95""3743","187.80.037","Terral","010037420110","20","99.95","99 .95""3743","187.80.037","Terral","010037420110","20","99.95","99 .95""3745","188.11.303","Nice","010037420115","10","29.95","20 .95""3745","188.11.303","Nice","010037420115","10","29.95","20 .95""3745","188.11.303","Nice","010037420115","10","29.95","20 .95""3745","188.11.303","Nice","010037420115","10","29.95","20 .95""3745","188.11.303","Nice","010037420115","10","29.95","20 .95""3745","188.11.303","Nice","010037420115","10","29.95","20 .95""3745","188.11.303","Nice","010037420115","10","29.95","20 .95"Should output;"3743","187.80.037","Terral","010037420110","20","99.95","99 .95"
"3745","188.11.303","Nice","010037420115","10","29.95","20 .95"
It now outputs;"3743","187.80.037","Terral","010037420110","20","99.95","99 .95""3745","187.80.037","Terral","010037420110","20","99.95" ,"99 .95"The whole second line is different..

Post by **Oleg** » Thu May 20, 2010 2:06 am

My example works correctly.
I got the output:
"3743","187.80.037","Terral","010037420110","20","99.95","99 .95"
"3745","188.11.303","Nice","010037420115","10","29.95","20 .95"

Yes, you can mail me your CSV files and the task, please, to oleg (at) robotask.com

rice1973 · Post by **rice1973** » Thu May 20, 2010 2:29 am

Hi,Found the problem!! By a strange way the import from the task "forgot/ did not import" all the "{EOL}" string, it shure is in the file.Typed them manually and query workx! Bug in the program?

Post by **Oleg** » Thu May 20, 2010 2:49 am

Hm-m-m...
it seems, yes.

We'll investigate the problem.