SSIS import flat file(padded fileds_ to SQL

decoded
decoded used Ask the Experts™
on
Hello Expert,

I need to import a flat file to a SQL db and the issue I am having is that some fields are zero padded.

For example one fields in one row might be 21.0000 and then in the other row it might be 1.0000 or 12345.0000. for some reason when I create the advance columns the data is not being adjust right. my thinking is due to the decimals.

The person that provided the data tells me that the decimal needs to be in the excat postion.

Any ideas Experts???
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jason YousefSr. BI  Developer

Commented:
Hi,
I don't see a problem from what you're describing now, is the columns delimited or fixed width or have spaces between or what exactly?

so if you can post a sample row or two so we can look at the problem.

Author

Commented:
Hello the columns are fixed width. Example below

1236620210U$V00312092204A702VGT         1003600VANGUARD SECTOR INDEX FDS     20120131           12.00000           12.00000    71.86000PSE                                     862.32    71.86000             862.32N     0             258.69                    

1236618818U$C02733714964U108CAVM        1003600CAVIUM INC                    20120131          600.00000          600.00000    28.69000NMS                                   17214.00    28.69000           17214.00N     0            5164.20

I kept getting issue when formatting the flat file connection manager in "advance".
Jason YousefSr. BI  Developer

Commented:
is each row spanned over 3 rows? or that was one row but got wrapped?

also what do you mean by "I kept getting issue when formatting the flat file connection manager in "advance"."   if it's a fixed width, then it's a fixed width, just make sure you use the exact column width that the files were created with.

other option, is to read each row as ONE column, replace the spaces with "," for example, then split that !

you may send me a sample file here or to my e-mail and i'll show you.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
That was actually two rows...attach example of the data looks

data
but then when I divide the columns based on the documentation provide

it looks like this:

based on dooc
not sure what is going on.
Jason YousefSr. BI  Developer

Commented:
Hi, try to set CRLF as your row delimiter, in the above screen shot you're reading it as 1 column.

 if that's not a sensitive information, please upload a sample !

Author

Commented:
Attached data and i am already using  "CRLF"
datatest.txt
example.JPG
Jason YousefSr. BI  Developer

Commented:
You're cheap!! sending me 12 rows !!!

see the attached screen shot?
screen.jpg

Author

Commented:
lol...looks good can you try using the below layout:

column1      size=3
column2      size=5
column3      size=1
column4      size=1
column5      size=2
column6      size=7
column7      size=9
column8      size=12
column9      size=7
column10 size=30
column11 size=8
column12 size=18
column13 size=18
column14 size=11
column15 size=10
column16 size=16
column17 size=18
column18 size=11
column19 size=18
column20 size=1
column21 size=6
column22 size=18
column23 size=21
Jason YousefSr. BI  Developer

Commented:
I've only up to 10 columns

Author

Commented:
Thats my issue, I need to use 23 cloumns with those input column width size.
Jason YousefSr. BI  Developer

Commented:
ok, I think we have miss communications here, so you need 23 columns as your output.

for the 12  rows you sent me , can you let me know exactly what's the desired output?? or for the first row at least

Author

Commented:
sometimes its hard to explain inwriting but see attached

exsee higlight
I basicaly create the 23 column and assign each column a width size...hopefully this make sense.

column1       widthsize=3
column2       widthsize=5
column3       widthsize=1
column4       widthsize=1
column5       widthsize=2
column6       widthsize=7
column7       widthsize=9
column8       widthsize=12
column9       widthsize=7
column10  widthsize=30
column11  widthsize=8
column12  widthsize=18
column13  widthsize=18
column14  widthsize=11
column15  widthsize=10
column16  widthsize=16
column17  widthsize=18
column18  widthsize=11
column19  widthsize=18
column20  widthsize=1
column21  widthsize=6
column22  widthsize=18
column23  widthsize=21

attached
Jason YousefSr. BI  Developer

Commented:
Hi I understand but that's wrong ! looks like your numbers are wrong !! or how do you plan on storing "12.00000" ??? as 12 or 12.0 or 12.00000  ???

Author

Commented:
every column is different...

n=the amount of characters before and after the decimal
 
column 12 - (n)12.(n)5
column 13 - (n)12.(n)5
column 14 -  (n)5.(n)5
column 16 - (n)7.(n)8
column 17 - (n)15.(n)2
column 18 - (n)5.(n)5
column 19 - (n)15.(n)2
column 22 - (n)15.(n)2


Hope this helps understand the data better and again thank you in advance for taking your time!!!

Author

Commented:
Wanted to clarify something...just in case i confused you.

column 12 - (n)12.(n)5 ----12+1(decimal)+5=18 width
column 13 - (n)12.(n)5----12+1(decimal)+5=18 width
column 14 -  (n)5.(n)5----5+1(decimal)+5=11 width
column 16 - (n)7.(n)8----7+1(decimal)+8=16 width
column 17 - (n)15.(n)2----12+1(decimal)+5=18width
column 18 - (n)5.(n)5----5+1(decimal)+5=11width
column 19 - (n)15.(n)2----12+1(decimal)+5=18width
column 22 - (n)15.(n)2----12+1(decimal)+5=18width


Hope this helps!!!
Jason YousefSr. BI  Developer

Commented:
Back to this, will take a look again and let you know :)

Author

Commented:
Thank you huslayer!!!!
Jason YousefSr. BI  Developer

Commented:
OK, for that line
4566618818U$C02733714964U108CAVM        1003600CAVIUM INC                    20120131          600.00000          600.00000    28.69000NMS                                   17214.00    28.69000           17214.00N     0            5164.20                    


what's the expected output columns in excel, please open an excel file and put down the values in the 22 columns so I can compare

Author

Commented:
Its going into an sql db...not sure what you mean by "put down the values in the 22 columns so I can compare "
Jason YousefSr. BI  Developer

Commented:
something like that, so i can see what's the expected results

e

Author

Commented:
got it...please let me know if the attach helps.

ssisexcel.xlsx
Jason YousefSr. BI  Developer

Commented:
how is that looks?o

Author

Commented:
PERFECTION...the magic question is how to get it done. also it lloks like you are missing two columns based on my spreadsheet(excel).
Jason YousefSr. BI  Developer

Commented:
I don't have column 23, I don't know where u got it from?

Author

Commented:
column 17 and column 23 based off my excel spreadsheet.
Jason YousefSr. BI  Developer

Commented:
Yep I got 17,check that2
I don't have 23

Author

Commented:
Now thats perfect...how can i get it done.
Sr. BI  Developer
Commented:
the package is here, just hookup ur file and test

https://www.box.com/s/29eea28abb1e9e102876

if it didn't work as expected, send me the data that failed

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial