Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Fixed width text file and SSIS Expand / Collapse
Author
Message
Posted Saturday, September 6, 2008 10:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 1, 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
Posted Sunday, September 7, 2008 5:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 36,788, Visits: 31,246
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #565181
Posted Monday, September 8, 2008 8:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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
Posted Monday, September 8, 2008 11:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 1, 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
Posted Monday, September 8, 2008 12:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Monday, September 8, 2008 12:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 1, 2012 8:16 PM
Points: 237, Visits: 764
it is not a delimted file. it is fixed width flat file.
Post #565687
Posted Monday, September 8, 2008 3:20 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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
Posted Monday, September 8, 2008 7:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 36,788, Visits: 31,246
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #565854
Posted Tuesday, September 9, 2008 3:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, September 9, 2008 5:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 36,788, Visits: 31,246
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #566044
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse