Openrowset returning null value when field is not null

  • 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.

  • 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

  • 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


  • 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?

  • 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/

  • Adding the schema.ini file did the trick. Thanks to everyone for all your help.

  • 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