batch import dat files

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.

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


The Real Jd wrote:
>
> 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

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