Previous Topic Next Topic

Batch excel import

Closed
overloaded - Jan 14, 2010 at 03:19 AM
wbarrett Posts 2 Registration date Thursday January 14, 2010 Status Member Last seen January 15, 2010 - Jan 15, 2010 at 05:39 PM
Hello everyone!

I've got a ton of Instron data in tab delimited .txt files that I need to import to Excel and was hoping you all could help me out. I have a folder called Test Data with subfolders A, B, C, ... which contain the .txt files mixed in a different format file. I need to make an Excel file for each folder (A,B,...) and import all of the text files to seperate worksheets within the file. I'd like the worksheets to be named the same as the file name (sans .txt) and the Excel file to be named the same as the folder. Below is a random sample of the contents of an test result file. Am I asing too much for a batch file to do all of this? It sounds easy in describing it, but I'm sure coding it is a lot trickier. Like I said, I have tons of data and could probably spend a month just importing it, so can anyone please help me? THANKS IN ADVANCE!!




Text Inputs : Material Polycarbonate
Text Inputs : Sample ID PC 1
General : Date 1/12/2010
Number Inputs : Diameter 12.62 mm
Dimension : Geometry Circular
Strain : Axial Gauge Length (Strain Source) 25 mm
Number Inputs : Final Diameter 9.59
Engineering TRUE
Time Tensile strain Load Strain 1 Strain 2 Stress Strain 1 Strain 2 Axial Stress
(sec) (mm/mm) (N) (mm/mm) (mm/mm) (N/mm^2) (mm/mm) (mm/mm) (N/mm^2)
0.0 0.00000 71.21642 0.00000 0.00001 0.56934 0.00000 0.00001 0.56934
0.1 0.00000 71.31160 0.00000 0.00002 0.57010 0.00000 0.00002 0.57010
0.2 0.00008 97.20797 0.00008 0.00004 0.77713 0.00008 0.00004 0.77719
0.3 0.00011 111.52076 0.00011 0.00006 0.89155 0.00011 0.00006 0.89165
0.4 0.00013 118.63084 0.00013 0.00007 0.94839 0.00013 0.00007 0.94852
0.5 0.00015 124.38036 0.00015 0.00008 0.99436 0.00015 0.00008 0.99451

1 reply

Answer 1 / 1
wbarrett Posts 2 Registration date Thursday January 14, 2010 Status Member Last seen January 15, 2010
Jan 15, 2010 at 05:39 PM
I've written several Excel macros for various operations, though none for your particular application.

Try this: turn on the macros (you may have to adjust the Excel security level, as Excel is snotty about user-generated macros), then go through a sample operation of the sort you describe. Turn off macros, give the macro a name.
Open the macro editor. You will find a function with your macro name in Visual Basic. So it provides clues about how to generalize your problem with an extended macro. You will have to learn some Visual Basic, I suggest buying a reference manual and tutorial for that, unless you want the pain of deciphering the online help.

So, in principle, you should be able to write a macro that grabs your file names (could be in a list in a special worksheet), creates a worksheet for each file, and downloads the file.

I've written very elaborate macros for PowerPoint, and simple ones for Excel. It takes some detective work to decipher how it's done, but start by having Excel generate some macros for the operations you need.

If you'd like some professional help with this, contact me through my web site www.wbarrett.us.
0