'已知SQL语句
sql_text = "select " sql_text = sql_text & "ltrim(rtrim(cast(bz_code as char(20)))) as bz_code,"'21
sql_text = sql_text & "ltrim(rtrim(cast(czrq as char(10)))) as czrq,"'11
sql_text = sql_text & "ltrim(rtrim(cast(zy_num as char(4))))as zy_num,"'5
sql_text = sql_text & "ltrim(rtrim(cast(jsdh as char(20)))) as jsdh,"'21
sql_text = sql_text & "ltrim(rtrim(cast(keshi as char(40)))) as keshi,"'40
sql_text = sql_text & "cast(price as char(10)),"'0
sql_text = sql_text & "cast(dept_name as char(40)),"'0
sql_text = sql_text & "cast(doctor as char(10)),"'0
sql_text = sql_text & "cast(fsrq as char(10))"'0 sql_text = sql_text & " From his..miHospTemp " sql_text = sql_text & "where bz_code=''''and czrq is not null and zy_num is not null and jsdh is not null and keshi is not null and bz_code is not null"
sql_text = sql_text & " Union All "
sql_text = sql_text & " select " sql_text = sql_text & "cast(class as char(1)),"'2
sql_text = sql_text & "cast(code as char(20)),"'21
sql_text = sql_text & "cast(unit as char(40)),"'41
sql_text = sql_text & "cast(kong as char(1)),"'2
sql_text = sql_text & "cast(num as char(10)),"'11
sql_text = sql_text & "cast(price as char(10)),"'11
sql_text = sql_text & "cast(dept_name as char(40)),"'41
sql_text = sql_text & "cast(doctor as char(10)),"'11
sql_text = sql_text & "cast(fsrq as char(10))"'11 sql_text = sql_text & " From his..miHospTemp" sql_text = sql_text & " Where bz_code Is Null And czrq Is Null And zy_num Is Null And jsdh Is Null And keshi Is Null"
sql_out = "EXEC master..xp_cmdshell 'bcp """ & sql_text & """ queryout c:\Temp.dat -c -Ssa -Usa -P'"
cn.Execute (sql_out)'执行上述SQL语句导出后的文本格式为:
'========================================================================================
2005-01-12 2 1333 内科
1 胆囊切除术 1 280 内科 12 45 2005-01-12
1 甲状腺切除术 1 500 内科 45 45 2005-01-12
'=========================================================================================
'再用datalength取输出每行的文本长度:'第一行:select datalength(' 2005-01-12 2 1333 内科 ')
'输出结果28,实际长度应该是:21+11+5+21+40=98,为什么这里的输出结果不对呢?'下面相同的N行:
'select datalength('1 胆囊切除术 1 280 内科 12 45 2005-01-12')
'输出结果150,长度完全正确=2+21+41+2+11+11+41+11+11=150请问我的SQL语句还要如何改动才能得到
select datalength(' 2005-01-12 2 1333 内科 ')=98 ???
sql_text = "select " sql_text = sql_text & "ltrim(rtrim(cast(bz_code as char(20)))) as bz_code,"'21
sql_text = sql_text & "ltrim(rtrim(cast(czrq as char(10)))) as czrq,"'11
sql_text = sql_text & "ltrim(rtrim(cast(zy_num as char(4))))as zy_num,"'5
sql_text = sql_text & "ltrim(rtrim(cast(jsdh as char(20)))) as jsdh,"'21
sql_text = sql_text & "ltrim(rtrim(cast(keshi as char(40)))) as keshi,"'40
sql_text = sql_text & "cast(price as char(10)),"'0
sql_text = sql_text & "cast(dept_name as char(40)),"'0
sql_text = sql_text & "cast(doctor as char(10)),"'0
sql_text = sql_text & "cast(fsrq as char(10))"'0 sql_text = sql_text & " From his..miHospTemp " sql_text = sql_text & "where bz_code=''''and czrq is not null and zy_num is not null and jsdh is not null and keshi is not null and bz_code is not null"
sql_text = sql_text & " Union All "
sql_text = sql_text & " select " sql_text = sql_text & "cast(class as char(1)),"'2
sql_text = sql_text & "cast(code as char(20)),"'21
sql_text = sql_text & "cast(unit as char(40)),"'41
sql_text = sql_text & "cast(kong as char(1)),"'2
sql_text = sql_text & "cast(num as char(10)),"'11
sql_text = sql_text & "cast(price as char(10)),"'11
sql_text = sql_text & "cast(dept_name as char(40)),"'41
sql_text = sql_text & "cast(doctor as char(10)),"'11
sql_text = sql_text & "cast(fsrq as char(10))"'11 sql_text = sql_text & " From his..miHospTemp" sql_text = sql_text & " Where bz_code Is Null And czrq Is Null And zy_num Is Null And jsdh Is Null And keshi Is Null"
sql_out = "EXEC master..xp_cmdshell 'bcp """ & sql_text & """ queryout c:\Temp.dat -c -Ssa -Usa -P'"
cn.Execute (sql_out)'执行上述SQL语句导出后的文本格式为:
'========================================================================================
2005-01-12 2 1333 内科
1 胆囊切除术 1 280 内科 12 45 2005-01-12
1 甲状腺切除术 1 500 内科 45 45 2005-01-12
'=========================================================================================
'再用datalength取输出每行的文本长度:'第一行:select datalength(' 2005-01-12 2 1333 内科 ')
'输出结果28,实际长度应该是:21+11+5+21+40=98,为什么这里的输出结果不对呢?'下面相同的N行:
'select datalength('1 胆囊切除术 1 280 内科 12 45 2005-01-12')
'输出结果150,长度完全正确=2+21+41+2+11+11+41+11+11=150请问我的SQL语句还要如何改动才能得到
select datalength(' 2005-01-12 2 1333 内科 ')=98 ???
即用SQL语句select datalength(' 2005-01-12 2 1333 内科 ')
输出的实际长度=21+11+5+21+40=98,而不是现在的28
Dim fnum As Integer
Dim file_name As String
Dim database_name As String
Dim db As Database
Dim rs As Recordset
Dim num_fields As Integer
Dim field_width() As Integer
Dim field_value As String
Dim i As Integer
Dim num_processed As Integer On Error GoTo MiscError ' Open the output file.
fnum = FreeFile
file_name = txtFileName.Text
Open file_name For Output As fnum ' Open the database.
Set db = OpenDatabase(txtDatabaseName.Text) ' Open the recordset.
Set rs = db.OpenRecordset( _
"SELECT * FROM Books ORDER BY Title") ' Start with the names of the fields.
num_fields = rs.Fields.Count
ReDim field_width(0 To num_fields - 1)
For i = 0 To num_fields - 1
' We're only working with Text here. Other
' types are different. For example, an
' integer may take 2 bytes to store but 6
' characters to display.
field_width(i) = rs.Fields(i).Size
If field_width(i) < Len(rs.Fields(i).Name) Then
field_width(i) = Len(rs.Fields(i).Name)
End If
field_width(i) = field_width(i) + 1
Print #fnum, rs.Fields(i).Name;
Print #fnum, Space$(field_width(i) - _
Len(rs.Fields(i).Name));
Next i
Print #fnum, "" ' Process the records.
Do While Not rs.EOF
num_processed = num_processed + 1
For i = 0 To num_fields - 1
field_value = rs.Fields(i).Value
Print #fnum, field_value & _
Space$(field_width(i) - _
Len(field_value));
Next i
Print #fnum, ""
rs.MoveNext
Loop ' Close the file and database.
rs.Close
db.Close
Close fnum
MsgBox "Processed " & _
Format$(num_processed) & " records." Exit SubMiscError:
MsgBox "Error " & Err.Number & _
vbCrLf & Err.Description