1.

Solve : Extracting data from Excel and creating folders and subfolders?

Answer»

I need to create a batch file that will extract data from an Excel spreadsheet to a .txt file and from there take the data and create folders and subfolders from it. Here's an example of what I need.

TEXT FILE
1 A
2 A Each letter should be the main folder. Each number should be a subfolder inside the letter folder.
3 A For example, folder A would have subfolders 1,2,3,4 in it and folder B would have subfolders
4 A 5,6,7,8 in it.
5 B
6 B
7 B
8 B
etc........

Any help on this would be greatly appreciated. It would save literally weeks of entering data and thousands of dollars. Thanks ahead of time!Batch code cannot process Excel spreadsheets which have a proprietary organization. If you can save the spreadsheet as a comma separated values (CSV) file, this little snippet may work for you.

Code: [Select]@echo off
set home=%cd%
for /f "tokens=1-2 delims=," %%x in (input.csv) do (
if exist %home%\%%y\nul (
cd %home%\%%y
md %%x
) else (
cd %home%
md %%y
cd %home%\%%y
md %%x
)
)

I made the assumption that the letters were in column 2 and the numbers in column 1 of the spreadsheet and that the CSV file would look like this:

Code: [Select]1,A
2,A
3,A
4,A
5,B
6,B
7,B
8,B

An easier way would be to USE VBScript (which is installed with Windows) or to write a macro to run inside Excel itself.

Good luck. QUOTE from: Sidewinder on December 14, 2007, 12:59:16 PM

Batch code cannot process Excel spreadsheets which have a proprietary organization. If you can save the spreadsheet as a comma separated values (CSV) file, this little snippet may work for you.

Code: [Select]@echo off
set home=%cd%
for /f "tokens=1-2 delims=," %%x in (input.csv) do (
if exist %home%\%%y\nul (
cd %home%\%%y
md %%x
) else (
cd %home%
md %%y
cd %home%\%%y
md %%x
)
)

I made the assumption that the letters were in column 2 and the numbers in column 1 of the spreadsheet and that the CSV file would look like this:

Code: [Select]1,A
2,A
3,A
4,A
5,B
6,B
7,B
8,B

An easier way would be to use VBScript (which is installed with Windows) or to write a macro to run inside Excel itself.

Good luck.

And suppose a person had never used VBScript in an Office application before... Where might he find some info on how to perform the specific TASK he needs to?Technically if you write code for an Office Application within the application you use VBA. If you write code for an Office Application that runs EXTERNAL to the application you use VBScript or any other Windows Script language that can create automation objects.

You can still do your original request in batch, but as mentioned batch code cannot read an Excel file directly. You can however save an Excel file as a CSV file by choosing Saveas from the File menu. When the dialog appears, give the file a name then choose CSV (comma delimited) from the save as type drop down box. The CSV file will be input to the script posted.

Help with VBA and VBScript can be found all over the net. In addition each Office Application comes many pages of Help (F1). The Microsoft Script Center has a Office Section geared toward their Office products.

There is a help file (script56.chm) found on most Windows installations that can help you learn all about VBScript and/or JScript.


Discussion

No Comment Found

Related InterviewSolutions