December 6, 2007 at 2:27 pm
I have a stored procedure whose job it is to take the name of a csv and add the values in certain fields to an exisiting table. The guts of it look something like this with @csv being passed in as a parameter.
Declare @CSV as varchar(44)
Set @CSV='test.csv'
Declare @sql as nvarchar(max)
Set @SQL = 'Select [Serial Number],
GM,
[From Date],
[To Date]
FROM OPENROWSET(' + char(39) + 'MICROSOFT.JET.OLEDB.4.0' + char(39) + ',' + char(39) + 'Text;Database=F:\MR\Comp\' + char(39) + ',' + char(39) + 'SELECT * FROM ' + @csv + char(39) + ')'
print @sql
Exec sp_executesql @stmt=@sql
When I run the above query on a csv that looks like this.
Model,Serial Number,GM,From Date,To Date
Something,12346789,A,10/1/2007,10/31/2007
Something Else,12345abc456,A,10/1/2007,10/31/2007
If the Serial Number column contains all numbers then the results come back fine however if there are any letters then the Serial Number Column returns null like this.
Serial Number GM From Date To Date
12346789A2007-10-01 00:00:00.0002007-10-31 00:00:00.000
NULL A2007-10-01 00:00:00.0002007-10-31 00:00:00.000
Any ideas? Or maybe using openrowset is not the way to go at all. Any help will be greatly appreciated.
December 6, 2007 at 3:55 pm
maybe you could set up a linked server instead of using openrowset? openrowset has to be enabled on server which is not what every DBA likes
and as far as the query is concerned: have you tried to provide explicit names of columns in remote table? Is it possible that somewhere there is conversion to int and it fails if there is letter in number?
Piotr
...and your only reply is slàinte mhath
December 6, 2007 at 11:51 pm
Setting up a linked server is the right way to go, anyway, try adding IMEX = 1 to the provider string, which will make treat the column as textual column.
--Ramesh
December 7, 2007 at 7:45 am
Thanks for the advice. Using a linked server does seem like the way to go. If I use the code below, however, I still have the same problem.
Exec sp_addlinkedserver @server=txtsrv2,
@srvproduct='Jet 4.0',
@provider='Microsoft.Jet.OLEDB.4.0',
@datasrc='F:\MR\Comp',
@provstr='TEXT;IMEX=1'
Select [Serial Number],
GM,
[From Date],
[To Date] from txtsrv2...test#csv
returns
Serial Number GM From Date To Date
1a2346789 A 2007-10-01 00:00:00.000 2007-10-31 00:00:00.000
NULL A 2007-10-01 00:00:00.000 2007-10-31 00:00:00.000
If I add a letter in the first Serial Number then everything is returned correctly like here
Serial Number GM From Date To Date
1a2346789 A 2007-10-01 00:00:00.000 2007-10-31 00:00:00.000
12345abc456 A 2007-10-01 00:00:00.000 2007-10-31 00:00:00.000
It sounds like IMEX=1 should solve the prob. Am I adding the linked server incorrectly?
December 7, 2007 at 8:25 am
You might want to look at adding a schema.ini file in the same folder with the text file you are importing and use the schema.ini file to force it to read the serial number field as text. The basic format of the schema.ini file is:
[name_of_text_file.txt]
Format=CSVDelimited
ColNameHeader=True
Col1= FieldName1 TEXT
Col2 = FieldName2 TEXT
And the Microsoft reference for it is at: http://msdn2.microsoft.com/en-us/library/ms709353.aspx
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
December 7, 2007 at 8:48 am
Adding the schema.ini file did the trick. Thanks to everyone for all your help.
December 7, 2007 at 8:50 am
just an idea, what would happen if you add an empty string before your number?
as in
Select '' + [Serial Number] as [Serial Number],
GM,
[From Date],
[To Date] from txtsrv2...test#csv
...and your only reply is slàinte mhath
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply