Null Dates: SQL Linked Server and Excel

I've got a spreadsheet with around 900 rows in it.  I have it in a linked server in SQL (see code attached).  One column is the Service Date.  Some values in that column are showing in SQL as null, although they are not. All values are a date (this is Excel 2007 & sql 2008).  Originally they were the "*3/14/2001" formatted date, and currently they're the m/d/yyyy custom date.  Although the values that work and the values that don't have the same format, I've even tried to copy the format using the format paint brush, to no avail.  

I can copy (ctrl-c) from a cell that is not showing as null in SQL and paste (ctrl-v) to a cell that is showing as null in SQL, then type over the correct date, and that does work.  However, I've also copied the column to another column, then copied from a "good" cell down the column, overwriting the "bad" cells, and tried a formula that will show the previous date (in cell C4, enter the formula "=D4"), but that does not work either.  

I'm hoping to use this coding in many future spreadsheets, not just this one, so I'm hoping for an answer that is not going to be sheet-specific.

Any help would be greatly appreciated,  Thanks!

jr
EXECUTE sp_addlinkedserver
@server = 'SvrExcel',
@srvproduct = 'ACE 12.0',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'T:\Data_Team\CurrentJobs\ClientData1103\WorkInProgress\MasterFile.xlsx',
@provstr = 'Excel 12.0'
GO

EXEC sp_addlinkedsrvlogin 'SvrExcel', 'false'
go

SELECT * INTO aaLookups FROM SvrExcel...Lookups$

Open in new window

LVL 4
jruheAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
What, exactly, is showing in the worksheet cell C4? When you select C4, what, exactly, is displayed in the formula bar?

Kevin
0
jruheAuthor Commented:
FYI, I did get around it, but I need a better solution due to the number of jobs I have of this nature.  In answer to your question, I see exactly this in both the cell (c4 is the example) and the formula bar:

1/1/2002

Check out the image.  date filter

I noticed in the drop-down filter that some were filtering like dates and others were filtering like text fields.  I am 100% certain that even the ones that were showing like text were formatted as dates.  I checked them, formatted the column, did the format painter--I'm absolutely certain that they were (at least showing as) formatted dates.

