1

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.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Lulu
  • 11
  • 2
  • 1
    "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_ᴅᴇᴠ May 08 '18 at 13:49

1 Answers1

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.

JNevill
  • 46,980
  • 4
  • 38
  • 63
  • Nice. I'd try that before rewriting everything to [use an ADODB stream](https://stackoverflow.com/a/13855268/1188513), although the [documentation](https://technet.microsoft.com/en-us/library/ee198708.aspx) seems to rather explicitly imply that FSO doesn't handle unicode. – Mathieu Guindon May 08 '18 at 13:57
  • 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. – JNevill May 08 '18 at 14:01
  • hmm, [this answer and its comments](https://stackoverflow.com/a/15422117/1188513) 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 ;-) – Mathieu Guindon May 08 '18 at 14:11
  • 1
    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. – JNevill May 08 '18 at 14:15
  • Fantastic! That got it - thank you so much! Had me completely flummoxed. – Lulu May 09 '18 at 13:22