SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fixed width text file and SSIS


Fixed width text file and SSIS

Author
Message
keywestfl9
keywestfl9
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1265 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218562 Visits: 41997
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 Row-By-Agonizing-Row.
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45415 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
keywestfl9
keywestfl9
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1265 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.
hmaharaj
hmaharaj
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 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.
keywestfl9
keywestfl9
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1265 Visits: 764
it is not a delimted file. it is fixed width flat file.
SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1584 Visits: 565
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218562 Visits: 41997
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 Row-By-Agonizing-Row.
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GAURAVKAUSHIK26
GAURAVKAUSHIK26
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 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..
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218562 Visits: 41997
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 Row-By-Agonizing-Row.
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search