Excel 首字母大写
Categories: Development Notes • 分享; Tagged with: Excel; @ November 28th, 2012 20:38使用Proper函数只能做到每个单词的首字母大写， 该单词的其余字母会变小写。
但如果有时只需要将首字母大写，剩余字符保持不变， 此种情况下可使用公式操作：
= UPPER(LEFT(A2))&RIGHT(A2,LEN(A2)-1)
SSIS: Append Header/Footer to flat file
Categories: Database; Tagged with: CMD • DTSX • ETL • SSIS • VB; @ November 25th, 2012 17:52Requirement:
Append additional header/footer to the destination flat file:
Solution:
Header/Footer can be added by several ways in SSIS, here is 3 ways I’m using:
Usinig Union in SQL :
-- Header
Select 'START' + convert(varchar(8),GETDATE(), 112)
-- Body
Union All
Select FULLNAME
from OVERSEA_ADDRESS
WHERE POSTAL_CODE = ''
-- Footer
Union All
Select 'END'+ Convert(varchar(8), Count(*))
from TWO_FA_OVERSEA_ADDRESS
WHERE POSTAL_CODE = ''
But sometimes the requirement is more complex, then we need to use Script task.
Script task using VB/C#
We can read/write file using VB or C#, and we can get the variables/paremeters, so basically, we can handle most of the requirement by Script task.
Reading file using VB:
Dim objReader As New System.IO.StreamReader(FILE_NAME)
' Read each line, and append the text into textLines
Dim currentLine As String
Do While objReader.Peek() <> –1
currentLine = objReader.ReadLine()
textLines.Add(currentLine)
Loop
Writing to file
' For loop the textLines
Dim index As Integer
For index = 0 To textLines.Count – 1
Dim text As String = textLines.Item(index).ToString
outStreamWriter.Write(text & vbNewLine)
Next outStreamWriter.Close()
Using copy CMD
Generate the header, body, foot file and use CMD to combine the files:
copy header.txt+body.txt+footer.txt output.txt /y
SSIS: Get Variables/Properties In Script task
Categories: Database; Tagged with: DTSX • ETL • SQL Server • SSIS • VB; @ November 25th, 2012 15:52Requirement:
Get variables/properties in Script task.
Note: Do remember to set the variables in the component editor.
Solution(in VB):
1. Get properties:
MsgBox(Dts.Connections(“fileDest”).ConnectionString)
2. Get variables:
MsgBox(Dts.Variables(“User::Counter”).Value.ToString)
Use expression to get dynamic data in SSIS package (file name with date)
Categories: Database; Tagged with: SSIS; @ November 21st, 2012 18:40Requirement:
Destination is flat file, and the file name should contains date: YYYYMMDD.
Solution:
Create one variable, stores the dir of the destination file.
Then use Expression to update the connection string of the file destination.
@[User::FileDestrination] +
"OVERSEAS_USER_INFO_"+
(DT_STR,4,1252) DatePart("yyyy",getdate()) +
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + ".txt"
Small Flex Tool for parsing column names to SQL format string
Categories: Development Notes • Flex • 分享; Tagged with: Flex • SQL • Tools; @ November 19th, 2012 18:40Hi all,
I wrote a small Flex tool for parsing column names to SQL format, for example:
when I want to select those fields:
| ColumnName |
| ACCOUNT |
| SECURITYEXCHANGE |
| ORDREFNO |
| ORDERID |
| BUYSELL |
I need to copy those fields into my SQL editor, and put “,” for each of them, it’s boring and waste time, So I wrote a Flex tool, which can parse the fields into:
1: Added comma:
ACCOUNT,
SECURITYEXCHANGE,
ORDREFNO,
ORDERID,
BUYSELL
2: Added quotation marks and comma:
‘ACCOUNT’,
‘SECURITYEXCHANGE’,
‘ORDREFNO’,
‘ORDERID’,
‘BUYSELL’
Hope it can save your time if you are doing such kind of SQL jobs.
Get the tool: http://liguoliang.com/wp-content/uploads/2012/11/ParseStrForSQL.swf