Author Topic: how can I do this in excel? (Read 24401 times)

kalos · « **Reply #25 on:** March 23, 2015, 05:43 PM »

"Then you take a program of the "directory reader" type to generate a complete list of all files in a folder."

so I need to run an external program? can't excel do that?

tomos · « **Reply #26 on:** March 23, 2015, 05:49 PM »

he's given you a template file - all you need is to test it with one linked file to see if it works in practice.
-tomos (March 23, 2015, 05:32 PM)

^when you've done that^ you can worry about whether you need an app to list files (the "directory reader")

TaoPhoenix · « **Reply #27 on:** March 23, 2015, 08:35 PM »

so TaoPhoenix, sorry but your posts are a bit hard to follow
so do I need anything else apart from the xls index file and the files themselves?
I think you mention something about a program that will perform a dir command to that folder?
but I need to have command line access to the web server, right?
-kalos (March 23, 2015, 05:12 PM)

I think this is a valid point.

It doesn't have to be command line access per se - the example I created used a Windows program called Karen Directory Reader.

Those files with the long names are there because they are *in the folder*. So if you don't want them in the index, you'd take them out of that specific folder and put them somewhere else like a "tools folder".

So then when you are happy that only the files you want indexed are there and no more, you will get what you want.

TaoPhoenix · « **Reply #28 on:** March 23, 2015, 08:39 PM »

he's given you a template file - all you need is to test it with one linked file to see if it works in practice.
-tomos (March 23, 2015, 05:32 PM)

^when you've done that^ you can worry about whether you need an app to list files (the "directory reader")
-tomos (March 23, 2015, 05:49 PM)

Tomos, the process I designed is meant to tie right in to a dir reader because it produces the complete index list of folders all at once - so he will need it pretty quickly. Part of the point is the dir reader "can't lie" vs typing them in by hand. So for example if there are client names, and he enters them by hand, and one of them is something like Schaefeirmann, whoever is doing data entry by hand will get that wrong 3 times out of 10, whereas the dir reader produces a "cold facts" dump where it cannot be wrong with random spelling errors. That's part of the system I designed.

Kalos, my experts here can let me know if there's any internal command in Excel that can do this. But consider for example if my test has three test files in it, then you do it for real and now there's 140 files in the folder, the Excel file and the related export index has no idea they are there. So it has to get the list from somewhere.

Shades · « **Reply #29 on:** March 24, 2015, 01:22 AM »

In the other thread, as Ath already noticed, I mention that many people perceive Excel to be the hammer to "nail" the problem. Although this may initially work, I can tell you now already that this solution won't scale well, if at all. Just open 25 instances of Excel at the same time...I have seen the result of that on a computer with 64 Xeon processors and 64GByte of RAM. A system that has proven to be capable of doing really heavy calculations on huge sets of data with our software, suddenly became so slow that we were called in the middle of the night by the (very upset) board of directors of the company that owned this computer for some serious "grilling".

Since then, there is one mantra. Excel is never, ever a solution. Entering Excel in any automated business process and you won't have to wait until you get burned by that decision. Excel eats so much resources, it is by far the biggest offender in the Office suite...Microsoft should be ashamed of letting such garbage loose on the public. Yet the public yearns for more crap to be bolted on that pile of sh*t.

An approach of generating an Excel file automatically after being triggered or after the interval of your choice is far better and something quite easy to do in already pre-existing solutions such as a CMS.

TaoPhoenix · « **Reply #30 on:** March 24, 2015, 01:37 AM »

I think you're right as it begins to scale.

So maybe there's a two step process.

My method can get him going, and then his company team can investigate which longer term CMS they want to use.

Ath · « **Reply #31 on:** March 24, 2015, 02:40 AM »

that the server is secure and I cannot run my own stuff on it
-kalos (March 23, 2015, 04:33 PM)

Since when is that an excuse to not use the right tool for the job?

Plenty of CMS's can be run on your local system, only to upload the files to a remote (optionally very tightly secured) web server. It's even a plus not having anything but the web-content on a web server.

