How do I get file names into excel?

[MikeMike]

I need to import 4000 products into my shopping cart database. I have images for each product, named with the product name: product1.jpg, product2.jpg, product3.jpg etc... How can I get all these names into a column in excel so I can work with them there?

[brotherhood of LAN]

Hi Mike,

I guess it all depends on the current format of your information and how it can be manipulated into excel. If there is no way you can import it....from the below example I would do this (providing all file names are the same minus the number)

in cell A put product
in cell C put .jpg
in the first cell of B put 1 and in the one below put =B1+1

Then highlight A B and C and use the bottom right notch on the cells to scroll down a few thousand rows......then hopefully you will have a list with product1.jpg to product1001.jpg.

As I said it all depends on how your data is currently set up.....if you could tell us a little more then there may be a tailor made way for your instance ;)

/oops added
you could then copy and paste this data into notepad.....using "edit/replace" to delete the spaces between each column. Then re-paste it back into excel it will be all within the one cell. But Im sure there is a better way, and it all dependso n what you are looking for.

[MikeMike]

Well, my problem is to get the product names into excel. I do not have any list with the product names, I only have all the image files. Somehow it must be possible to get the names from all those files and copy them into excel.

[Woz]

You need to get a list of the image directory contents, strip out the ".jpg", save a text, and then import into excel. I know how to do the latter two but someone will have to help with the first.

I do seem to remember a nifty little addin to the right hand click menu in explorer that would give you a text file of a folders contents but have no idea where I originaly found it nor whether I still have a copy. Not much help I know but perhaps it will stimulate someone else's memory.

Onya
Woz

[brotherhood of LAN]

>stimulate mind

I dunno, but you could enter the dos prompt and output a dir tree and save it in notepad. You will get all of your file names....it would just be a matter of deleting the tree alongside it.

[smokin]

MikeMike, This can be implemented using vbscript or similar scripting language imo. What scripting language are you currently using?

[gsx]

Enter DOS and type:

Dir [DirectoryWithGraphics] > c:\txt.txt

Replace [DirectoryWithGrpahics] to the directory containing all the files.

Enter Excel, goto open and select the c: drive, change file types to "text files" at the bottom and open the file txt.txt

Excel should enter you into a wizard, click Finish. You should have a list of filenames in column A and a list of extensions in column B. Delete column C onwards and put the following equation in column C:

=CONCATENATE(A1,".",B1)

Select column C, and go to copy. Create a new spreadsheet and go to the Edit menu, select Paste Special... (NOT PASTE) and change the top set of buttons to the one that says 'Values', click OK.

Voila, a list of filenames in a new Excel spreadsheet. :) Longwinded, but it works quickly if it for a one of reason.

[MikeMike]

I thought gsx's suggestion would work, but some of the files are too long and comes out as filena~1 nameof~1 etc...
Smokin, I am not a programmer and can't write scripts.
I searched around for a explorer add-on like woz mentioned, but haven't had any luck so far.
Any other suggestions?

Regards
Michael

[smokin]

Hi mike

I have found an old script I wrote that will do the job for you.

sticky mail me if interested

smokin

[visca]

1. Pop into a dos prompt
2. Go into directory with the files
3. Do a directory listing redirect:

dir >filenames.txt

4. Open trusty Notepad
5. Open newly created filenames.txt file
6. Edit¦Select All
7. Edit¦Copy
8. Open excel
9. Paste clipboard contents into desired column
10. Done

[MikeMike]

oops, now I see that the long filenames are listed in the last column. Only the first column are shortened with ~1.
Thanks alot for the help guys.

Regards
Michael