CODEWITHSUNDEEP
csvsql-server-2012vb6
BobSki
BobSki

Reputation: 1552

Read a .CSV file and insert it into SQL SERVER - something is missing

I have a function that looks as such...

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

Everything looks okay as in, the file gets opened, I am able to go into the file, it recognizes the data in my .csv files in this line

Line Input #iFileNo, sLine

However, when it gets to 

vasFields = Split(sline," , ")

VasFields it not being split

so when it gets to the loop :

For iIndex = 0 to Ubound(vasFields) - 1

it skips right to the end and instead hits this part

    sSQL = sSQL & "'"
    sSQL = sSQL & vasFields(iIndex)
    sSQL = sSQL & "'"

    sSQL = sSQL & ")"

So the whole VALUES part of the insert statement, only gets the single quotes in the beginning and end. Overall abbreviated insert statement looks like this..

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

Any idea why vasFields=split(sline," , ") is not getting the values?

Upvotes: 1

Views: 569

Answers (1)

Luke G.
Luke G.

Reputation: 587

The problem is the literal being used in the second parameter of the split function. It's looking for exactly a space-comma-space combination. I'd take out the spaces and try this instead:

vasFields = Split(sline,",")

Upvotes: 1

Related Questions