Reputation: 11
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
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