# Automate sort and strip of Excel file? [Moved from MS Office]



## Mid_Life_Crisis (Jan 15, 2010)

Working with a tab separated excel table. What is the simplest, cleanest way to automate deletion of all records with a zero value in a particular column and append the results to a table in a second file?


----------



## Dragoen (Apr 10, 2009)

*Re: Automate sort and strip of Excel file?*

Hey Mid_Life,

If you don't have to stay within Excel, you can link the tables into an Access database, run a query to append the records with a value >0 in that column into the second table. You can also delete those other rows in the first table if that is really necessary.

Just an option,


----------



## Mid_Life_Crisis (Jan 15, 2010)

*Re: Automate sort and strip of Excel file?*

There is no guarantee that the individual using it will have Access, but you did give me an idea on how to handle this. Importing them into a table would be a snap with a little standalone VB or C# routine. I could pull in the three lines I need from the small file then add the lines with >0 to the same table and finally write the results to an appropriately named file.

Thank you.


----------



## Glaswegian (Sep 16, 2005)

*Re: Automate sort and strip of Excel file?*

A simple macro in Excel would also work a treat.


----------



## RSpecianjr (Jan 20, 2010)

*Re: Automate sort and strip of Excel file?*

Just to add to Glaswegian. Excel macros are written in VB for Applications, so if you know VB, VBA is a cinch.


----------



## Mid_Life_Crisis (Jan 15, 2010)

*Re: Automate sort and strip of Excel file?*

Does VBA require Office to be installed? The files in question are not actual Excel files. They are tab delimited, which means they open nicely in Excel for viewing and manipulating, but there is no requirement that the individual using them have Excel on their computer.


----------



## Dragoen (Apr 10, 2009)

*Re: Automate sort and strip of Excel file?*

If there is no guarentee the user will have any specific office product then you will need to write a stand-alone application (Windows script, C#, etc) and distribute it as needed. What's the topography you are working on, LAN, WAN, Intranet, Internet?


----------



## RSpecianjr (Jan 20, 2010)

*Re: Automate sort and strip of Excel file?*

Hey guys,

Excel has VBA installed in it, you do not need the full microsoft office to use it.

As long as the person that edits the file has Excel, they would be able to use VBA to edit/append the cvs file.

This method is super easy and most of it can be recorded using a macro recorder.

Ciao,

Robert D. Specian Jr.


----------



## Mid_Life_Crisis (Jan 15, 2010)

*Re: Automate sort and strip of Excel file?*



Dragoen said:


> If there is no guarentee the user will have any specific office product then you will need to write a stand-alone application (Windows script, C#, etc) and distribute it as needed. What's the topography you are working on, LAN, WAN, Intranet, Internet?


They are part of a LAN, but all activity will be confined to a specific workstation.
I'm a dabbler at best. Fortunately this is a simple program that is very similar to one I wrote a couple of years ago. I'll muddle through. VB has a built in parser. The logic is pretty simple, I think. It's a small file, so it doesn't have to be hyper-efficient. This should do it.

Open output file.
Open first input file.
Read predetermined number of lines I know I will need and write to the output file.
Open second input file.
Parse it one line at a time until EOF into a one record data structure.
If the particular data field has greater than zero value,
write line to the output file.
If not,
Ignore the line
Close all files.
Rename files I want to keep with a date/time stamp for backup.
Close program.

If anybody has a link to sample code in VB or C# similar to this to give me a jump start, I'll be very grateful. I'm not sure if I can find my old program code.


----------



## RSpecianjr (Jan 20, 2010)

*Re: Automate sort and strip of Excel file?*

Hey,

If you can post the files, or mock files, we can write the code for you.

We would also need to know where the files are located, meaning full directory name etc.

Also, if you notice in the bottom left hand corner of Excel, there should be a little button to start recording a macro. If you click on that (name it using the popup box that comes up) and then go through the steps that you want, it will record that for future use.

Ciao,

Robert


----------



## Mid_Life_Crisis (Jan 15, 2010)

*Re: Automate sort and strip of Excel file?*



RSpecianjr said:


> Hey,
> 
> If you can post the files, or mock files, we can write the code for you.
> 
> ...


I can't use a "record the steps" macro because the second file won't always be the same length and I can't count on Excel being on the computer.
I would prefer a push in the right direction and finish it up myself, because I'll learn a lot more that way.


----------



## RSpecianjr (Jan 20, 2010)

*Re: Automate sort and strip of Excel file?*

Hmm, I'm not sure how you are going to do it if Excel isn't on the computer. I'd suggest checking first. If not, it will have to be some other language. Can't really help you there... but we have a forum for programming that should be able to.

Find out if Excel is on the computer, if it's not, then we can move this thread over to the programming forum where they are more suited for it.

Ciao,

Robert


----------



## Mid_Life_Crisis (Jan 15, 2010)

*Re: Automate sort and strip of Excel file?*

Move it to programming at this point.
Thank you all for the suggestions.


----------



## Ninjaboi (Dec 27, 2010)

Visual Basic for Applications (VBA) Forum

That link should prove to be useful. It's a forum specifically for VBA . Several hundreds of questions asked that you could search and attempt to locate what your trying to do. You could also post a question yourself if you'd like.

Currently ( as far as I know ) we have no VBA scripters at this forum. If we do, they are hiding under rocks lol. I know one or two who use'd' VB, so they'd be your best bet if nobody can help you at the forum I suggested ( though they obviously should, since it's specific to VBA ).


----------



## Mid_Life_Crisis (Jan 15, 2010)

Thank you for the link, but my understanding of VBA is that you need to have Office installed to use the script and I cannot count on that being the case. It will have to be VB or C# or something along those lines.


----------



## Ninjaboi (Dec 27, 2010)

Ah, Ok. So your requiring "...to write a stand-alone application (Windows script, C#, etc) and distribute it as needed." right? That way you can simply put your product in it and be able to distribute it.


----------



## Mid_Life_Crisis (Jan 15, 2010)

It isn't a product so much as a tool for use with a product. I work in support and Development is not responsive to requests like these because they are too busy working on new product development. I have found in the past that it saves me a great deal of time and allows me to get more done if I have automated solutions for customers instead of having to hold their hands. I can usually accomplish this with batch files, but this one is more involved than that. I need to merge two files together, ideally stripping any records with a zero value in a particular field from the second file before or during the merge.


----------



## Ninjaboi (Dec 27, 2010)

So upon merging them, you'd like anything in the second file's fields to be changed to zeros, and to follow up on the merging right?

For example, you have two files that have the following inside the fields:

---File01---
Jane: 6
Mark: 3
Dean: 9
Sam: 1

---File02---
Chon: 4
Ron: 7
Ben: 11
Isaac: 8

That is before the merge, after the merge, there should be only one file with the following in fields:

---File01---
Jane: 6
Mark: 3
Dean: 9
Sam: 1
Chon: 0
Ron: 0
Ben: 0
Isaac: 0

Is this correct from how I'm thinking? The names are organized in their own fields vertically, and to the values are in a field next to each name. Your just wanting to add the first fields so you don't have to type it all again, but you'd like their values to be zero instead. ( If this is way off, please tell, as I'm just trying to understand the problem ). I'd of done a simply 'Copy' and 'Paste' situation with what I said, then go back and change the zeros. However, seeing as you want it automated, I'm guessing there is many more than a few fields to add and change.


