Scripting help?

[ddrueding]

Not being able to solve this problem should cause my geek card to be revoked, but I can't figure it out.

I have a spreadsheet ("names.xls") with a single column and a bunch of rows. Each cell contains text that includes spaces, numbers, commas, etc. What I need is to copy a file "sample.xls" into a folder indexed by the first letter of the cell and re-name the file the contents of the cell.

For example, if some "names.xls" rows said this:

Thomas, Michael David 1234345
Van Der Beek, Bob 13245987
Zoe, Cleo 1234876

Than I would need copies of the "sample.xls" file in folders "T", "V", and "Z" called "Thomas, Michael David 1234345.xls", "Van Der Beek, Bob 13245987.xls", and "Zoe, Cleo 1234876.xls" respectively.

I suspect that this will involve exporting the spreadsheet to a txt file and running a batch file against it, but what to use as a delimiter, and how to put it together eludes me.

Help please?

 

[Howell]

Send me a link to the original and I'll hit you back tomorrow.

 

[Handruin]

I'm assuming the file "Thomas, Michael David 1234345.xls" only contain that person's information? Same would be true for other xls files?

Basically the file names.xls will generate individual xls files with the user's data. Will the individual files need their data broken into new columns?

Why do you need to use a delimiter?

 

[ddrueding]

I'm assuming the file "Thomas, Michael David 1234345.xls" only contain that person's information? Same would be true for other xls files?

Basically the file names.xls will generate individual xls files with the user's data. Will the individual files need their data broken into new columns?

Why do you need to use a delimiter?

Nope. Every copy of "sample.xls" will be identical, and contain a complete copy.

 

[Handruin]

Nope. Every copy of "sample.xls" will be identical, and contain a complete copy.

If they're all the same data content, you're basically just creating a copy for each person in a folder which starts with the letter of their last name?

I have code for you which can do most if not all of this. I was able to get something together which will read names.xls, extract every row as a string and then break it into the name and number separately. It also extracts the first letter from the last name to use to create the folder and copy a version into the folder. Is that what you were looking for?

 

[ddrueding]

That is exactly it.

 

[Handruin]

Attached is a bin and the source code so you can look through and see what I did if you don't want to trust the binary I built. You can easily get the free Microsoft visual studio free version and build your own copy of it if you need to. Others can also look it over to reassure you I've not coded anything shady.

To run this, you will need to do the following:

Argumrnets missing.
Arg1 = Output Location
Arg2 = Source Excel File
Arg3 = Excel WorkSheet name

Example: ExcelCopy.exe c:\temp c:\temp\names.xls Sheet1

This will produce the following:

c:\temp\T
	| Thomas, Michael David 1234345.xls

c:\temp\V
	| Van Der Beek, Bob 13245987.xls

c:\temp\Z
	| Zoe, Cleo 1234876.xls

I forgot to add that you'll need .net installed for this to run. It's a C# application. It also makes use of the Microsoft Office tools API/package, so you may need to have office installed on the machine in which this runs on. I can't redistribute the dll files (at least I don't think so). The referenced libraries are:

Microsoft.Office.Interop.Excel
(Microsoft.Office.Interop.Excel.dll)

Microsoft.Office.Tools.Excel
(Microsoft.Office.Tools.Excel.dll)

 

[ddrueding]

Thanks Handruin!

 

[ddrueding]

Handruin,

Finally got to testing yours...seems to be missing something?

I'm trying to copy and rename an excel spreadsheet, but not the one with the names...a different one. There doesn't seem to be room for an argument that would specify the file to be copied.

It does function exactly as needed with regard to the folder structure and the file copying, however.

 

[ddrueding]

Also, the file that needs to be copied is an .xlsx file with spaces in the name. Not sure if that matters or not...

 

[Handruin]

Handruin,

Finally got to testing yours...seems to be missing something?

I'm trying to copy and rename an excel spreadsheet, but not the one with the names...a different one. There doesn't seem to be room for an argument that would specify the file to be copied.

It does function exactly as needed with regard to the folder structure and the file copying, however.

I must not understand a step somewhere in your process. I thought you were trying to read from a file called names.xls and generate folders with a copy of that file. What is this other file supposed to do in relation to the script? Can you list out the steps again in more detail and I can alter the script to do what you need if you still want it?

Also, the file that needs to be copied is an .xlsx file with spaces in the name. Not sure if that matters or not...

