|
| I am receiving data from an external party in the form of an excel
spreadsheet. We are sent 1 spreadsheet per day. It has a list of potential
clients that they have contacted, some of which have booked on to go to one
of our courses.
The only information I get about which booking is a date column and a
separate time column.
Since this is for our French operations, the dates are in French format.
dd-mmm-yy. Note that I am Australian, so I'm used to working in dd-mm-yy
format - that isn't the issue.
The problem is that the DTS import doesn't seem to recognise April (Avril in
French). The excel spreadsheet may contain "03-avr-06". (Third of April).
So I create an excel object in DTS, and a connections and link the two with
a transform task. The source looks fine (I had to use IMEX=1 though), and
then I click on the Preview button. In this screen "03-avr-06" becomes
"03-avr.-06" with the extra full stop. (Note that sometimes it also comes up
with the numeric date (38810). If I complete the process and import to a
nvarchar field, "03-avr.-06" in held in the field. If I try and import to a
datetime field, the import falls over with a conversion error on that field.
Note that it worked perfectly fine when I was only importing March dates,
which showed in both the excel spreadsheet and in SQL as "25-mars-06". I'm
wondering if SQL is getting confused due to March having 4 characters in it's
short form and April having 3?
I'm hoping long term to fully automate the process, but if this is not
possible and I have to work in the spreadsheet then it would be nice to know
that now.
Thanks
Derek
|
|