open space delimited file

[dky1e]

Hi,

I need to import a couple hundred of files.

From what I noticed, the files do not all have the same columns, but they all share three columns that I am interested in, ie "col1","col2", and "col3".

Now I need to import these files, preferably not through dts into my table that contains the three columns.

Ideally I'd like the solutionto look something like this:
--for loop: for all files in the directory
select col1,col2,col3 from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\;','select * from test.txt')

But the problem is that the above will only read csv or tab delimited files.... how do I make it read space (or anything) delimited files???

 

[johnv20]

this perl program reads all the text (or text readable) files in a dir, splits on a space - just change the value between the ' split(/[delimit factor]/, $line)' line & insert the first 3 columns into 1 table in a db

use Win32::ODBC;        # Use win32 odbc
$DSNCN="db1";                     # Set DSN Varible
$User="sa";                        # Set Database user
$Password="sa";                # Set password

# Database Connect
$dsm=undef;
$dsm="DSN=$DSNCN;UID=$User;PWD=$Password";
$db = new Win32::ODBC($dsm);

if ($db)
{
  # open & read the dir into an array
 $plog_dir ="c:\\passed";
  opendir (PASSED, "$plog_dir")	|| die "$!";
  @passed = readdir(PASSED);
  closedir(PASSED); 
  
  # for every file in the dir
  foreach $logfile(@passed)
{
$file = "$plog_dir\\$logfile";
	# if it's a text file
	if (-T $file)	
	{
  	foreach $line(@filelog)
			{
			# split space delimit
      @linewords = split(/ /, $line); 
      # insert into db col1,2 & 3
      $db->Sql("insert table1 values ('$linewords[0]', '$linewords[1]', '$linewords[2]') ");  
       
      }	 
	 
	}

}
  
}

$db->Close();

 

[dky1e]

Thanks!

I ended up writing a little c# console app to parse the input file and output anther file in format I wanted. Too bad, currently, you can't do it easily through sql--in other word, I cant wait for sql2k5.