Strange characters in string when importing sql file via VBA

Question

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.

"getting tiny squares between each character" -> probably reading UTF16 as ANSI/ASCII/other 8 bit encoding - try use a hex editor to check source file. — LS_ᴅᴇᴠ
– LS_ᴅᴇᴠ, Commented May 8, 2018 at 13:49

JNevill · Accepted Answer · 2018-05-08 13:56:48Z

2

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.

edited May 8, 2018 at 13:56

answered May 8, 2018 at 13:54

JNevill

50.6k4 gold badges46 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mathieu Guindon Over a year ago

Nice. I'd try that before rewriting everything to use an ADODB stream, although the documentation seems to rather explicitly imply that FSO doesn't handle unicode.

JNevill Over a year ago

Agreed. Jet/Ace are so good at making bad decisions on text and excel files too. Before you know it you are writing a schema.ini on the fly and dropping System DSNs into registry on run time just to make things work. Although, I do love me some ADODB.

Mathieu Guindon Over a year ago

hmm, this answer and its comments strongly imply that FSO wouldn't handle UTF-8 encoding (UTF-8 != Unicode), making ADODB.Stream the only remaining option, depending on the actual encoding of OP's file. FWIW ADODB.Stream has nothing to do with Jet/Ace or databases, it's just a file stream ;-)

JNevill Over a year ago

I'm holding out that the TriStateTrue parameter will do the trick. UTF-16LE which is the windows default unicode is a two-byte scheme and OP is saying there is one extra character popping up in between each character. UTF-8 is three bytes (but is often only two) so... it's a 50/50. Didn't know that about ADODB.stream. I'll have to read up on that one.

Lulu Over a year ago

Fantastic! That got it - thank you so much! Had me completely flummoxed.

Collectives™ on Stack Overflow

Strange characters in string when importing sql file via VBA

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related