Import problem with Excel to Sql Server

[dom24]

Hi,

I'm trying to import data from an excel spreadsheet to a table in sql server 7. This all works fine, except that it the spreadsheet holds a column which stores dates. These dates do not appear when the data is imported into SQL, all the values are NULL. Anyone got any ideas why?

I've tried changing the format on the excel column to date, general, text, etc, and also changed the type in the sql table from datetime to varchar, as well as had the import create the table from new. Nothing works!

Any help would be appreciated! Thanks.

 

[dbomrrsm]

check the format of the date in excel and in sql server - it may be that sql server is expecting one format and getting another.

something else to try if that doesnt happen is to bring the date into a varchar (50) field then update into another newly created field using CONVERT(datetime,newfield,style)

[blue]DBomrrsm[/blue]

 

[yksvaan]

Is it really an excel file, not a csv file ? Have a look at the transformation tab, is there a transformation between those two columns or between all columns at all?

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[dom24]

Yes it's really an excel file, that's the format it gets sent to me in. I've found something on the microsoft website which says that if you change the format of a column in excel, the data in the column must be re-entered for it to take affect.

I've done this (a pain I know but I want to see if it worked) and it worked! Not sure what i'm gonna do with it in future though!

 

[jhall01]

You could everytime you get the file save it as a text file then import the text file.

If the Transformation is setup you could try deleting that tranformation and creating a new one as an ActiveX script instead of a copy column. This way you can use a little VB code to convert the format from excel to SQLServer.

Jon

 

[jhall01]

If the Transformation is setup you could try deleting that tranformation and creating a new one as an ActiveX script instead of a copy column. This way you can use a little VB code to convert the format from excel to SQLServer.

or

Everytime you get the file save it as a text file then import the text file.

I would recommend the 1st option so you don't have manual work to do everytime you get a file

Jon

 

[yksvaan]

So when you wrote "the spreadsheet holds a column which stores dates", it didn't mean there is date data (which is actually a serial number formatted with a date format, of course). I didn't think of that. The way to the solution is to find out, what kind of data it is, and only after that decide what to do so that there will be minimum amount of extra work and phases.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]