In order to get around it, I copied the column to another column (we'll say column E), then I used the following command in the original "problem" column:  =date(year(E4),month(E4),day(E4)).  I then did the SELECT command in SQL and although it showed some E values as null, all of the C values were dates.  So I did a copy/paste values and deleted my column E.

Not very elegant, I realize, but it's getting late. . .  Anyway, I'm hoping to find a better solution because I (and my department, I'm thinking) will use this tool a lot in the future and I don't want to have my people (and me) jumping through too many hoops to use it.

Thanks very much

0
zorvek (Kevin Jones)ConsultantCommented:
So you are stating that all of the dates are being imported into your worksheet but some of them are not showing as real dates.

Why do you state that some values are null? Where is the null displayed? Can you post the worksheet? If it contains sensitive data or is exceptionally large, delete all the irrelevant columns and most of the rows leaving just a sample of dates and, if the problem is still present in the remaining cells, post that workbook.

Kevin
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

jruheAuthor Commented:
All of the values are showing as dates in Excel; however, note in my original question that the dates are showing as null in SQL.  So if in the spreadsheet I see this:
1/1/2003
2/2/2004
3/3/2005

In SQL I may see:
1/1/2003
NULL
3/3/2005

Thanks,
jr
0
zorvek (Kevin Jones)ConsultantCommented:
That's puzzling to me. If the values are null in SQL then wouldn't you want null or empty cells in Excel? Where are the dates coming from if they don't exist in SQL?

Kevin
0
jruheAuthor Commented:
I am trying to read the dates from Excel into SQL.  I'm taking a spreadsheet's data, and running some code using that data in SQL.
0
zorvek (Kevin Jones)ConsultantCommented:
That's the opposite of what I though was going on. Sorry.

How are you loading the worksheet with the dates? Are the dates being entered manually?

Kevin
0
jruheAuthor Commented:
The spreadsheet (and its dates) are coming from my client.  I have no way to determine how each client will do it--I thought that formatting the column as a date would work, but alas. . .

0
zorvek (Kevin Jones)ConsultantCommented:
Now I see the problem. You won't be able to do much from the SQL side of things. One possibility is to create a macro that resides in another workbook such as Personal.xls which you can use to clean up the selected cells.

You can resolve the problem manually each time using one of the techniques below (one of which you have already tried - formulas - and would prefer not to do).

Sometimes numbers, dates, and times can be entered into worksheet as text values which prevents them from being formatted and sorted. This frequently happens when data is imported from an external source such as a database. To convert such values to real numbers, dates, and/or times, select any unused empty cell and choose the Edit->Copy menu command. Select the cells containing the text version of the numbers or dates to be converted. Choose the menu command Edit->Paste Special. Select the Values radio button in the Paste section, the Add radio button in the Operation section, and click OK. Note that this technique also can be used to remove single quotes from in front of numeric data.

If any dates show as numbers then select those cells and choose the menu command Format->Cells, navigate to the Number tab, select Date in left list and the desired date format from the right list. Click OK.

If all dates show as numbers but display as dates in the formula bar, choose the menu command Tools->Options, navigate to the View tab, and uncheck Formulas in the "Window options" section.

If using Excel 2003 then choose the menu command Tools->Options, navigate to the Error Checking tab, check on "Number stored as text", and click OK. Any numeric values stored as text now have a small green triangle in the upper left corner of the cells. Select the afflicted cells, click on the warning icon that appears next to the selection, and choose "Convert to Number" from the menu that appears. If the warning icon is not displayed or the "Convert to Number" is not available then select a smaller set of cells. Another technique is to select the first cell with the green triangle, move the mouse over the alert popup to activate it, while pressing the SHIFT key, use the arrow keys to select the other cells, and then choose the corrective action from the popup control menu.

If converting a date or time and the above technique does not work, then the text has to be broken into parts to be properly converted. To do this four functions are required: LEFT, MID, RIGHT, and VALUE. The LEFT, MID, and RIGHT functions pulls parts of a string so they can be assembled in a way that Excel can interpret. The VALUE function interprets the assembled parts into a date time value. For example, assuming the text string in cell A1 is formatted as:

   Feb-14-07 18:20:11

Excel does not recognize this as a date value and so the parts must be extracted and reassembled in order to convert the string into a date time value:

   =VALUE(MID(A1,5,2)&LEFT(A1,3)&MID(A1,8,2))+VALUE(RIGHT(A1,9))

The above formula takes the various date and time parts and assembles them into two strings: a date string and a time string, each in a form that Excel can interpret. More specifically:

   =VALUE("14"&"Feb"&"07")+VALUE("18:20:11")
   =VALUE("14 Feb 07")+VALUE("18:20:11")
   =2/14/2007 6:20:11 PM

Different date formats have to be handled with different rules but the technique is always basically the same: reassembling the parts in way that Excel can interpret them. The three functions used to pull the parts from the string are described below.

   LEFT(string, length) - Returns the specified number of characters (length) from the left side of the string.

   RIGHT(string, length) - Returns the specified number of characters (length) from the right side of the string.

   MID(string, start, length) - Returns the specific number of characters (length) from the string starting at the specified location (start).

When assembling the parts, the assembled string must be a recognizable date format. Below are some examples of date formats that Excel recognizes as dates.

   8/22/2008
   8/22
   22-AUG-2008
   2008/02/23
   5-JUL

Note that the day and month might be reversed in some location such as the United Kingdom. Also note that the date separator character may be different. Check the Regional Settings control panel to determine the day month order and the date separator character.

If the text form of the date contains the character form of the month and the regional settings are set for some other language than the date is in, then Excel will not recognize the month text and the month must be translated to a month number. The formula below illustrates how to do this using an English language date and assuming the date is formatted as "Dec 25, 2007" and is stored in cell A1.

   =VALUE(RIGHT(A1,4)&"/"&MATCH(LEFT(A1,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)&"/"&MID(A1,5,2))

Kevin
0
zorvek (Kevin Jones)ConsultantCommented:
If you want a macro solution, post a workbook containing just the column of dates and tell us what date formats you are using in your locale settings.

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jruheAuthor Commented:
OK.  Thanks...  Not the solution i was hoping for but I'm aware of what I'd have to do to "macro-ize" it.

jr
0
zorvek (Kevin Jones)ConsultantCommented:
Yes, the bottom line is you have buggered dates and they have to be cleaned up to be recognized by SQL Server.

I just though of another possibility but I would not be able to assist: Have SQL Server import the data as text and then convert the text into dates in stored procedure.

Kevin
0
jruheAuthor Commented:
Thanks :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.