Home > Archive > SQL Server Programming > February 2006 > Excel to SQL
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Brandy R via SQLMonster.com 2006-02-20, 7:01 pm |
| 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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...amming/200602/1
| |
| Roy Harvey 2006-02-20, 9:57 pm |
| 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.
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 SQLMonster.com"
<u12396@uwe> wrote:
>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!!!
| |
| Pinata Brain 2006-02-20, 9:57 pm |
| 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
| |
| Roy Harvey 2006-02-20, 9:57 pm |
| 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
| |
| Brandy R via SQLMonster.com 2006-02-21, 7:04 pm |
| Thank you so much for trying to help me. I am trying to create a User Defined
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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...amming/200602/1
| |
| Brandy R via SQLMonster.com 2006-02-21, 7:04 pm |
| 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 SQLMonster.com
http://www.sqlmonster.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?
| |
| Marco A. Piña 2006-02-21, 9:57 pm |
| 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 SQLMonster.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 SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forum...amming/200602/1
>
| |
| Brandy R via SQLMonster.com 2006-02-22, 7:01 pm |
| 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. Piña wrote:[color=darkred]
>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
>
>[quoted text clipped - 13 lines]
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...amming/200602/1
| |
| Roy Harvey 2006-02-22, 7:02 pm |
| >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.
DECLARE @dd int
SET @dd = 21706
SELECT CONVERT(datetime,
SUBSTRING(STR(@dd,6),5,2) +
REPLACE(SUBSTRING(STR(@dd,6),1,2),' ','0') +
SUBSTRING(STR(@dd,6),3,2))
Roy
| |
|
| Can you cut and paste row one? It looks like Col001 is not formatted
exactly like you think it should be. Also, are you planning on
continuing this process past September? Your code will break on
October 1, 2006.
Stu
| |
| Brandy R via SQLMonster.com 2006-02-23, 7:04 pm |
| Hi Stu,
Yes, I will need this code to work for a long time. I did notice that I would
have problems once the month went to two characters.
Here is one line of the CSV file
Date Job Phase Unit Plan
PO Prj Frm Order Status Boxes
21706 KBMP NEW 200 2031 Y Y 64149 SCHED
17
Stu wrote:
>Can you cut and paste row one? It looks like Col001 is not formatted
>exactly like you think it should be. Also, are you planning on
>continuing this process past September? Your code will break on
>October 1, 2006.
>
>Stu
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...amming/200602/1
| |
|
| Hey Brandy,
When you set up the text file to be the source of the data
transformation, did you specify that the first row of data contains
column headers? According to the vbscript you posted earlier, it
doesn't look like you did (if you had, the column name would have been
used instead of the generic COL001); when the DTS runs it sees line 1,
col1 and the value is "Date", not "21706". That's your first issue,
and its easy enough to fix.
The second issue's a bit tougher, without knowing what your future
data will look like; do you have any rows that contain dates from
single-digit days (e.g., Feb 2 or March 2)? I'm concerned that the
source data will not put a leading 0 in front of your days, which could
be difficult to interpret programmatically (hence my earlier post
regarding 11106; is it Jan 11, 2006, or Nov 1, 2006?. Since it keeps
the zero for the year, it's probably OK, but I'd still look into it.
If we assume that the length of the string representing the date will
always be either 5 or 6 characters, you can change your DTS package
like so:
Function Main()
' add a 0 to the front, and keep only the 6 right characters.
'21706 -> 021706 -> 021706
'100106 -> 0100106 -> 100106
str = Right("0" & DTSSource("Date"), 6)
iMo = CInt( Mid( str, 1, 2) )
iDay = CInt( Mid( str, 3, 2) )
iYear = CInt( Mid( str, 5, 2) )
....
HTH,
Stu
| |
| brenenger via SQLMonster.com 2006-02-27, 7:12 pm |
| Hi Stu,
SQL Server doesn't like Clnt function. I am trying to find something to
replace it with.
Stu wrote:
>Hey Brandy,
>
>When you set up the text file to be the source of the data
>transformation, did you specify that the first row of data contains
>column headers? According to the vbscript you posted earlier, it
>doesn't look like you did (if you had, the column name would have been
>used instead of the generic COL001); when the DTS runs it sees line 1,
>col1 and the value is "Date", not "21706". That's your first issue,
>and its easy enough to fix.
>
> The second issue's a bit tougher, without knowing what your future
>data will look like; do you have any rows that contain dates from
>single-digit days (e.g., Feb 2 or March 2)? I'm concerned that the
>source data will not put a leading 0 in front of your days, which could
>be difficult to interpret programmatically (hence my earlier post
>regarding 11106; is it Jan 11, 2006, or Nov 1, 2006?. Since it keeps
>the zero for the year, it's probably OK, but I'd still look into it.
>
>If we assume that the length of the string representing the date will
>always be either 5 or 6 characters, you can change your DTS package
>like so:
>
>Function Main()
> ' add a 0 to the front, and keep only the 6 right characters.
> '21706 -> 021706 -> 021706
> '100106 -> 0100106 -> 100106
> str = Right("0" & DTSSource("Date"), 6)
> iMo = CInt( Mid( str, 1, 2) )
> iDay = CInt( Mid( str, 3, 2) )
> iYear = CInt( Mid( str, 5, 2) )
>...
>
>HTH,
>Stu
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...amming/200602/1
| |
|
| Hmmm, that's odd. I use CInt all the time in ActiveX; at this point, I
still think it's the fact that the data doesn't appear like you think
it does. It's tough debugging something over the net; I've run out of
ideas.
You need to debug the VBScript; have a look at On Error Resume Next and
see if you can capture the value that's blowing up your script. A good
reference on vbscript can be found at http://www.devguru.com
HTH,
Stu
| |
| brenenger via SQLMonster.com 2006-02-27, 7:13 pm |
| Can I email you a copy of the .CSV file?
Stu wrote:
>Hmmm, that's odd. I use CInt all the time in ActiveX; at this point, I
>still think it's the fact that the data doesn't appear like you think
>it does. It's tough debugging something over the net; I've run out of
>ideas.
>
>You need to debug the VBScript; have a look at On Error Resume Next and
>see if you can capture the value that's blowing up your script. A good
>reference on vbscript can be found at http://www.devguru.com
>
>HTH,
>Stu
--
Message posted via http://www.sqlmonster.com
| |
|
|
|
|
|
|
|