Spaces in the name shouldn't matter, you would need to enclose the name in quotes when passing as an argument.

Example: ExcelCopy.exe c:\temp "c:\temp\my names file.xlsx" Sheet1

 

[ddrueding]

I must not understand a step somewhere in your process. I thought you were trying to read from a file called names.xls and generate folders with a copy of that file. What is this other file supposed to do in relation to the script? Can you list out the steps again in more detail and I can alter the script to do what you need if you still want it?

Nearly.

1. Read from a file called names.xls
2. Generate folders A-Z
3. Rename a different file, "Master Competency.xlsx" and put it in the folders.

What you are going now is exactly right, except that the file I want copied is different than the file with the names.

 

[Handruin]

Nearly.

1. Read from a file called names.xls
2. Generate folders A-Z
3. Rename a different file, "Master Competency.xlsx" and put it in the folders.

What you are going now is exactly right, except that the file I want copied is different than the file with the names.

Ah, my bad, I get it now. Master Competency should have been copied and renamed to each person's name. I misunderstood that originally. I'll fix it up and give you another built and there will be a 4th argument to pass which will be for that file.

 

[ddrueding]

Ah, my bad, I get it now. Master Competency should have been copied and renamed to each person's name. I misunderstood that originally. I'll fix it up and give you another built and there will be a 4th argument to pass which will be for that file.

Fantastic. You will make my wife quite happy

 

[Handruin]

I made the changes, but can't get you the build because the upload is now block from work. :-\ I'll hook you up with the changes later. If you need it sooner, I can paste the source changes in here.

 

[ddrueding]

If I could get the changes sooner, I'll try building it myself. Of course, she just dropped her deadline on me.

 

[Handruin]

Download Visual C# 2010 Express for free.

I was able to attach the three source files (the zip password is 'excel').

Create a new project in VS2011 express and give it a name (such as ExcelCopy). Make the program type a C# Console application.

Expand the following code into your new project location:

FileReader.cs
Person.cs
Program.cs

Within VS2010 express, in the right menu tree called the solution explorer, right mouse click on the project name and select > Add > Existing Item....

Select the three files listed above. You'll probably already have a file name Program.cs, you can replace it. It's the main entry point into the application.

Next, you'll need to right mouse click on the same project name and select > Add Reference.

Select the .NET tab and sort by Component Name. Scroll and locate:

Microsoft.Office.Interop.Excel
(Microsoft.Office.Interop.Excel.dll)

and repeat the above step to also add:

Microsoft.Office.Tools.Excel
(Microsoft.Office.Tools.Excel.dll)

Next, try to build the application by selecting > Build > Build Solution. If it doesn't work, the error list should pop up and complain why it couldn't. If this happens, paste in the error into this thread for me.

If you happen to get no errors, open windows explorer and browse to the location of the project. There should be a 'bin' folder with possibly two folders under it. One may be named Debug, the other Release. If only Debug exists, you can go back into VS2010 express and change the drop down menu at the top from Debug to Release and then reselect Build > Build Solution.

If that works, just copy the .exe by itself and you're ready to roll.

 

[ddrueding]

I have no Microsoft.Office.anything items in that list. I verified that my copy of Office 2010 has the ".Net tools" installed. Help?

 

[Handruin]

What if you Add Reference, but change to the "Browse" tab and then point it to the dll files I mentioned? If you do a search do you find those two DLLs? If so, you can add an external reference using the browse tab.

 

[Sol]

A slightly different approach with fewer (and completely different) dependencies would be to use awk with a script like;

#!/usr/bin/awk -f 
BEGIN {FS = "|"}
{
	dirname = toupper(substr($1,1,1))
	printf("mkdir %s\n", dirname) | "sh"
	printf("cp '%s' '%s/%s.xls'\n", ARGV[2], dirname, $1) | "sh"
}END{close("sh")}

It's a bit less pretty and it'll spit out a, totally benign, error every time it tries to create a directory which already exists. Plus it requires you to export your names file to a "|" separated text file and probably you'd have to install cygwin if you don't have a *nix machine with awk or gawk handy.

usage is
awk script.awk names.txt file_to_copy.xls

 

[timwhit]

For Windows I think you'd be best off writing a script in VBS. Handy's solution can be a lot more powerful, but VBS doesn't need to be compiled and is easily modifiable.

 

[Mercutio]

On the other hand, awk is one of those swiss army knife tools that works on everything, once you know it. Microsoft changes its major scripting language at the drop of a hat. Or had you forgotten about Powershell?

 

[timwhit]

On the other hand, awk is one of those swiss army knife tools that works on everything, once you know it. Microsoft changes its major scripting language at the drop of a hat. Or had you forgotten about Powershell?

I remembered PowerShell, but I believe less people know it than VBS.

 

[Mercutio]

Oh, probably. But a lot of things Microsoft is doing now aren't exposed to VBS but are exposed to Powershell. Lots of Exchange functionality, for example.

 

[Handruin]

I'm sure either awk, VBS, or PowerShell can all accomplish the same thing. C# is what I know better than those other options, so that's what I went with. I would have been done with this if I could have uploaded the binary for ddrueding, but it's not allowing me to from here at work. I guess that is the down side to requiring it to be compiled/built vs interpreted.

 

[Mercutio]

No doubt. This is a perfect case of having a problem with a million solutions.

Ages and ages ago there was a really cool thread in one of the UNIX groups on USENET about all the different ways you could make the standard utilities to give a directory listing. There were probably hundreds of answers in that thread, even without dipping in to actual code.

 

[Will Rickards]

I would have used VBA in excel itself.

 

[Sol]

Yeah, obviously there's a million and one solutions, I just immediately thought of awk because it's a scripting language designed to iterate through a file and do something to each line. It's not a great solution on Windows though... Or to put it another way, Windows isn't a good solution for scripting...

 

[Sol]

I would have used VBA in excel itself.

Do VBA macros in excel have file system access?

 

[timwhit]

Do VBA macros in excel have file system access?

Yes, they do.

 

[ddrueding]

My dad happens to be a .NET developer, so I got him to build it for me. Of course, it won't run on my system, something about not supporting a 64-bit OS (no doubt a build option that my father forgot).

In the end Howell's command line solution did the trick. If this were a competition, I would have to say that both went through the same number of iterations due to my inability to clearly communicate, and Howell's was the first to accomplish the task because (even I) was able to look at the command line and understand it enough to make the last tweak.

Here is Howell's solution:

@ECHO OFF

:Instructions
:edit vars appropriately
:Save XLS with no spaces in the name
:Save XLS as tab delimited file with no spaces in the name to the same folder as the XLS
:strip out "s with find/replace
:run batch file


:set vars
set name=Last,First 1234567
set sourceloc=C:\temp
set sourcefile=Master_File.xlsx
set sourcetxtfile=names.txt
set destloc=C:\temp\output


:read in column 1 data
FOR /F "tokens=*" %%x IN (%sourcetxtfile%) DO call:create %%x

goto :EOF


:Create folders and copy file
:create
set name=%*
md "%destloc%\%name:~0,1%"
copy "%sourceloc%"\"%sourcefile%" "%destloc%\%name:~0,1%\%name%.xls"


:EOF

Anyway, thanks to the two participants! As soon as you send me your addresses, you'll be receiving some thanks.

 

[Handruin]

Glad you got something working with Howell. Sorry I wasn't able to get it built for you in time. In case you still want to use it in the future, I built it and included all the stuff together as a zip file.

 

[Howell]

Yeah, obviously there's a million and one solutions, I just immediately thought of awk because it's a scripting language designed to iterate through a file and do something to each line. It's not a great solution on Windows though... Or to put it another way, Windows isn't a good solution for scripting...

Maybe leave the kiddy scripting languages alone and learn Perl. It runs fine on Windows.

j/k

 

[ddrueding]

I can't seem to ship wine to TN? What the heck?

 

[Mercutio]

That is correct. Some states won't let you. Indiana is another one. wine.woot.com can only operate in I think 21 states.

 

[ddrueding]

stupid religion.

 

[Handruin]

Massachusetts is the same way.

 

[time]

Found this interesting: Tennessee Fair Wine Laws.

People in glass houses shouldn't throw stones, and I know we have our own restrictions on liquor sales in my state, but come on, I thought America was supposed to be a free country? I take particular umbrage at the legislated monopolies - how come that isn't illegal under federal law?

On the other side of the coin, we have huge problems with an increasingly vertically integrated retail duopoly, that is wiping out small manufacturers such as independent wineries.

 

[Howell]

How about GA or AL?

 

[Howell]

From time's link the problem is a lack of mechanism for collecting the excise tax.

Buying wine at the grocery store is usually not a problem for me as I can literally throw a rock into GA from my front porch.