tomos · « **Reply #32 on:** March 24, 2015, 05:17 AM »

he's given you a template file - all you need is to test it with one linked file to see if it works in practice.
-tomos (March 23, 2015, 05:32 PM)

^when you've done that^ you can worry about whether you need an app to list files (the "directory reader")
-tomos (March 23, 2015, 05:49 PM)

Tomos, the process I designed is meant to tie right in to a dir reader because it produces the complete index list of folders all at once - so he will need it pretty quickly. Part of the point is the dir reader "can't lie" vs typing them in by hand. So for example if there are client names, and he enters them by hand, and one of them is something like Schaefeirmann, whoever is doing data entry by hand will get that wrong 3 times out of 10, whereas the dir reader produces a "cold facts" dump where it cannot be wrong with random spelling errors. That's part of the system I designed.
-TaoPhoenix (March 23, 2015, 08:39 PM)

^ I'm actually very interested in the process you describe. My point was that he try it with one or two ('manually') linked files first to see if it works - a proof of concept - then he could look at setting up the more complex system.

@kalos, it seems to me it would be a good idea if you showed your manager a few of the replies here - or the whole of the two threads. They might reconsider.

Vurbal · « **Reply #33 on:** March 24, 2015, 08:03 AM »

What are the business requirements please? I might be able to help.
-IainB (March 23, 2015, 08:36 AM)

Exactly!

Particularly in light of the other threads which apparently relate back to this same project, this already sounds less like an Excel problem than a process issue. Specifically, it appears to be a content and/or document management problem, which would be much easier solved by addressing the entire process of document creation, access, modification, storage, metadata, and possibly even version control - all of which points to at least a document management system, if not a more full featured CMS.

I imagine you can kludge together what you're looking for using Excel. You can also steer a car with your feet. That doesn't make it a good idea.

edit: I should probably mention, I have some experience with document management from the IT side. Setting it up really is so much easier than solving your Excel problem. It would also address the metadata question you posed on another thread.

kalos · « **Reply #34 on:** March 24, 2015, 09:05 AM »

how exactly that html tag works?

is it the equivalent for 'search in the same directory' for hyperlinks of local files?

how actually the xls 'knows' its web folder and that html tag links the hyperlinks to the same folder?

I don't get the mechanism

can anyone explain me please

kalos · « **Reply #35 on:** March 24, 2015, 10:18 AM »

guys, it doesn't work

I uploaded a.txt, b.txt, c.txt to my server
I also uploaded index.xls in the same web folder

I am not sure what to write inside the xls file though
it cannot allow me to enter <A Href="c.txt"> c.txt in a cell as a hyperlink
it converts it to %3cA%20Href=%22c.txt%22%3e%20c.txt
what exactly the xls index file need to contain?
any hint?

TaoPhoenix · « **Reply #36 on:** March 24, 2015, 10:41 AM »

he's given you a template file - all you need is to test it with one linked file to see if it works in practice.
-tomos (March 23, 2015, 05:32 PM)

^when you've done that^ you can worry about whether you need an app to list files (the "directory reader")
-tomos (March 23, 2015, 05:49 PM)

Tomos, the process I designed is meant to tie right in to a dir reader because it produces the complete index list of folders all at once - so he will need it pretty quickly. Part of the point is the dir reader "can't lie" vs typing them in by hand. So for example if there are client names, and he enters them by hand, and one of them is something like Schaefeirmann, whoever is doing data entry by hand will get that wrong 3 times out of 10, whereas the dir reader produces a "cold facts" dump where it cannot be wrong with random spelling errors. That's part of the system I designed.
-TaoPhoenix (March 23, 2015, 08:39 PM)

^ I'm actually very interested in the process you describe. My point was that he try it with one or two ('manually') linked files first to see if it works - a proof of concept - then he could look at setting up the more complex system.

@kalos, it seems to me it would be a good idea if you showed your manager a few of the replies here - or the whole of the two threads. They might reconsider.
-tomos (March 24, 2015, 05:17 AM)

Sure Tomos, the "three file test" is what I began with here. The excel file needs nothing but a file name, and then it creates the links itself.

Then he would just run a directory reader for the real thing, and those file names already come out in a list, so he'd copy and paste those instead.

The big bug to fix doing the whole thing is that there's two lines at the bottom of the excel that go /body and /html that would have to just be moved down to the bottom of his list.

TaoPhoenix · « **Reply #37 on:** March 24, 2015, 10:46 AM »

how exactly that html tag works?

is it the equivalent for 'search in the same directory' for hyperlinks of local files?

how actually the xls 'knows' its web folder and that html tag links the hyperlinks to the same folder?

I don't get the mechanism

can anyone explain me please
-kalos (March 24, 2015, 09:05 AM)

guys, it doesn't work

I uploaded a.txt, b.txt, c.txt to my server
I also uploaded index.xls in the same web folder

I am not sure what to write inside the xls file though
it cannot allow me to enter <A Href="c.txt"> c.txt in a cell as a hyperlink
it converts it to %3cA%20Href=%22c.txt%22%3e%20c.txt
what exactly the xls index file need to contain?
any hint?
-kalos (March 24, 2015, 10:18 AM)

Kalos,
You're asking about making an index.
But you might have missed something I was saying.
The index is a web file produced from half of the excel file.

So upload Excel Produced Index.html to that server and try those links.

The Excel file has a lot of components that aren't actually links - it assembles the links which you just copy and paste over into a text web file. That web file is your index. The links aren't directly live in the excel file.

kalos · « **Reply #38 on:** March 24, 2015, 12:22 PM »

oh I see now
well, not exactly what I was looking for
but it is a good suggestion to replace the index from an xls file to a html file

1) I don't understand how the html file is produced from the excel file
2) Also, I don't understand, how the Href tag works.
3) And how the html file can 'know' its url and thus search for the specific files in that directory.

Can you explain these things to me please?

Also, couldn't we embed that html file into an excel document, so that when I download the excel file, it will display inside the html index?
Or isn't it possible to make an excel file that will open the html index and convert the links into the correct ones? ie. the filenames with the path of the current web folder?

TaoPhoenix · « **Reply #39 on:** March 24, 2015, 12:58 PM »

oh I see now
well, not exactly what I was looking for
but it is a good suggestion to replace the index from an xls file to a html file

1) I don't understand how the html file is produced from the excel file
2) Also, I don't understand, how the Href tag works.
3) And how the html file can 'know' its url and thus search for the specific files in that directory.

Can you explain these things to me please?

Also, couldn't we embed that html file into an excel document, so that when I download the excel file, it will display inside the html index?
Or isn't it possible to make an excel file that will open the html index and convert the links into the correct ones? ie. the filenames with the path of the current web folder?
-kalos (March 24, 2015, 12:22 PM)

Let's look at those in slightly different order.

The html file source looks like this: (Modified for this post!) (And that URL tag is not in there!)

<HTML> 
<Body> 
 
 
 <A Href="a.txt"> Top important file for Kalos!! - a.txt </A> 
 <A Href="b.txt"> b.txt </A> 
 <A Href="c.txt"> c.txt </A> 
 
 
</Body> 
</Html>

So for #3, because it is in the same folder as the files, it "just knows" because it's assuming it's in the same folder, and it is. A slightly fancier way to say it is that it is just what basic spec web behavior is - look in the same folder until you tell it to look anywhere else.

For #2, "Anchor" is the A letter, and that too "just is" - it's the raw command to produce a link. It basically says "look here for the file" but then "display the link like whatever comes next". The middle half is basically any text you want to put there.

Then it just closes the link so the computer knows it's done.

BR is a line break.

I didn't spend any time putting any fancy header text on there, but once the basics work, you can fiddle with that later.

For #1, How it is produced, it's basically the right couple of columns of the excel file copied wholesale and pasted into the text html file. Notice for later you can put other things in the excel file you don't need to copy over. That's for another day.

So once you get going, you just paste the list of files in the left couple of columns, and the right side is supposed to automatically concatenate them together to produce your index file.

"Also, couldn't we embed that html file into an excel document, so that when I download the excel file, it will display inside the html index?
Or isn't it possible to make an excel file that will open the html index and convert the links into the correct ones? ie. the filenames with the path of the current web folder?"

These make less sense. You were saying a big restriction was you couldn't load a lot of stuff up on that server. So the excel file is down on your machine where you're working on it. So you're not downloading it anywhere - you already have it on your machine. And you really can't try to embed the excel file inside the html one - that starts to get way out of hand. The production flow runs the other way. Your excel file is your "engine" that does all your work, and "12 clicks later" you have a simple html index. As long as that html index is in the same folder as the files, it shouldn't need a full path - that's what you wanted earlier, flexibility to move the files and not break the index.

And don't try to "super optimize it" to save 1 step - that just makes it get all messy! Once you get the proof of concept working like tomos was saying, it really is as easy as "drop file list into excel, copy right few columns of excel, open text file, control-v paste and save" and it's done. Fast and ruthless.

The other guys are right that later you'll want a parallel investigation into content systems, but this was supposed to get "this month's problems" solved in under a day once you get the idea, and then you can grind out a more thorough review of a few different content systems and maybe even get your IT guy or someone to engage a thorough test.

Does that help?

kalos · « **Reply #40 on:** March 24, 2015, 01:17 PM »

sorry, I don't know how the html file knows its url and parent web path and you answer "it 'just' knows" !

I see nothing inside the html file that contains its url

it seems the web browser when it opens an html and sees hyperlinks in it with no path, it automatically takes the same path of the url that was downloaded

but if you take that html file and send it in an email attached, and open it from that email, will these Href links without path, work?
if they work I will get mad!

so, if they don't work, and they only work when the index html is downloaded, there is no chance to do what I want, one thing of which was to have the index file (I said xls, you made it html) to be stored locally!

kalos · « **Reply #41 on:** March 24, 2015, 01:21 PM »

can we do this?
I edit the xls file to add the filenames and possibly add new files I have uploaded
the html at the time it is downloaded, it AUTOMATICALLY reads and grabs the hyperlinks from the xls file and displays the latest uptodate index

is that possible?

that would be interesting

kalos · « **Reply #42 on:** March 24, 2015, 01:34 PM »

also, we could take advantage of excels ability to 'refresh', ie re-connect to a web location and update real time its data

so I edit the html file to create the appropriate links (without the need of excel, I just type the html code, or unless there is an idiot proof method so that people who don't know can use it) and then all I have to do is to open the excel file (from the same web folder) and it will display the right links!

that would be interesting too

BUT

I won't be able to have an excel file in my local machine and have the links in it automatically updated

I think the REAL SOLUTION is to write the code that will search in the web server and find the correct filename match and update the link, am I right? totally portable and totally robust

TaoPhoenix · « **Reply #43 on:** March 24, 2015, 01:38 PM »

sorry, I don't know how the html file knows its url and parent web path and you answer "it 'just' knows" !

I see nothing inside the html file that contains its url

it seems the web browser when it opens an html and sees hyperlinks in it with no path, it automatically takes the same path of the url that was downloaded

but if you take that html file and send it in an email attached, and open it from that email, will these Href links without path, work?
if they work I will get mad!

so, if they don't work, and they only work when the index html is downloaded, there is no chance to do what I want, one thing of which was to have the index file (I said xls, you made it html) to be stored locally!
-kalos (March 24, 2015, 01:17 PM)

can we do this?
I edit the xls file to add the filenames and possibly add new files I have uploaded
the html at the time it is downloaded, it AUTOMATICALLY reads and grabs the hyperlinks from the xls file and displays the latest uptodate index

is that possible?

that would be interesting
-kalos (March 24, 2015, 01:21 PM)

I think you might be drifting a little where a couple of your desires run opposite to each other! Again I'll try to answer these out of order in what I hope makes sense.

"...but if you take that html file and send it in an email attached, and open it from that email, will these Href links without path, work? If they work I will get mad!"

So let's not make you mad!

You shouldn't be emailing the index file. Leaving aside security on who is authorized to look at files on the server, you would email the authorized group the *link to the index file on the server*. The index file lives on the server just like any of the other files that get up there. (Otherwise, why have a web server at all? It's not software, it's just a file.)

PS in all these posts the DC board software adds random things to my posts that are not there!

So anyway, for example in my server, it was my fault in our example I used file names with spaces. It's tough to do prototypes because in prototyping you take shortcuts! I don't do a lot of web work so I was using file names with spaces. So I renamed the index with dashes which should come through as a straight link.

So my "hard link" would be
http://www.freevoteu...-Produced-Index.html

But you were asking that if people move the files around, that "hard link" breaks. So I went the other way. If you load the index up to the web server where all the files are, then you *browse* to the index, it "finds" everything in that folder. Then someone wants to move all the files say to a "completed folder". Sure. So you move the index file there too, and it still "finds" the files again. That's what I hope you were asking.

You can't simultaneously (easily) have an index file on your local machine that looks at the web server AND flexible enough to still find files when people move them around on the server.

So if I had an index on my local C drive, it would need a "hard link"
http://www.freevoteu...-Produced-Index.html
to find the stuff on the web server. But then when someone moves the file out from under you, that link would break.

You can re-do the index, but it takes a few more steps.

I know, limits of proprietary data, but ask your manager if there are a couple of very non-sensitive files (like maybe a readme and a boring memo) we can use together to take our example past one step of these prototypes and do ten minutes of "real work". A small piece of this is you are having a little trouble converting my prototype to your real situation.

I'll add a few more notes in the next post to keep this from becoming a wall of text!

kalos · « **Reply #44 on:** March 24, 2015, 01:49 PM »

sorry, in short, will the links in the index file I emailed, work? ie. download the actual files?
let aside security and that I would email the link and not the file or anything else

TaoPhoenix · « **Reply #45 on:** March 24, 2015, 01:59 PM »

sorry, in short, will the links in the index file I emailed, work? ie. download the actual files?
let aside security and that I would email the link and not the file or anything else
-kalos (March 24, 2015, 01:49 PM)

Again this is ringing my alarm bells with mixed purposes.

Do you want to come to the chat room for a bit?

Then later we can post a summary here in the thread.

kalos · « **Reply #46 on:** March 24, 2015, 02:01 PM »

sure, but which chat?
I never knew there is a chat room
by the way, I don't run irc, so if there is a web interface it would be best
EDIT: got it thanks!

TaoPhoenix · « **Reply #47 on:** March 24, 2015, 02:04 PM »

Yeah, DC has its own chat room!
Not everyone does know about it, but it's no secret, just not really advertised.
https://www.donation...ndex.php?action=chat

You can right click it and create a new tab right here in the browser. It's a little finicky, but it works pretty well unless you have heavy firewalling or something at work. But your manager knows you are trying to get him answers so he shouldn't worry if you visit there.

tomos · « **Reply #48 on:** March 24, 2015, 02:10 PM »

You'd make a great teacher Tao
I'm going to 'listen-in' as I'm learning here too

PS if you want the forum to display just text (and not mess around with html etc.) there's

disable bbc code button/ tags: [nobbc][/nobbc]
code highlighting drop down box

- both visible when you go to preview (as opposed to quick reply)

TaoPhoenix · « **Reply #49 on:** March 24, 2015, 04:55 PM »

You'd make a great teacher Tao
I'm going to 'listen-in' as I'm learning here too

PS if you want the forum to display just text (and not mess around with html etc.) there's
disable bbc code button/ tags: [nobbc][/nobbc]
code highlighting drop down box

- both visible when you go to preview (as opposed to quick reply)
-tomos (March 24, 2015, 02:10 PM)

Thank you!

Meanwhile, I tend to post-and-modify, so I don't see "nobbc" - I do see a dropdown for "basic text" - is that roughly the same thing?