[dba-SQLServer] OpenRowset

Thanks for the reply Arthur.  Unfortunately I am working on importing files
where the content is not known to me, thus knowing the format just doesn't
really work long term. 


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
Fuller
Sent: Sunday, July 01, 2007 3:08 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] OpenRowset

I'm not sure, JC. But perhaps this might help. It's another approach:
Using the OPENROWSET BULK provider with a format file to retrieve rows from
a text file


Using the OPENROWSET BULK provider with a format file to retrieve rows from
a text file The following example uses a format file to retrieve rows from a
tab-delimited text file, values.txt that contains the following data:

1     Data Item 1
2     Data Item 2
3     Data Item 3

The format file, values.fmt, describes the columns in values.txt:

9.0
2
1  SQLCHAR  0  10 "\t"        1  ID
SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"      2  Description
SQL_Latin1_General_Cp437_BIN


This is the query that retrieves that data:

SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt',
   FORMATFILE = 'c:\test\values.fmt') AS a;

... which of course demands a format file. I'm not sure what the 9.0 means.
The 2 means column count. The "\t" means tab, I think, and the "\r\n" means
return + newline.
For more info on using format files, see
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2956df78-
833f-45fa-8a10-41d6522562b9.htm.

hth,
Arthur

On 7/1/07, jwcolby <jwcolby at colbyconsulting.com> wrote:
>
> Guys,
>
> I am trying to do the following, straight in a query window in SQL 
> Server
> 2005:
>
> SELECT * FROM
> OpenRowset('MSDASQL','Driver={Microsoft Text Driver (*.txt; 
> *.csv)};DefaultDir=C:\aTestFTP\;','select * from Infutor1.txt')
>
> I get the following error:
>
> OLE DB provider "MSDASQL" for linked server "(null)" returned message 
> "[Microsoft][ODBC Text Driver] '(unknown)' is not a valid path.  Make 
> sure that the path name is spelled correctly and that you are 
> connected to the server on which the file resides.".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDASQL" 
> for linked server "(null)".
>
> So what did I do wrong this time?  ;-)
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com