----------



## Mid_Life_Crisis (Jan 15, 2010)

The first two lines I'm copying in from file number 1 are header records.
The second file contains the actual data. There are several columns of data. If one particular field in a record is zero, I want to skip that record, that is, not copy it over to the new file. 
I have decided that it is not the end of the world if the zero data comes over, so if we can forget about that part and do this with a batch, that would be wonderful. 
I found this via web search, but the problem is that one of the header lines starts with an exclamation point, which is a special character in batch files, so it gets skipped. If I can get around that, I'll be okay.

@echo off & setLocal enableDELAYedeXpansion
set N=
for /f "tokens=* delims= " %%a in (source.txt) do (
set /a N+=1
if !N! gtr 2 goto :done
>> output.txt echo.%%a
)
:done

I'm not 100% sure how that all works, but I can mostly follow it. How do I get the ! to be included at the beginning of the first line? 
Can you echo a character without a CR or LF? Then I could echo the ! to the file first, followed by the routine I pasted above to get the first couple of lines.


----------



## Ninjaboi (Dec 27, 2010)

Honestly, I have hardly even looked at batch scripts. I do have a bookmark that I keep in case I ever needed to look up information:

Here.

As far as me helping you, that probably won't be able to happen unless I start learning it. However, from what you've told me, this seems to be a few small things your wanting done, simply puttting them together. There is a scripting forum that should be able to solve this easily.

Here.

Sorry I keep pointing you to other locations. We here at TSF don't have many programmers/scripters, so the few that we do have usually only know a few scripting/programming languages. I know C-styled languages + Java, I've seen others familiar with VB and VBS here at the forum. However, I'm not sure we have any that use batch fluently. Of course there are people in the world that do, and that's why I direct you to them. TSF is a forum filled with people volunteering their talents to help others. Therefore talents can sometimes become small in a certain area, programming being just that case.

You should have good luck on the forum/page I linked above. Hopefully TSF will grow and gain more knowledge from each person that joins to help. Sorry for not being much help for you. Maybe someone else on the forum does know how to help you and will, who knows.


----------

