阅读.csv文件,然后将其插入SQL SERVER - 缺少的东西
问题描述:
我有一个看起来是这样的功能...阅读.csv文件,然后将其插入SQL SERVER - 缺少的东西
Dim iFileNo As Integer
Dim sLine As String
Dim sSQL_InsertPrefix As String
Dim sSQL As String
Dim vasFields As Variant
Dim iIndex As Long
iFileNo = FreeFile
'Open File
Open FileName For Input As #iFileNo
If EOF(iFileNo) Then
Exit Sub
End If
sSQL_InsertPrefix = "INSERT INTO temptable (Vendor, Invoice, Date1, Date2, HoldPayment, NetRedeemed, GlCode, ServiceName, SepCheck) VALUES ("
Do Until EOF(iFileNo)
Line Input #iFileNo, sLine
'Initialize SQL STRING
sSQL = sSQL_InsertPrefix
vasFields = Split(sLine, " , ")
For iIndex = 0 To UBound(vasFields) - 1
sSQL = sSQL & "'"
sSQL = sSQL & vasFields(iIndex)
sSQL = sSQL & "'"
sSQL = sSQL & ","
Next iIndex
sSQL = sSQL & "'"
sSQL = sSQL & vasFields(iIndex)
sSQL = sSQL & "'"
sSQL = sSQL & ")"
g_cn.Execute sSQL
Loop
Close #iFileNo
一切看起来不错,如,文件被打开,我能够进入该文件,它承认在我的.csv文件中的数据在此行
Line Input #iFileNo, sLine
然而,当它到达
vasFields = Split(sline," , ")
VasFie LDS它没有被拆分
所以当它到达循环:
For iIndex = 0 to Ubound(vasFields) - 1
它跳过对末端,而是打这部分
sSQL = sSQL & "'"
sSQL = sSQL & vasFields(iIndex)
sSQL = sSQL & "'"
sSQL = sSQL & ")"
所以整个值插件的一部分声明,仅在开始和结束时获取单引号。总体略INSERT语句看起来像这样..
Insert into temptable (x, y, z) Values ('123,XXX,456') <-- in which case it treats it as a single value aI believe and gives me an error that the number of insert field has to be equal to specified values
任何想法,为什么vasFields =拆分(SLINE的, “”)没有得到值是多少?
答
问题是在分割函数的第二个参数中使用的文字。它正在寻找一个空格 - 逗号空间组合。我会拿出空格,并试试这个:
vasFields = Split(sline,",")
您确定在逗号分隔符前后有空格吗?如果不是,我会删除分割函数的第二个参数中的空格。 –