import dates from excel 1

[kitty024]

Hi

I have a table in excel, which either has a date or says emptyCell.

HLDR
-----
22/09/1988
emptyCell
emptyCell
22/06/1993
1/04/1991
29/03/1990


I am trying to import the data (using the import wizard)into sql server, but even if i set the datatype to varchar or nvarchar for the destination column, it maintains the emptyCell text but loses the dates and puts in <NULL> instead.

The wizard recognises that the source column is a varChar[255], so i don't understand why it doesn't import the dates. I have tried formatting the source column on the spreadsheet to make it a date cell but that doesn't make any difference.

Has anybody got any ideas?

Kitty

 

[SQLSister]

I've had trouble before with fields containing data that was all numeric or character inthe same column, the driver seems to only recognize one of them. Suggest you delete the words empty cell and try again.

Questions about posting. See faq183-874

 

[kitty024]

Hi SQLSister

I tried that. If i have the cells empty ie: delete the words emptyCell it just imports everything as a <NULL>. If i put a random default date in eg: 01/01/1900 instead of emptyCell, then it's fine and it will import the dates correctly. That solves the problem there but it's not practical.

The problem being that i have data in another column which has a combination of date formats eg: 22/08/1960; ca.1927; Sep-Oct 1852; 1860 etc.

At the moment if i try to import that column it's seeing the source column as having a datatype double and is just importing data from those fields which contain the year only, everything else it sets to <NULL>.

I figured if i could sort the problem with the simple example i could make it work for the more difficult column.

kitty

 

[dbomrrsm]

save the excel data to CSV and import the csv.

[blue]DBomrrsm[/blue]

 

[kitty024]

Thanks DBomrrsm

That worked. Once i created the .cvs I had to rename it as .txt file to simplify the data source process, but that was it.

cheers

kitty