0

I got the issue to export a huge amount of Excel cells to a .txt file, which necessarily needs to in UTF-8 format. Therefore I made a VBScript, which is executed by a batch file and it totally does what it should (despite it creates a UTF-16 file).

Set file = fso.OpenTextFile(FILE, 2, True, -1)

In the documentation is mentioned that the -1 will generate a Unicode file and I am quite sure, that this is limited to UTF-16.

My questions are now: am I missing something or it is quite not possible to achieve this with VBScript? Is there an easier way? Besides that: is this platform independent?

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Pierre Begon
  • 166
  • 13

1 Answers1

2

The FileSystemObject doesn't support UTF-8, but ADODB.Stream objects do.

...

'Note: wb is the variable holding your workbook object
'Save worksheet as Unicode text ...
wb.SaveAs "C:\utf16.txt", 42

'... read the Unicode text into a variable ...
Set fso = CreateObject("Scripting.FileSystemObject")
txt = fso.OpenTextFile("C:\utf16.txt", 1, False, -1).ReadAll

'... and export it as UTF-8 text.
Set stream = CreateObject("ADODB.Stream")
stream.Open
stream.Type     = 2 'text
stream.Position = 0
stream.Charset  = "utf-8"
stream.WriteText txt
stream.SaveToFile "C:\utf8.txt", 2
stream.Close

...
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
  • Let me get this: with the stream object I am able to write UTF8 strings to my text file, despite of the format of the created file? Can I append line with this, too? – Pierre Begon Aug 26 '15 at 13:10
  • @buffo I wouldn't recommend that. Export the worksheet from Excel to a Unicode encoded text file, then read that Unicode file using the `FileSystemObject` and write it to a UTF-8 encoded file using the `ADODB.Stream` object. – Ansgar Wiechers Aug 26 '15 at 13:14
  • @pnuts That answer describes manual conversion, not scripted conversion. – Ansgar Wiechers Aug 26 '15 at 13:34
  • Thanks so far Ansgar, I will try that and it sounds reasonable.Last question to this: would a so written VBScript be executable on a Linux system? – Pierre Begon Aug 26 '15 at 13:48
  • @buffo No. To my knowledge there are no VBScript interpreters for Linux. You could convert the exported Unicode file via [`recode`](http://stackoverflow.com/a/266279/1630171), though. – Ansgar Wiechers Aug 26 '15 at 14:27
  • Thanks a lot. Your implementation just works perfectly with my changes. Chapeau. – Pierre Begon Aug 26 '15 at 14:29