March 29, 2011 at 10:34 am
Hi,
I have a flatfile data which to be loaded into table.
I am using ssis.
The flatfile does't have any commas . they have empty spaces between them.
like
Belleville MIMICHIGAN 60010 20080109
when i am trying to load its coming as only 1 column.
Please help me out with this.
thanks
March 29, 2011 at 11:43 am
Are you using the import/export wizard?
March 29, 2011 at 11:45 am
Hi id
If I was certain that none of your field contain spaces I'd replace them with a separator and work my way up from there.
What do you think?
Maxim
March 29, 2011 at 4:05 pm
Are you going to have data like this?
Ann Arbor MIMICHIGAN 48103 20080109
In this case the spaces are not really delimiting your fields.
If this is the case then the first 3 spaces starting from the right will be you delimiters.
March 29, 2011 at 4:34 pm
All depends on the consistency of your data.
I would import into a one coumn statging table.
Then update the data. This is really ugly, but it works.
CREATE TABLE TEST
(
String varchar(1000)
)
INSERT INTO TEST
SELECT 'Ann Arbor MIMICHIGAN 48103 20080109'
UPDATE TEST
SET String = REVERSE(String)
UPDATE TEST
SET String = Stuff(String, CharIndex(' ', String), Len(' ')+1, ',')
UPDATE TEST
SET String = Stuff(String, CharIndex(' ', String), Len(' ')+1, ',')
UPDATE TEST
SET String = Stuff(String, CharIndex(' ', String), Len(' ')+1, ',')
UPDATE TEST
SET String = REVERSE(String)
SELECT * FROM Test
DROP TABLE TEST
You'd end up with:
Ann Arbor,MIMICHIGAN,48103,20080109
Then you would want to parse your one-column delimited string from your staging table into your multiple column destination table.
Search this site for 'parse delimited string'
Good luck...!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply