Excel to SQL

Hi everyone. I am very familiar with Access but I am new to SQL Server. I am
trying to import data to a table in SQL Server that comes from an AS400.
Everything is coming through except the date.
The date field in the Excel sheet looks like 21706. When this imports into
the table it changes to 6/5/1959. Is there some kind of formula I can put in
there so the date comes through correctly? This excel sheet is being created
from an AS400 program, so I can't change the date field there.
Any information would be greatly appreciated.
Thank you!!!
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1If you have control of the Excel spreadsheet, I have had some success
importing using DTS after setting the spreadsheet column format to
display as a date. Of course that assumes that EXCEL can translate
21706 (or whatever value) to the date you expect.
Otherwise, I always import data like this into a special table that I
name with an _Imported suffix to the name just to be able to handle
problems like this. Such a table could have the column defined as a
number, then when the data is moved to the production table the number
can be converted on the fly by applying whatever constant adjustment
factor does the job. A view over the _Imported table can simplify
that.
Roy
On Tue, 21 Feb 2006 00:52:54 GMT, "Brandy R via webservertalk.com"
<u12396@.uwe> wrote:

>Hi everyone. I am very familiar with Access but I am new to SQL Server. I a
m
>trying to import data to a table in SQL Server that comes from an AS400.
>Everything is coming through except the date.
>The date field in the Excel sheet looks like 21706. When this imports into
>the table it changes to 6/5/1959. Is there some kind of formula I can put i
n
>there so the date comes through correctly? This excel sheet is being create
d
>from an AS400 program, so I can't change the date field there.
>Any information would be greatly appreciated.
>Thank you!!!|||run this script to create this function, then call the function when you
import from the AS/400.
CREATE FUNCTION dbo.ShortDate
(@.int bigint )
RETURNS smalldatetime
BEGIN
declare @.temp smalldatetime
---
if isdate( -- this is necessary because we have some illegal dates such as
2/29/03
---
case when len(ltrim(@.int)) in (5,6) then
case when len(@.int)=6 then
left(@.int,2)
else
case when len(@.int)=5 then
left(@.int,1)
end
end
+ '/'+ left(right(@.int,4),2) + '/' +
right(@.int,2)
else
null
end
---
)=1
---
Begin
set @.Temp= case when len(ltrim(@.int)) in (5,6) then
case when len(@.int)=6 then
left(@.int,2)
else
case when len(@.int)=5 then
left(@.int,1)
end
end
+ '/'+ left(right(@.int,4),2) + '/' +
right(@.int,2)
else
null
end
end|||I'm assuming that 21706 is supposed to translate to February 17, 2006;
what does 11106 translate to? January 11, 2006 or November 1, 2006?
Just asking because that will impact how you interpret the data.
Is your data an excel file, or is it a comma-delimited text file? If
the latter, check to be sure what the dtaa really looks like. It may
be 021706, in which case a function like the one below will work, but
if not, you need to see if you can tweak the extract settings to give
you a better data format than the one you have.
HTH,
Stu|||On Mon, 20 Feb 2006 20:25:26 -0500, Roy Harvey <roy_harvey@.snet.net>
wrote:

>If you have control of the Excel spreadsheet, I have had some success
>importing using DTS after setting the spreadsheet column format to
>display as a date. Of course that assumes that EXCEL can translate
>21706 (or whatever value) to the date you expect.
Well forget that, I forgot how odd the AS/400 was with dates.
Roy|||Thank you so much for trying to help me. I am trying to create a User Define
d
Function in SQL Server, however I am getting a syntax error near the keyword
"case". Any ideas?
Pinata Brain wrote:
>run this script to create this function, then call the function when you
>import from the AS/400.
>CREATE FUNCTION dbo.ShortDate
> (@.int bigint )
> RETURNS smalldatetime
> BEGIN
>declare @.temp smalldatetime
>---
>if isdate( -- this is necessary because we have some illegal dates such as
>2/29/03
>---
> case when len(ltrim(@.int)) in (5,6) then
> case when len(@.int)=6 then
> left(@.int,2)
> else
> case when len(@.int)=5 then
> left(@.int,1)
> end
> end
> + '/'+ left(right(@.int,4),2) + '/' +
>right(@.int,2)
> else
> null
> end
>---
> )=1
>---
> Begin
>set @.Temp= case when len(ltrim(@.int)) in (5,6) then
> case when len(@.int)=6 then
> left(@.int,2)
> else
> case when len(@.int)=5 then
> left(@.int,1)
> end
> end
> + '/'+ left(right(@.int,4),2) + '/' +
>right(@.int,2)
> else
> null
> end
> end
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1|||Hi Stu,
It is supposed to translate to Feb 17, 2006. However it translates to June 5
,
1959. I can't figure out why. I have no control over the formatting of the
Excel file as it is created out of the AS400.
Stu wrote:
>I'm assuming that 21706 is supposed to translate to February 17, 2006;
>what does 11106 translate to? January 11, 2006 or November 1, 2006?
>Just asking because that will impact how you interpret the data.
>Is your data an excel file, or is it a comma-delimited text file? If
>the latter, check to be sure what the dtaa really looks like. It may
>be 021706, in which case a function like the one below will work, but
>if not, you need to see if you can tweak the extract settings to give
>you a better data format than the one you have.
>HTH,
>Stu
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1|||Is it an excel file or is it a csv file? if it's a csv file that is
opened in excel, excel will truncate the leading zero from a number.
Can you open the file in wordpad or notepad?|||The reason that 21706 is translate by Excel in June 5, 1959 is because it's
taked as Julian date, so I figure out from AS400 you get an CSV file. Use it
in that way no in Excel
"Brandy R via webservertalk.com" wrote:

> Hi Stu,
> It is supposed to translate to Feb 17, 2006. However it translates to June
5,
> 1959. I can't figure out why. I have no control over the formatting of the
> Excel file as it is created out of the AS400.
> Stu wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200602/1
>|||Thank you. I was able to get the file in .csv format. The field in the table
is datetime. However, when I add this function to Transformation, I get an
error
Function Main()
str = DTSSource("Col001")
iMo = CInt( Mid( str, 1, 1) )
iDay = CInt( Mid( str, 2, 2) )
iYear = CInt( Mid( str, 4, 2) )
DTSDestination("DelDate") = DateSerial( iYear, iMo, iDay)
DTSDestination("ProjectID") = DTSSource("Col002")
DTSDestination("Phase") = DTSSource("Col003")
DTSDestination("Unit") = DTSSource("Col004")
DTSDestination("Tract") = DTSSource("Col005")
DTSDestination("Release") = DTSSource("Col006")
DTSDestination("UnitPlan") = DTSSource("Col007")
DTSDestination("UnitOpt") = DTSSource("Col008")
DTSDestination("POComp") = DTSSource("Col009")
DTSDestination("PrjFrm") = DTSSource("Col010")
DTSDestination("OrderNo") = DTSSource("Col011")
DTSDestination("OrderStat") = DTSSource("Col012")
DTSDestination("Boxes") = DTSSource("Col013")
Main = DTSTransformStat_OK
End Function
The error is
Error during Transformation 'AxScriptXform' for Row number 1. Errors
encountered so far in this task: 1.
Error Code:0
Error Source = Microsoft VBScript runtime error
Error Description: Type mismatch: 'CInt'
Error Line 9
Can you help me with this?
Marco A. Pia wrote:
>The reason that 21706 is translate by Excel in June 5, 1959 is because it's
>taked as Julian date, so I figure out from AS400 you get an CSV file. Use i
t
>in that way no in Excel
>
>[quoted text clipped - 13 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1