CODEWITHSUNDEEP
sqlexcelstringvba
Lulu
Lulu

Reputation: 11

Strange characters in string when importing sql file via VBA

I'm trying to import an SQL file into excel via VBA in order to return the SQL code as one string, however I'm getting tiny squares (carriage returns?) between each character when I import the SQL as a string - not a clue why! It's worked before in other files, with different SQL, so I'm not sure what I've done wrong.

Function ImportSQLText(FileExt, FileSQLName)
'//Needs Microsoft Scripting Runtime Ref
'//and microsoft active x data objects 2.8

Dim ReadText As String, newLine As String
Dim fso As FileSystemObject: Set fso = New FileSystemObject

Set txtStream = fso.OpenTextFile(FileExt & fileName, ForReading, False)

ReadText = " "
Do While Not txtStream.AtEndOfStream
    newLine = txtStream.ReadLine
    If InStr(newLine, "[input-sDte]") > 0 Then

        ReadText = ReadText & Replace(newLine, "[input-sDte]", sDte) & vbCrLf
    ElseIf InStr(newLine, "[input-eDte]") > 0 Then
        ReadText = ReadText & Replace(newLine, "[input-eDte]", eDte) & vbCrLf
    Else
         ReadText = ReadText & newLine & vbCrLf
    End If

Loop
txtStream.Close

ImportSQLText = ReadText

End Function

The SQL code looks like:

    ;if object_id('tempdb.#usr_fld') is not null

    drop table #usr_fld

    -- Creating Temp Table
    CREATE TABLE #usr_fld([PLACE-REF] NVARCHAR(50)
    ,[PROJCODE] nvarchar(100)
    ,[CUORACLE] nvarchar(100));

Any input would be greatly appreciated - googling it is not helping me this time.

Upvotes: 1

Views: 1257

Answers (1)

JNevill
JNevill

Reputation: 50308

You almost definitely have a unicode file. There is an optional fourth parameter on the OpenTextfile method of FSO:

 Const TriStateTrue = -1
 Set txtStream = fso.OpenTextFile(FileExt & fileName, ForReading, False, TriStateTrue)

Just swap out your line for these two and it might fix the issue.

Upvotes: 2

Related Questions