Hi,
Can anyone help with the following:
I am importing a XML file into a SQL (MSDE) database table, and one
field is set as follows:
01/11/2005 00:03:33.18
01/11/2005 00:04:31.12
01/11/2005 00:10:06.32
01/11/2005 00:12:20.59
01/11/2005 00:00:56.66
I am using access to manage MSDE, and when I go to the table design and
try to format this field as "General Date" or any other pre-set date
format, it give the "Access encountered an error" message and then
deletes everything in that field.
Does anyone know how I can format the above time format into something
that will work with MSDE?
Thanks in advance for any help.
Thanks
H01/11/2005 00:03:33.18 works on SQL Server but not in Access.
Access only recognizes seconds (00:00:00 to 23:59:59) so 01/11/2005 00:03:33
will work.
If you need miliseconds perhaps you can use another tool.
Ben Nevarez
<harrys@.gmail.com> wrote in message
news:1131930009.458433.191620@.g43g2000cwa.googlegroups.com...
> Hi,
> Can anyone help with the following:
> I am importing a XML file into a SQL (MSDE) database table, and one
> field is set as follows:
> 01/11/2005 00:03:33.18
> 01/11/2005 00:04:31.12
> 01/11/2005 00:10:06.32
> 01/11/2005 00:12:20.59
> 01/11/2005 00:00:56.66
> I am using access to manage MSDE, and when I go to the table design and
> try to format this field as "General Date" or any other pre-set date
> format, it give the "Access encountered an error" message and then
> deletes everything in that field.
> Does anyone know how I can format the above time format into something
> that will work with MSDE?
> Thanks in advance for any help.
> Thanks
> H
>|||Hi Ben.
Problem is, that if I try and covert it to the 00:00:00 format, access
doesn't like that so it just deletes all the records!
And at the moment there is no other way to get the XML file, apart from
with those miliseconds in there.
H
Ben Nevarez wrote:
> 01/11/2005 00:03:33.18 works on SQL Server but not in Access.
> Access only recognizes seconds (00:00:00 to 23:59:59) so 01/11/2005 00:03:
33
> will work.
> If you need miliseconds perhaps you can use another tool.
> Ben Nevarez
>
>
Showing posts with label importing. Show all posts
Showing posts with label importing. Show all posts
Wednesday, March 7, 2012
Date Format Conversions in SSIS
Hi
I am quite new to SSIS and have been given the task of importing some data from a text file into the database. The data contains dates which are in the American format of mm/dd/yyyy. I need them in the datadase in the format of dd mon yy.
I realise I could load it and do a SQL task to convert once it is in the database but ideally i would like this data transformed before it is loaded into the tables.
any suggestions will be gratefully recieved.
Best regards
It's just string manipulation. Add a derived column transformation, and manipulate the input string (using SUBSTRING, etc.) into a new column, casting it as the appropriate type.Greg.
Sunday, February 19, 2012
Date Conversion Problem
I am importing a text file into a SQL table, using DTS. My problem is concerning the date fields. The source fields are in the yyyymmdd format. I have tried using datetime transformation, using yyyyMMdd as the source format, and MM/dd/yyyy as the destination formation. If there is a valid date, this works fine. However, many of the dates are either null or contain spaces, and the DTS will not handle them. Any suggestions as to how to handle this?Is the column defined as NOT NULL?
DTS the table a stage table with all of the columns a varchar...
Then manipulate it with sql and do an insert?|||Originally posted by Brett Kaiser
Is the column defined as NOT NULL?
DTS the table a stage table with all of the columns a varchar...
Then manipulate it with sql and do an insert?
-----------
The Allow Nulls option is turned on in the table definition. If I manually add a record in Enterprise Manager, it will accept nulls. It's just the DTS that doesn't like them.
I can use an intermediary table if that's the only way. I was just hoping that it could be done during the initial import.
Thanks for your suggestions.|||Sounds like a fixed width file...
Actually I' m suprised it's not working...
Where's the file coming from?
Mainframe?
Got any unprintable chars there?|||Originally posted by Brett Kaiser
Sounds like a fixed width file...
Actually I' m suprised it's not working...
Where's the file coming from?
Mainframe?
Got any unprintable chars there?
Yes, it is a mainframe file, with fixed width fields. There are no unprintable characters. It's just that some of the date fields are either null or contain spaces (I'm not sure which), and DTS keeps choking on them.|||Can you post the transformation code?
You do know that putting the code in the package like that slows everything down..
You're much better off getting all the data in, then using set based methods to transform the data...
much, much fatser...
Ever use bcp?|||Originally posted by Brett Kaiser
Can you post the transformation code?
You do know that putting the code in the package like that slows everything down..
You're much better off getting all the data in, then using set based methods to transform the data...
much, much fatser...
Ever use bcp?
No, I haven't used bcp before. I'll check it out.
In addition to choosing datetime transformation and setting the formats, I've also tried using an ActiveX script. Here is the ActiveX code I've tried for the transformation:
Function Main()
If Not IsNull(DTSSource("Col010")) AND LEN(TRIM(DTSSource ("Col010"))) > 0 Then (Checking for null or spaces)
DTSDestination("AWARD_DATE") = MID(DTSSource("Col010"),7,2)&"-"&MID(DTSSource("Col010"),5,2)&"-"&LEFT(DTSSource("Col010"),4)
Main = DTSTransformStat_OK
End If
End Function
The error returned is: Invalid procedure call or argument - DTSSource|||Just wondering is your System a AS400 cause i had also ran into this before.|||Originally posted by hillcat
Just wondering is your System a AS400 cause i had also ran into this before.
No, PC with Windows XP Pro & SQL Server 2000|||but is the the mainframe file a rpg file|||Originally posted by hillcat
but is the the mainframe file a rpg file
I'm not familiar with rpg; all I know is, the file is a text file from a mainframe, with fixed width fields. I was given a printout of the file layout to indicate starting and ending point of the fields.|||well Is not null function will not work thats for sure since this is a unprintable caracter and this caracter as a value. if this unprintable caracter is at the begining of a string try to trim the first caracter from the string.|||Originally posted by hillcat
well Is not null function will not work thats for sure since this is a unprintable caracter and this caracter as a value. if this unprintable caracter is at the begining of a string try to trim the first caracter from the string.
I'm not sure whether it is null or spaces; that's why I used both the the 'not isnull' and the 'trim', so that I'd be covered either way. If either is not true (value is null, or value is spaces), then the statements inside the if clause should be bypassed|||My guess here is that if you DTS a column that has space and no transformation, it'll put in null..
But because of the transformation, I guess it thinks there should be a valid value, and then fails.
The other thing is that it might not be space, but other data that doesn't transform to a valid date.
Use a stage table and do some analysis.
Soemthing like
SELECT * FROM myStage99 WHERE ISDATE(yourDateCol) = 0|||Originally posted by Brett Kaiser
My guess here is that if you DTS a column that has space and no transformation, it'll put in null..
But because of the transformation, I guess it thinks there should be a valid value, and then fails.
The other thing is that it might not be space, but other data that doesn't transform to a valid date.
Use a stage table and do some analysis.
Soemthing like
SELECT * FROM myStage99 WHERE ISDATE(yourDateCol) = 0
I'll give it a try. Much thanks . . .|||Like:
USE Northwind
GO
CREATE TABLE myTable99(Col1 varchar(8))
GO
INSERT INTO myTable99(Col1)
SELECT 'yyyymmdd' UNION ALL
SELECT '20040317' UNION ALL
SELECT ' '
GO
-- Show me Valid Dates
SELECT * FROM myTable99 WHERE ISDATE(Col1)=1
-- Show me InValid Dates
SELECT * FROM myTable99 WHERE ISDATE(Col1)=0
--Move to it's Final Destination
CREATE TABLE myTable00(Col1 datetime)
GO
INSERT INTO myTable00(Col1)
SELECT Col1 FROM myTable99 WHERE ISDATE(Col1)=1
SELECT * FROM myTable00
GO
DROP TABLE myTable00
DROP TABLE myTable99
GO
DTS the table a stage table with all of the columns a varchar...
Then manipulate it with sql and do an insert?|||Originally posted by Brett Kaiser
Is the column defined as NOT NULL?
DTS the table a stage table with all of the columns a varchar...
Then manipulate it with sql and do an insert?
-----------
The Allow Nulls option is turned on in the table definition. If I manually add a record in Enterprise Manager, it will accept nulls. It's just the DTS that doesn't like them.
I can use an intermediary table if that's the only way. I was just hoping that it could be done during the initial import.
Thanks for your suggestions.|||Sounds like a fixed width file...
Actually I' m suprised it's not working...
Where's the file coming from?
Mainframe?
Got any unprintable chars there?|||Originally posted by Brett Kaiser
Sounds like a fixed width file...
Actually I' m suprised it's not working...
Where's the file coming from?
Mainframe?
Got any unprintable chars there?
Yes, it is a mainframe file, with fixed width fields. There are no unprintable characters. It's just that some of the date fields are either null or contain spaces (I'm not sure which), and DTS keeps choking on them.|||Can you post the transformation code?
You do know that putting the code in the package like that slows everything down..
You're much better off getting all the data in, then using set based methods to transform the data...
much, much fatser...
Ever use bcp?|||Originally posted by Brett Kaiser
Can you post the transformation code?
You do know that putting the code in the package like that slows everything down..
You're much better off getting all the data in, then using set based methods to transform the data...
much, much fatser...
Ever use bcp?
No, I haven't used bcp before. I'll check it out.
In addition to choosing datetime transformation and setting the formats, I've also tried using an ActiveX script. Here is the ActiveX code I've tried for the transformation:
Function Main()
If Not IsNull(DTSSource("Col010")) AND LEN(TRIM(DTSSource ("Col010"))) > 0 Then (Checking for null or spaces)
DTSDestination("AWARD_DATE") = MID(DTSSource("Col010"),7,2)&"-"&MID(DTSSource("Col010"),5,2)&"-"&LEFT(DTSSource("Col010"),4)
Main = DTSTransformStat_OK
End If
End Function
The error returned is: Invalid procedure call or argument - DTSSource|||Just wondering is your System a AS400 cause i had also ran into this before.|||Originally posted by hillcat
Just wondering is your System a AS400 cause i had also ran into this before.
No, PC with Windows XP Pro & SQL Server 2000|||but is the the mainframe file a rpg file|||Originally posted by hillcat
but is the the mainframe file a rpg file
I'm not familiar with rpg; all I know is, the file is a text file from a mainframe, with fixed width fields. I was given a printout of the file layout to indicate starting and ending point of the fields.|||well Is not null function will not work thats for sure since this is a unprintable caracter and this caracter as a value. if this unprintable caracter is at the begining of a string try to trim the first caracter from the string.|||Originally posted by hillcat
well Is not null function will not work thats for sure since this is a unprintable caracter and this caracter as a value. if this unprintable caracter is at the begining of a string try to trim the first caracter from the string.
I'm not sure whether it is null or spaces; that's why I used both the the 'not isnull' and the 'trim', so that I'd be covered either way. If either is not true (value is null, or value is spaces), then the statements inside the if clause should be bypassed|||My guess here is that if you DTS a column that has space and no transformation, it'll put in null..
But because of the transformation, I guess it thinks there should be a valid value, and then fails.
The other thing is that it might not be space, but other data that doesn't transform to a valid date.
Use a stage table and do some analysis.
Soemthing like
SELECT * FROM myStage99 WHERE ISDATE(yourDateCol) = 0|||Originally posted by Brett Kaiser
My guess here is that if you DTS a column that has space and no transformation, it'll put in null..
But because of the transformation, I guess it thinks there should be a valid value, and then fails.
The other thing is that it might not be space, but other data that doesn't transform to a valid date.
Use a stage table and do some analysis.
Soemthing like
SELECT * FROM myStage99 WHERE ISDATE(yourDateCol) = 0
I'll give it a try. Much thanks . . .|||Like:
USE Northwind
GO
CREATE TABLE myTable99(Col1 varchar(8))
GO
INSERT INTO myTable99(Col1)
SELECT 'yyyymmdd' UNION ALL
SELECT '20040317' UNION ALL
SELECT ' '
GO
-- Show me Valid Dates
SELECT * FROM myTable99 WHERE ISDATE(Col1)=1
-- Show me InValid Dates
SELECT * FROM myTable99 WHERE ISDATE(Col1)=0
--Move to it's Final Destination
CREATE TABLE myTable00(Col1 datetime)
GO
INSERT INTO myTable00(Col1)
SELECT Col1 FROM myTable99 WHERE ISDATE(Col1)=1
SELECT * FROM myTable00
GO
DROP TABLE myTable00
DROP TABLE myTable99
GO
Subscribe to:
Posts (Atom)