batch import dat files

[Guest]

i have hundreds of dat files (regular text files that are space delimited)
that are filled with columns of data. is there a way to batch import multiple
dat files. I don't want to go through the same importing procedure for each
file.

 

[Dave Peterson]

Do you want them all in one worksheet when you're done?

Are they all in the same folder?

If yes, shell to DOS
Windows start button|Run
command
or
cmd

Go to that folder.
copy *.dat all.txt
This concatenates all those .DATs into one .Txt file.

Then close that window and open the .txt file.

=========
If you want them in different worksheets/workbooks, then I'd record a macro when
I imported one, then modify that recorded macro slightly and just rerun it to
open all the files. (Or even make it open all the files.)

I recorded a macro and tweaked it just a bit to ask for multiple files (click on
the first and ctrl-click on subsequent).

It looks like this when I'm done.

Option Explicit
Sub testme()

Dim myFileNames As Variant
Dim iCtr As Long
myFileNames = Application.GetOpenFilename _
(filefilter:="DAT Files, *.DAT", MultiSelect:=True)

If IsArray(myFileNames) Then
For iCtr = LBound(myFileNames) To UBound(myFileNames)
Workbooks.OpenText Filename:=myFileNames(iCtr), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
Tab:=False, Semicolon:=False, Comma:=False, Space:=True, _
Other:=False, FieldInfo:=Array(1, 1)
Next iCtr
End If

End Sub

each .dat file in it's own worksheet in separate workbooks.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Thanks! I'll give the dos command a go first!