Need Help with this Macro

[Shawnathon]

Need to import multiple text files in one shot.
(Excel only allows 1 at a time)

Most file names are similar just numbered.
ex. JoeBlowfile 1.txt, JoeBlowfile 2.txt, JoeBlowfile 3.txt, etc......

All text files have 30 lines of text

For example: JoeBlowfile 1.txt will start at A1 and end at A30, then JoeBlowfile 2.txt starts at A31 and ends at A60 and so on.....

May need to import 1 to 10000 files at a time.

all in 1 row single line starting at A1

once all files are imported need to export to 1 single text file
file saved in XXXX folder with XXXX.txt name.

Thanks in advance.
Shawn

 

[Special-K99]

If all you're trying to do is concatenate several thousand text files then a DOS approach may be better

go to the directory with the files in
create a batch file as follows

del Files.txt
dir /b k*.*> List.lst
for /F %%a in (List.lst) do type %%a >> Files.txt

this will create a list (List.lst) of all the files to copy (I have chosen files beginning k*.*)
Each file is appended to the final file Files.txt

Once this batch file has finished you should have a single file (Files.txt) which will contain all the text files concatenated together

 

[Shawnathon]

Hi Special-K99, thanks for the reply

what is the correct code to find file location?
I'm getting an "invalid switch" notification.

looks like it needs to be switched and I need help.

I am new to the command prompt

Thanks,
Shawn

 

[Special-K99]

I'm running this on Windows XP it may be different on Vista or Windows 7
Invalid switch usually means the wrong slash was entered
Use / instead of \

I would usually put this batch file in the same folder as the files.

 

[Shawnathon]

Ok i think have that figured out.

is the /b for the location of the text files
and the /F s for location of where it is to be saved?


del Files.txt
dir /b k*.*> List.lst
for /F %%a in (List.lst) do type %%a >> Files.txt</pre>
Thanks for the help on this
appreciate it very much

Shawn

 

[Shawnathon]

if the file or files that i want to import have names with spaces what do i put in the spaces when i enter it in the command prompt?

EX: Joe Blow File 1

Write: Joe_Blow_File_1.txt ? is this correct? if not how do i write it?

And yes i am using Windows XP

Thank you very much
Shawn

 

[Special-K99]

The /b switch just lists the filename (no path) so you need to be in the same directory when you run the batch file.

/F is something to do with tokens, I dont fully understand it myself

The %%a is a variable that gets passed the file name

The double carats > > indicate append rather than create a new file

So the batch file is saying

Delete any existing list of files made previously
Make a list of all the filenames (no paths) in the current directory and call it List.lst
The For loop is then saying run through the list and for each item in the list type the contents out to a file called Files.txt but make sure you append to the existing Files.txt

which effectively concatenates the individual files into one large file.


Regarding spaces in filenames

Changing the for statement to this may work (untested)

for /F "%%a" in (List.lst) do type %%a >> Files.txt
or
for /F %%a in (List.lst) do type "%%a" >> Files.txt
or
for /F "%%a" in (List.lst) do type "%%a" >> Files.txt

Failing that some form of bulk renaming utility should allow you turn spaces to underscores
but youd have to Google around for that