Importing multiple files at once

[NoiCe]

Hello,

I really need some help. I am looking for an easy way to import multiple files (50+) Pipe Delimited txt files into a table (Append to all 1 table) so I can do some data manipulation. All the files have a different file name; however, have the same layout. I am not too familiar with VBA. Can anyone please help?

Thank you very much.

[PatrickMc]

Here are a couple of ideas. These scripts are in biterscripting (http://www.biterscripting.com for free download) .

To put 50+ files with the same layout into one file:

 
cat file1 > "C:/MasterFile.psv"
cat file2 >> "C:/MasterFile.psv"
.
.
.

If there is a certain theme beghind these input file names, paths, etc. , you can code that logic into above code.

I am calling the MasterFile .psv for Pipe-Separated-Values.

You did not say what kind of data manipulation - here is one example. Let's say you want to add up field 5 (an int value) in the MasterFile.psv. Here is a script for that.

 
set $wsep="|"     # Field Separator
 
var int itotal5
 
# Read in MasterFile
var str records ; cat "C:/MasterFile.psv" > $records
 
# Read one record at a time
while ($records <> "")
do
    var str record ; lex -e "1" $records > $record
 
    # Get the 5th field.
    var str field ; wex -e "5" $record > $field
 
    # Conver to int
    var int ifield ; set $ifield = makeint(str($field))
 
    # Add to total
    set $itotal5 = $itotal5 + $ifield
done
 
# Print total of field 5
echo "THE SUM TOTAL OF FIELD 5 IS " $itotal5

If you need to do this periodically, just put these scripts into a file (C:/WeeklyScript.txt) and call that script from your periodic batch process as follows.

C:/biterScripting/biterScripting.exe "C:/WeeklyScript.txt"