Column separator

Question

Post reply

Column separator

Strannik2002

SSC Veteran

Points: 279
More actions
November 21, 2002 at 10:46 am

#159002

There's great need to copy data from text files to MSSQL database using OPENROWSET statement. The problem is that default column separator is stored in the Windows Registry("Format" key).If it's comma everything is OK. But on some systems semicolon character is installed.
The question is how to specify explicitly column delimeter in the provider string when creating new connection? Now it's:
SELECT * from OPENROWSET('Microsoft.Jet.OLEDB.4.0','TEXT;HDR=YES;IMEX=1;Database=c:\Public\;',
'SELECT * from [data.txt]')
Great thanks.

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

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

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 1

You can use xp_regread to read the registry and BULK INSERT to import the delimited data.

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 2

This is an example of reading the registry

EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLDataRoot', @local_backup_Dir OUTPUT

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Strannik2002 SSC Veteran Points: 279 More actions · Answer 3

Thanks, Simon!

That's a good thought to apply to registry. But we must use OPENROWSET statement rather than BULK INSERT because it places less restrictions and above all OPENROWSET reads metadata(columns and their data types). BULK INSERT can't do that.

The problem is : if I know the Jet delimeter I still can't use this information because input file is always comma-delimeted. So I have to pass to Jet this condition. But how ?

Strannik2002 SSC Veteran Points: 279 More actions · Answer 4

Thanks to everybody who replied.

The only possible solution was to specify field separator explicitly in the schema.ini file.