<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Integration Services
»
Fixed width text file and SSIS
15 posts, Page 1 of 2
1
2
»»
Fixed width text file and SSIS
Rate Topic
Display Mode
Topic Options
Author
Message
keywestfl9
keywestfl9
Posted Saturday, September 06, 2008 10:52 PM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, February 01, 2012 8:16 PM
Points: 237,
Visits: 764
i am trying to insert fixed width flat file to sql table thru import/export wizard
1. it goes to all one column
2. even if i change column in advanced option by specifying the column output width and input width matching the same, it doesnt store in right way in sql table.
can anybody tell how to do it?
thanks
Post #565098
Jeff Moden
Jeff Moden
Posted Sunday, September 07, 2008 5:31 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:10 AM
Points: 33,108,
Visits: 27,030
It'[s pretty simple with Bulk Insert and a BCP format file. Take a look in Books Online. If your were to provide the first 100 lines of the flat file and attach it to your next post as a txt file AND provide the record layout AND provide the CREATE statement for the target table, I'm thinking that somone could probably bang it out for you.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #565181
Jack Corbett
Jack Corbett
Posted Monday, September 08, 2008 8:06 AM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 11:45 AM
Points: 10,613,
Visits: 11,949
You should be able to this pretty simply in SSIS, but without any example data and information about the fixed with sizes it is hard to give you any kind of good advice.
Jack Corbett
Applications Developer
Don't let the good be the enemy of the best. --
Paul Fleming
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #565428
keywestfl9
keywestfl9
Posted Monday, September 08, 2008 11:44 AM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, February 01, 2012 8:16 PM
Points: 237,
Visits: 764
TYPE PFORM TNAME LOGONID NAME NUM
aaaaY Nsdffgg testb 14433333
bbbbRCH0C tesgggd testb 13343434
sample of the text file and there are many more columns
value of each column starts exactly the same position as the header. but in this posting it gets shifted little bit here and there.
Post #565639
hmaharaj
hmaharaj
Posted Monday, September 08, 2008 12:19 PM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 29, 2011 6:15 PM
Points: 31,
Visits: 126
using SSIS you can use your flat fiel as your source file and the SQl table as you destination. in your destination click on mapping to match all columns. if you see only one columns check your flat file connection asnd make sure your Format is correct: Delimited
then click on column and check your row and column delimeter.
Post #565667
keywestfl9
keywestfl9
Posted Monday, September 08, 2008 12:36 PM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, February 01, 2012 8:16 PM
Points: 237,
Visits: 764
it is not a delimted file. it is fixed width flat file.
Post #565687
SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
Posted Monday, September 08, 2008 3:20 PM
SSChasing Mays
Group: General Forum Members
Last Login: Saturday, October 06, 2012 4:41 PM
Points: 626,
Visits: 507
what is a row delimeter?
or it is a fixed width including the last column.
If so , when you create the flat file connection using dataflow, map the columns accordingly. you should also have the total width for the file which is marked by red line during the mapping.
If u can attach here a copy of ur text file and table code and i will try to map for you.
thanks
Post #565786
Jeff Moden
Jeff Moden
Posted Monday, September 08, 2008 7:18 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:10 AM
Points: 33,108,
Visits: 27,030
keywestfl9 (9/8/2008)
TYPE PFORM TNAME LOGONID NAME NUM
aaaaY Nsdffgg testb 14433333
bbbbRCH0C tesgggd testb 13343434
sample of the text file and there are many more columns
value of each column starts exactly the same position as the header. but in this posting it gets shifted little bit here and there.
Doesn't help me help you... whole row not displayed and was looking forward to you attaching one of the files. I'd also need the Create table statement to really help.
The suggestion immediately above this post would also work.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #565854
GAURAVKAUSHIK26
GAURAVKAUSHIK26
Posted Tuesday, September 09, 2008 3:40 AM
Grasshopper
Group: General Forum Members
Last Login: Thursday, January 14, 2010 5:18 PM
Points: 12,
Visits: 310
You can define cr/lf delimiter for getting data in rows. As far as columns are concerned, you can use substring function of sql to create and fill columns. eg: if you need to spilt 'goodluck' into 2 columns - good|luck
col1 = substring(columnName,starting position,length)
col1=substring(ColumnName,1,4).....will give 'good' from column columnName which can be filled into new created column..
Post #565995
Jeff Moden
Jeff Moden
Posted Tuesday, September 09, 2008 5:28 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:10 AM
Points: 33,108,
Visits: 27,030
GAURAVKAUSHIK26 (9/9/2008)
You can define cr/lf delimiter for getting data in rows. As far as columns are concerned, you can use substring function of sql to create and fill columns. eg: if you need to spilt 'goodluck' into 2 columns - good|luck
col1 = substring(columnName,starting position,length)
col1=substring(ColumnName,1,4).....will give 'good' from column columnName which can be filled into new created column..
Yep, that would work, as well, and it'll be pretty fast. But a Bulk Insert using a BCP Format file would be faster.
Still waiting on the OP to attach a sample file and the CREATE statement for the target table, though...
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #566044
« Prev Topic
|
Next Topic »
15 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
