January 3, 2012 at 11:52 am
Hello Guys/Gals,
I have a spreadsheet that I am converting to a flat file.
Spreadsheet:
-Identity Code
-Account #'s
-Address lines (
-Shareholdings (Amount of stock someone owns)
-And a few more columns with data
etc...
Flat File:
-Account #'s
-Address Lines
-Shares
Now my flat file needs to be in a fixed format that is and the shares position needs to be padded out with zero's to fulfill 18 character length. I am not quite sure how to achieve this or even manipulate the data.
Please assist.
January 3, 2012 at 1:42 pm
Not sure if you want left or right padding so I will take a stab that you want them right filled. Let's say you have AccountNum of variable length and you want to make it fill with 0's to the right for a total field length of 15.
select left(cast(AccountNum as varchar(15)) + replicate('0', 15), 15) as AccountNum
Just add this same type of logic to each column. If you want the 0's on the other side just switch them in the select and take right instead of left.
--edit-- fat fingers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 3, 2012 at 1:54 pm
Right idea Sean but I believe he'll need to use a derived column instead, since I'm assuming he's using SSIS. Can replace the existing but I usually prefer a new one for things that can change the metadata formatting, and you need this to be a string when done.
What you'll want is an expression similar to the following:
RIGHT( REPLICATE( (DT_STR,30,1252)"0", 18) + (DT_STR,30,1252) [Col1], 18)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 5, 2012 at 9:55 am
Evil Kraig,
Thanks for that expression. Really on the money with that. Taking that into consideration, how would I add zero's to the end of the string as well as in the beginning.
Your expression showed me that it added the 0's at the beginning of the string to pad out until the length reaches 18 characters.
Now considering that, I have a column of share positions whole positions and decimal positions.
Ex.
-What I have:
[Column1]
123456.00000
.12345
12345.12345
-What I want:
000000123456000000
000000000000123450
000000123451234500
January 5, 2012 at 9:58 am
Note: Assume the column is right justified
[Column1]
123456.00000
.12345
12345.12345
January 5, 2012 at 9:59 am
Just need to reverse it.
RIGHT( REPLICATE( (DT_STR,30,1252)"0", 18) + (DT_STR,30,1252) [Col1], 18)
becomes
LEFT((DT_STR,30,1252) [Col1] + REPLICATE( (DT_STR,30,1252)"0", 18), 18)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 5, 2012 at 11:23 am
fabulous617 (1/5/2012)
Now considering that, I have a column of share positions whole positions and decimal positions.Ex.
-What I have:
[Column1]
123456.00000
.12345
12345.12345
-What I want:
000000123456000000
000000000000123450
000000123451234500
So, you're assuming 6 decimal positions and 14 on the whole number? You'll have to break them into separate components and then recombine them as text. For example, I'd first use a derived column that would break them into their whole and decimal components, so I knew I was dealing with xxxxx.00 and 0.xxxxx. Now, I'd turn them into unique strings after multiplying your decimal by 100000 to turn it into a whole number first with 6 positions. Now pad the original whole number portion and then concatonate the two.
EDIT: Or you could just multiply your number by 100000 before doing anything and trimming off any trailing decimals, then padding as you already were... 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply