Another one on OPENROWSET

Question

Post reply

Another one on OPENROWSET

Gabriela Nanau

SSCommitted

Points: 1967
More actions
August 12, 2005 at 10:36 am

#110076

Is there possible to add some parameter in the provider string for this:
select * from OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\TEMP\;','select * from [My TEXT.txt]')
so that the text will be considered custom delimited with character ~, let's say? Otherwise is considered comma delimited by default.
Does any one have the complete syntax of the provider strings used by OPENROWSET?

Thanks,
Gabriela

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Gabriela,

I think for custom formats schema.ini file should be used with text drivers as described in

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Schema.ini File (Text File Driver)

Which should contain lines:

MyFileName.txt

Format=Delimited(custom character)

This file should be in the same directory as your text file as this article says. I will try it too.

Regards,Yelena Varsha

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

It Works!

in C:\Temp i have 2 files: MyFile.txt and Schema.ini

MyFile.txt content (2 lines for example):

this~is~a~file

Schema.ini content (3 lines)

[MyFile.txt]

Format=Delimited(~)

ColNameHeader=False

T-SQL in Query Analyzer (like yours):

select * from OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};

DefaultDir=C:\TEMP\;','select * from [MyFile.txt]')

The only thing is when I output in text then it looks like the field length is 255, we should probably use other parameters too.

Regards,Yelena Varsha

Gabriela Nanau SSCommitted Points: 1967 More actions · Answer 3

Thanks, the schema.ini works fine. The only thing I don't like is that I have to change the name of the file any time I want to read a different file from the same folder, but I think I canot have them all...

Gabriela

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

I think it could be done. You can use VBscript to modify content of the text file in the directory or since it is only 3 lines in the schema.ini file you can just overwrite the content. You can use FileSystemObject. Then you can run this script with the filename as a parameter in DTS or job step.

Looks complicated, but if you need to do it a lot, I think it could be automated.

Regards,Yelena Varsha