filtering data 5 different sets

June 27, 2013 at 16:20:13
Specs: Windows 7
I came to a pretty complex problem which i decided to ask here since there are many who are very skilled in batch scripting and vbscript.
The problem is as follows:
We have 5 different sets of data which we'll name in the following order:
car:location:land:beach:table.
Now the problem is, all of that data is in 1 line in a .txt file, which will be like this:
mercedes:newyork:big:somebeach:small,car1:newjersey:small:anotherbeach:big,etc,etc,etc..
You get the point, each set of data will be separated by a comma, now the problem is, i want it to be filtered as follows:if location exists, ignore land and beach and just export the data:car:location:table. The other problem is, some may not contain land and beach and therefore will be in the following format if those two don't exist car:location:::table, now sometimes location will not exist where then, land and beach will so it will be in the following format:car::land:beach:table. Sometimes, location and beach and land will all exist and be in the format car:location:land:beach:table. In short, the logic to be used is:
if location doesn't exist, export car:land:beach:table to data.txt,
if beach and land don't exist, export:car:location:table,
if location exists and beach and land exists, export the following data:car:location:table.
Many thanks to the person who can achieve this in either vbscript or batch.

See More: filtering data 5 different sets

Report •


#1
June 27, 2013 at 18:01:15
Post a few lines of data.

=====================
M2 Golden-Triangle


Report •

#2
June 28, 2013 at 08:17:48
So you're trying to do . . . what, exactly? Remove duplicate colons?

How To Ask Questions The Smart Way


Report •

#3
June 28, 2013 at 13:06:33
I'm not really clear either, but I made this attempt:

::========= begin batchscript

@echo off & setlocal
for /f "tokens=*" %%a in (zzz) do call :xx %%a
)
goto :eof
:xx
call :yy "%1"
shift
if "%1" neq "" goto :xx
goto :eof

:yy
set jj=%1
set jj=%jj::=","%
call :zz %jj%
goto :eof

:zz
set oo=%~1
set five=%~5
if "%~2" equ "" (
set oo=%oo%,%~3,%~4
goto :xit)

set oo=%oo%,%~2
if "%~3%~4" equ "" goto :xit
if %2 equ "" goto :eof
if %3 equ "" goto :eof
if %4 equ "" goto :eof
goto :xit

:xit
set oo=%oo%,%five%
echo output will be: %oo%
::enable the following line to activate output. "NEWFILE" DESTROYED!
rem >>newfile echo %oo%

::========== end batchscript

some of the outcomes would be different, depending on the order of evaluation, due
to overlaps of qualifications. f/e: if fields 2,3 and 4 were all empty, either 2-null or 3-null-4-null outcomes would qualify.


Report •

Related Solutions

#4
June 29, 2013 at 00:49:40
Hi nbrane,

This one gives me that old sinking feeling.

As Adrian Cronauer says, you KNOW what I mean.

=====================
M2 Golden-Triangle


Report •

#5
June 29, 2013 at 10:51:17
Hi M2: Ha ha! yes, I imagine I "stepped right into it" alright. :-D
As long as I've been at this, you'd think I'd have sense enough to ask for a healthy sample of supply data as well as a sample of desired output. :-(

Report •

#6
June 29, 2013 at 11:10:56
Hi nbrane tried your script, didn't work :) but thanks for the attempt.
The data is not necessarily in one line, i'd like it to be able to grab the data from a .txt file.
Here's sample data:
Bob:59438dnsk4905830:::example1@,cvdxc:49djdns4ks84:::example2@,toms:dksi48593jsks49595u:::example3@,ael:4986969sndbdklsodk:5443sdsdaad343da24324:N{}<.??{}>:.dk4o204899_=_+sdk:examples_""@,John::49384893:9349DJ,SKkdsnk{{}>??>>??P$d;;s':john1345@
The desired output:
1.Bob:59438dnsk4905830:example1@
2.cvdxc:49djdns4ks84:example2@
3.toms:dksi48593jsks49595u:example3@
4.ael:4986969sndbdklsodk:examples_""@
5.John:49384893:9349DJ,SKkdsnk{{}>??>>??P$d;;s':john1345@

Sometimes, beach may contain the character : and , as well, so that makes it even harder to accomplish.


Report •

#7
June 29, 2013 at 12:28:40
Well, I was "asking for it". (in other words, in giant caps: "I TOLD YOU SO HA HA!!!")

Nah, batch won't handle the "system" characters (at least, I'm not going to try to tame them). If it weren't for your last statement, I might try vbscript, but there's no way to mix delimiters in with your data and be able to pull it out, unless you create fixed-length fields (thereby eliminating the delimiters). Whatever is generating this mess is what needs to be fixed, before any sense can be made of it.


Report •

#8
June 29, 2013 at 12:35:16
@nbrane
I'll try to work on a solution later if no one does this, it can be done in batch although it's a lot of work.
The delimiters are a bug i know, but i think i can overcome that.

Report •

#9
June 30, 2013 at 09:53:15
Have you considered importing a sheet in excel from a renamed csv file, and using comma as the deliminator?

:: mike


Report •

#10
July 1, 2013 at 01:39:01
This does not lend itself to bat processing:

5443sdsdaad343da24324:N{}<.??{}>:.dk4o204899_=_+sdk:

BTW, anybody got a link to Bruce Guthrie's CHANGE. I had it on my site but the server pooped out.

=====================
M2 Golden-Triangle


Report •

#11
July 1, 2013 at 18:44:56

Report •

#12
July 2, 2013 at 01:02:43
hi nbrane,

got it

N2 says hi

=====================
M2 Golden-Triangle


Report •

Ask Question