September 17, 2008 at 9:39 pm
Hi,
I am trying to Import a flat file (delimited) into a SQL server table. the was created from a unility which is not too flexible. I have the date format "YYYYMMDD" in the file , which should be converted to MM/DD/YYYY to load into a table column. I am familier with SSIS but not for this requirement. I will really appreciate any suggestions.
Another question is, I need to Import a Oracle table into a SQL server table but not whole data, only for a selected criteria. Doing this with a script component I feel a rough way. Is there any way I can insert a SQL script task between source and destination data flow tasks and execute my sql statement.
This will be a big help.
Thanks
September 23, 2008 at 6:06 am
I am trying to Import a flat file (delimited) into a SQL server table. the was created from a unility which is not too flexible. I have the date format "YYYYMMDD" in the file , which should be converted to MM/DD/YYYY to load into a table column. I am familier with SSIS but not for this requirement. I will really appreciate any suggestions.
Add a derived column into the data flow and use the following expression:
SUBSTRING([Column 1],5,2) + "/" + SUBSTRING([Column 1],7,2) + "/" + SUBSTRING([Column 1],1,4)
This will create a string MM/DD/YYYY for you that you can then input into a data conversion task to transform into a datetime before inserting into your destination. NOTE: Change [Column 1] to the real name of your input column containing the YYYYMMDD values to change.
In english this roughly means: Get 2 characters from column1 starting at position 5, add a forward slash, get 2 characters from columns1 starting at position 7, add a forward slash then add 4 characters from column1 starting at position 1 (in SSIS strings start at pos 1, not pos 0)
As for your second question, you can use an OLEDB source in your control flow to connect to your table and use a select statement with a where clause to only return the records that you are wanting. You can have more than one source->destination per control flow and SSIS will execute them in parallel according to local settings.
September 24, 2008 at 5:58 am
vaioks (9/17/2008)
Hi,I am trying to Import a flat file (delimited) into a SQL server table. the was created from a unility which is not too flexible. I have the date format "YYYYMMDD" in the file , which should be converted to MM/DD/YYYY to load into a table column.
Why do you need to convert the data just to load into SQL? YYYYMMDD is actually the recommended format by a lot of the folks here within your data, and will be recognized as datetime just fine.
DECLARE @test-2 datetime
SET @test-2 = '20080901'
SELECT @test-2 AS 'No Conversion',convert(varchar,@test,101) AS 'Convert 101',convert(varchar,@test,112) AS 'Convert 112'
/*
RESULTS
No ConversionConvert 101Convert 112
=================================== ==========
2008-09-01 00:00:00.00009/01/200820080901
*/
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 24, 2008 at 7:03 am
YYYYMMDD is ISO format which is fine
Make sure you use proper DATETIME datatype for your column
There is no need of convertion
Failing to plan is Planning to fail
September 24, 2008 at 8:34 am
I am having a similar issue with my date formats. I have an excel file I am importing from that is MM/DD/YYYY format and I import into the database as VARCHAR(10) because it won't convert to date format into the database for some reason. The format I want to convert to is CCYYMMDD.
I then run this code to try to convert it to the right format:
UPDATE temp_ameriflex_detail
SET emp_doh = convert(varchar,emp_doh,112)
It says all rows are effected but nothing has changed.
September 24, 2008 at 9:11 am
themissnlink (9/24/2008)
I am having a similar issue with my date formats. I have an excel file I am importing from that is MM/DD/YYYY format and I import into the database as VARCHAR(10) because it won't convert to date format into the database for some reason. The format I want to convert to is CCYYMMDD.I then run this code to try to convert it to the right format:
UPDATE temp_ameriflex_detail
SET emp_doh = convert(varchar,emp_doh,112)
It says all rows are effected but nothing has changed.
If you're importing as a varchar, then converting to a varchar, the style portion ",112" of the convert means nothing, so it is successfully converting your varchar to varchar (no change). What is the error you get when trying to import your excel file into a datetime column?
Somebody correct me if I'm wrong, but I think datetime columns are all stored in the same format, it's really just a display issue on output that determines whether you see '9/12/2008' or '20080912 00:00:00'
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 24, 2008 at 9:21 am
UPDATE temp_ameriflex_detail
SET emp_doh = convert(datetime,emp_doh,112)
This gets the following error:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
September 24, 2008 at 10:08 am
themissnlink (9/24/2008)
UPDATE temp_ameriflex_detailSET emp_doh = convert(datetime,emp_doh,112)
This gets the following error:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
I'm assuming you're creating temp_ameriflex_detail when you run this? or does the table already exist? Can you change the CREATE statement to make emp_doh a datetime column and then populate it with your data?
sample:
-- =============================================
-- Create temp table
-- =============================================
IF object_id('Tempdb..#temp_ameriflex_detail') IS NOT NULL
BEGIN DROP TABLE #temp_ameriflex_detail END
CREATE TABLE #temp_ameriflex_detail
(iRow int identity(1,1),
emp_doh varchar(255)NOT NULL,
emp_doh2 datetime NOT NULL)
--===== Add a Primary Key to maximize performance
IF OBJECT_ID('Tempdb..PK_#temp_ameriflex_detail_iRow') IS NULL
BEGIN
ALTER TABLE #temp_ameriflex_detail
ADD CONSTRAINT PK_#temp_ameriflex_detail_iRow
PRIMARY KEY CLUSTERED (iRow)
WITH FILLFACTOR = 100
END
INSERT INTO #temp_ameriflex_detail VALUES ('20080901','20080901')
INSERT INTO #temp_ameriflex_detail VALUES ('09/02/2008','09/02/2008')
SELECT * FROM #temp_ameriflex_detail
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 24, 2008 at 10:18 am
Yes the table has already been made. I actually import data from excel into this table which is why I don't have the column as DATETIME originally because SSIS always gives me errors if I don't import the data with the column as VARCHAR for some reason.
September 24, 2008 at 10:41 am
Right, if it's already datatype of varchar, you'll have to convert to a string to put anything in it.
I would suggest you add a column with a datatype of datetime and copy the data over to fix the data problems.
If that's not an option, split the string and re-arrange it to put the year first (additional statement to previous code):
UPDATE #temp_ameriflex_detail
SET emp_doh = convert(datetime,substring(emp_doh,7,4)+substring(emp_doh,1,2)+substring(emp_doh,4,2),112)
WHERE substring(emp_doh,3,1)='/'
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 24, 2008 at 10:46 am
Thank you for the replies =D
The only problem I see is some of the dates I get are not set positions. For example:
9/3/2008
10/21/2008
I orginally did a substr but the dates were not set in strict positions and it always messed it up. Is there a way to add zeros in certain areas if needed to fix this?
September 24, 2008 at 10:53 am
Look up CHARINDEX in BOL, and replace the number in the Substring function with the position of your '/' plus one.
see http://www.sqlservercentral.com/articles/String+Manipulation/63604/ for some recent article/discussion on this.
😀
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 24, 2008 at 11:22 am
I have researched CHARINDEX some but I do not see how I could use it to my advantage in this situation. It appears to indicate what position where a char starts at but I do not understand fully how to use this due to the fact they start in different locations. I can do an update to remove the '/' from the columns but it still puts me in the same position I am in now.
Maybe I do not understand completely how to use CHARINDEX effectively.
September 24, 2008 at 12:17 pm
ok, quick example -
'9/1/2008' has two slashes, and you know now that you can find the first slash by doing charindex('/',emp_doh,1) telling the Charindex() function to start at 1.
If you want to find the charindex of the next slash, you'll have to tell it to start after the first slash, since it always finds the first occurrence of whatever you're searching for. Therefore, you replace the number 1 in the original Charindex() above with the entire expression above plus one (to start past the first slash, not on top of it), and get:
CHARINDEX('/',emp_doh,charindex('/',emp_doh,1)+1)
This gives you the location of the second slash.
So, if you want to split your string and pull out the parts around the slashes, you'll first grab the year by finding the second slash (plus one to move after it) and grabbing the four digits after that:
substring(emp_doh,CHARINDEX('/',emp_doh,charindex('/',emp_doh,1)+1)+1,4)
or alternatively, find the starting digit by finding the total length of the string minus the second slash, then grab four digits:
substring(emp_doh,len(emp_doh) - CHARINDEX('/',emp_doh,charindex('/',emp_doh,1)+1),4)
next find the month from the front end of your string by finding the first slash and subtracting one (so as not to include the slash):
substring(emp_doh,1,charindex('/',emp_doh,1)-1)
Then the remaining digits that are in between the two slashes (use substring, put in the position of the first slash + 1 for the starting digit, position of the second slash minus the position of the first slash for the number of digits)
substring(emp_doh,charindex('/',emp_doh,1)+1,(CHARINDEX('/',emp_doh,charindex('/',emp_doh,1)+1) - charindex('/',emp_doh,1))-1 )
Concatenate that all together and you get (from your original UPDATE statement):
UPDATE #temp_ameriflex_detail
SET emp_doh = convert(datetime,
substring(emp_doh,CHARINDEX('/',emp_doh,charindex('/',emp_doh,1)+1)+1,4) +
substring(emp_doh,1,charindex('/',emp_doh,1)-1) +
substring(emp_doh,charindex('/',emp_doh,1)+1,(CHARINDEX('/',emp_doh,charindex('/',emp_doh,1)+1) - charindex('/',emp_doh,1))-1 )
,112)
Ain't it pretty? No, you're right, it's not. Much better to try and copy the data into a new column, store it as datetime and save yourself some headaches. But you gotta do what you gotta do.
Let me know if you have questions.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 24, 2008 at 1:02 pm
Thank you again for the well written post.
When I run this code:
UPDATE temp_ameriflex_detail
SET emp_doh = convert(datetime,
substring(emp_doh, CHARINDEX('/',emp_doh,charindex('/',emp_doh,1)+1)+1, 4) +
substring(emp_doh, 1, charindex('/',emp_doh,1)-1) +
substring(emp_doh, charindex('/',emp_doh,1)+1,
(CHARINDEX('/',emp_doh,charindex('/',emp_doh,1)+1) - charindex('/',emp_doh,1))-1 ),112)
I still get the same error about converting to string first...which I did. I am at a loss as to what to do. I have tried CAST and CONVERTS which it says works but doesn't. Every CAST I did worked but nothing happened, even when using off the wall CONVERTS that I knew would not work.
I also went into the table and changed VARCHAR to DATETIME and tried to CONVERT it then and it still didn't work. :crazy:
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply