import multiple csv files (>100 at a time) to Access or MS SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 100's of csv files to append to one another.

Summary file information exists at the top of the file followed with the
actual Table columns & data existing a few rows down the file.

Is there a macro that can select multiple files at a time then go through
each progressively to append them to a master file, also deleting any
unwanted rows other than the Table of data

e.g. each table looks something like this

Report 1
Ran on 01/01/2007
Run by user : XYZ

Field1 Field2 Field3 Field4
1 Joe Bloggs 02556689879
2 John Bloggs 02556689879
etc.

Any suggestions? Thanks
 
Hi Nedzer,

Access has no built-in facility for doing this, and if you're just
processing text files there's no particular reason to use Access; almost
any programming or scripting language would do. I'd use Perl; here's a
script that does the job:

#skip_headers_and_concatenate.pl
use strict;
my $infile;
my $linestoskip = @ARGV[1];

while (my $filespec = glob @ARGV[0]) { #process wildcard filespec

print STDERR "Processing $filespec\n"; #progress message
open $infile, "<$filespec" or die "Can't open $filespec";

while (<$infile>) { #read file line by line
next if $. <= $linestoskip;
chomp;
print "$_\n";
}
close $infile;
}

The command line would be like this, to skip the first five lines:

perl skip_headers_and_concatenate.pl "C:\source folder\*.csv" 5
"C:\destination folder\XXX.csv"

If you're comfortable working in VBA or VBScript you can do similar
things less elegantly. The TrimFileHeader() function at
http://www.j.nurick.dial.pipex.com/Code/index.htm may give you ideas.
 
Back
Top