Query excel numbers with leading 0 creates null

[mbowles]

I have an OpenRowSet calling an excle spreadsheet and it works fine except one column has numbers as a General format and the numbers that start with 0 have been preceded by an apostrophe in excle show they show. WHen the sql query runs, they come back as null. I have tried casting to no avail. any thoughts?

 

[jbenson001]

Are you writing to or reading from the excel sheet... ??

 

[SQLBill]

Do you mean your numbers in EXCEL look like this:

'01
'04
etc?

If so, depending on what your query does (you don't provide the code), those are NOT numbers as far as SQL Server is concerned. They are STRINGS. Drop the single quote (') and it should work.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Oh almost forgot....in SQL Server TSQL a single quote (') indicates the beginning of a STRING and needs a closing single quote.

-SQLBill

Posting advice: FAQ481-4875

 

[mbowles]

Yes reading from excel. yes the numbers are like so

14574
14749
'00475
'01258

I did not create the excel, but forced to use it. I would be happy with bringin them over to sql as char(15) or whatever.
Thanks. I will see if I can append the excel with aditional single quote at end. If you remove the ' you turn 00121 into 121 and I need the 0's in sql

 

[amrita418]

Are all the values in the column expected to be of the same size? (For example I see 14574 as one of the values, if say there is a value 23, should that be outputted as 00023)? If yes, try formatting the column to custom 00000 which should preserve the 0's without the use of quotes(').

Once you do that in excel try running the openrowset with and see if that solves your problem.

Regards,
AA