如何将txt文件导入数据库

[qiqi163]

求各位专家帮忙.让我可安心回家过年.
将一个txt报文,一行行导入数据库中.应该怎么做.网上查了下.用fso应该可以做到.怎么编请指教 非常感谢.有实例吗?
以下是报文格式,以"^" 分段:
ISA^00^ ^00^ ^ZZ^TRADIANT ^ZZ^CARRIERSENDERID^090105^1936^U^00401^015758190^1^P^>
GS^RO^TRADIANT^CARRIERSENDERID^20090105^193634^15758190^X^004010
ST^300^0001
B1^TRAD^15758190^20090105^N
Y7^^^^^20090124
Y1^20090112^^SCAC^O^^^^HH
Y2^4^R^HH^42GP
N9^AG^15758190
N9^BN^BOOKINGNUMBER
N9^SI^PLI00349
N9^FN^PLI00349
N9^KL^SW1203
N1^SH^TOSPO O/B
N3^LEVEL 6, THREE PACIFIC PLACE 1^QUEEN'S ROAD EAST
N4^DONGYANG^^200000^CN
N1^CN^PHILIPS LIGHTING LAMPS
N3^BRACKMILLS IND. ESTATE^GOWERTON ROAD,
N4^NORTHAMPTON^^NN4 7BW^GB
N1^R6^PHILIPS ELECTRONICS HK LTD^25^PHIL02^^ZZ
N3^LEVEL 6 THREE PACIFIC PLACE^1 QUEENS RD. EAST WANCHAI
N4^HONG KONG^^HK-ZZZ^HK
G61^RQ^CHERRY GUAN^TE^[phone]
G61^RQ^CHERRY GUAN^EM^[e-mail]
G61^RQ^CHERRY GUAN^FX^[phone]
N1^CA^CARRIER
G61^CA^ADMIN
R4^E^ZZ^GBSOU^SOUTHAMPTON^GB
R4^L^ZZ^CNSHA^SHANGHAI^CN
R4^D^ZZ^GBSOU^SOUTHAMPTON^GB
LX^1
L0^1^^^45948^G^184.8^X^84^PLT^^K
L5^1^ENERGY SAVING LAMPS
L1^1^^^99999^^^^^^^P
V1^^APL RUSSIA^^004W
SE^33^0001
GE^1^15758190
IEA^1^015758190

[swhrzl]

你一行行是指文本的换行吧？
那么用^来分段是指什么呢？
每行中的每个^都存一次吗?
ST^300^0001
B1^TRAD^15758190^20090105^N
Y7^^^^^20090124
ST 存
300 存
0001 存
B1 存?

[qiqi163]

谢谢回复。报文的每一行存进数据库里的一行记录
“^”把它分成一个个字段。存进数据库
例如第一行 ISA 有16个字段 分别存进数据库下的
id temp1 temp2 temp3 ......temp17
1 00 00........ >

接下来就是GS 这一行了
请专家详细教导

[swhrzl]

<%
Set fs = Server.CreateObject("Scripting.FileSystemObject")
File = Server.MapPath("File1.txt")
Set txt = fs.OpenTextFile(File)
TmpTxt=Txt.ReadAll
TmpArrTxt=Split(TmpText,VBCrLf)
for i=0 to ubound(TmpArrTxt)-1
TmpL=Split(TmpArrTxt(i),"^")
UpSqlStr=""
For k=0 to ubound(TmpL)
if k=0 then
IntoStr="Temp"&k
UpSqlStr="temp"&k&"='"&TmpL(k)&"'"
else
IntoStr=IntoStr & ",temp"&k
UpSqlStr=UpSqlStr & ",'"&TmpL(k)&"'"
end if
Next
if IntoStr<>"" and UpSqlStr<>"" then
conn.execute("INSERT INTO Temp ("&IntoStr&") VALUES ("&UpSqlStr&")")
End If
Next
%>

[net8guest]

如果是txt报文,以"^" 分段,以回车换行

可以 bulk insert 一次将整个txt文件导入数据库中

bulk insert 的具体使用参见 ms sql 2000 帮助说明

[swhrzl]

少了一个东西intostr

<% 
Set fs = Server.CreateObject("Scripting.FileSystemObject") 
File = Server.MapPath("File1.txt") 
Set txt = fs.OpenTextFile(File) 
TmpTxt=Txt.ReadAll 
TmpArrTxt=Split(TmpText,VBCrLf) 
for i=0 to ubound(TmpArrTxt)-1 
    TmpL=Split(TmpArrTxt(i),"^") 
    IntoStr=""
    UpSqlStr="" 
    For k=0 to ubound(TmpL) 
        if k=0 then 
            IntoStr="Temp"&k 
            UpSqlStr="temp"&k&"='"&TmpL(k)&"'" 
        else 
            IntoStr=IntoStr & ",temp"&k 
            UpSqlStr=UpSqlStr & ",'"&TmpL(k)&"'" 
        end if 
    Next 
    if IntoStr <>"" and UpSqlStr <>"" then 
        conn.execute("INSERT INTO Temp ("&IntoStr&") VALUES ("&UpSqlStr&")") 
    End If 
Next 
%>

[smartcatiboy]

[Quote=引用 4 楼 net8guest 的回复:]
如果是txt报文,以"^" 分段,以回车换行

可以 bulk insert 一次将整个txt文件导入数据库中

bulk insert 的具体使用参见 ms sql 2000 帮助说明
[/Quote]

学习ing

[swhrzl]

= =看了才知道多了点东西。啊啊。

<% 
Set fs = Server.CreateObject("Scripting.FileSystemObject") 
File = Server.MapPath("File1.txt") 
Set txt = fs.OpenTextFile(File) 
TmpTxt=Txt.ReadAll 
TmpArrTxt=Split(TmpText,VBCrLf) 
for i=0 to ubound(TmpArrTxt)-1 
    TmpL=Split(TmpArrTxt(i),"^") 
    IntoStr=""
    UpSqlStr="" 
    For k=0 to ubound(TmpL) 
        if k=0 then 
            IntoStr="Temp"&k 
            UpSqlStr="'"&TmpL(k)&"'"
        else 
            IntoStr=IntoStr & ",temp"&k 
            UpSqlStr=UpSqlStr & ",'"&TmpL(k)&"'" 
        end if 
    Next 
    if IntoStr <>"" and UpSqlStr <>"" then 
        conn.execute("INSERT INTO Temp ("&IntoStr&") VALUES ("&UpSqlStr&")") 
    End If 
Next 
%>

[swhrzl]

<% 
Set fs = Server.CreateObject("Scripting.FileSystemObject") 
File = Server.MapPath("File1.txt") 
Set txt = fs.OpenTextFile(File) 
TmpTxt=Txt.ReadAll 
TmpArrTxt=Split(TmpText,VBCrLf) 
for i=0 to ubound(TmpArrTxt)-1 
    TmpL=Split(TmpArrTxt(i),"^") 
    IntoStr=""
    UpSqlStr="" 
    For k=0 to ubound(TmpL) 
        if k=0 then 
            IntoStr="Temp"&Int(k+1)
            UpSqlStr="'"&TmpL(k)&"'"
        else 
            IntoStr=IntoStr & ",temp"&Int(k+1)
            UpSqlStr=UpSqlStr & ",'"&TmpL(k)&"'"
        end if 
    Next 
    if IntoStr <>"" and UpSqlStr <>"" then 
        conn.execute("INSERT INTO Temp ("&IntoStr&") VALUES ("&UpSqlStr&")") '这里就是插入数据库的语句，INTO Temp中的Temp是你建的表，首先你要先建立conn与数据库连接上就没问题了!
    End If 
Next 
%>

[smartcatiboy]

这栋数据用交叉表比较好

[liuhaibing0903]

UP

[ygyjack]

[Quote=引用 7 楼 swhrzl 的回复:]
= =看了才知道多了点东西。啊啊。

VB code<%
Set fs = Server.CreateObject("Scripting.FileSystemObject")
File = Server.MapPath("File1.txt")
Set txt = fs.OpenTextFile(File)
TmpTxt=Txt.ReadAll
TmpArrTxt=Split(TmpText,VBCrLf)
for i=0 to ubound(TmpArrTxt)-1
TmpL=Split(TmpArrTxt(i),"^")
IntoStr=""
UpSqlStr=""
For k=0 to ubound(TmpL)
if k=0 then
…
[/Quote]
Ding...

[qiqi163]

请问下怎么插入数据库下的表下面.比如我已经建好表.
id temp1 temp2 temp3 ......temp17