batch excel import Closed

Question

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

wbarrett · Answer

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.

Batch excel import

1 response

Similar discussions

Subscribe To Our Newsletter!