Photoshop Question posted in
The official user guide can be found here.

VBA Reading From a UCS-2 Little Endian Encoded Text File – Photoshop

I have a whole bunch of text files that are exported from Photoshop that I need to import into an Excel document. I wrote a macro to get the job done and it seemed to work just fine for my test document but when I tried loading in some of the actual files produced by Photoshop Excel started putting all the data in a separate column except for the first line.

My code that reads the text file:

Open currentDocPath For Input As stream
    Do Until EOF(stream)
        Input #stream, currentLine
        columnContents = Split(currentLine, vbTab)
        For n = 0 To UBound(columnContents)
            ActiveSheet.Cells(row, Chr(64 + colum + n)).Value = columnContents(n)
        Next n
        row = row + 1
    Loop
Close stream

The text files I am reading look like this, only with much more data:

"Name"  "Data"  "Info"  "blah"
"Name1" "Data1" "Info1" "blah1"
"Name2" "Data2" "Info2" "blah2"

The problem seemed pretty trivial, but when I load it into excel, instaed of looking like it does above it looks like this:

ÿþ"Name"    "Data"  "Info"  "blah"
Name1   
Data1   
Info1   
blah1
Name2   
Data2   
Info2   
blah2

Now I am not sure why this is happening. It seems like the first two characters in the first row are there because those bytes declare the text encoding. Somehow those characters keep the first row formatted correctly while the remaining rows lose their quotation marks and all get moved to new lines.

Could someone who understands UCS-2 Little Endian text encoding explain how I can work around this? When I convert the files to ASCII it works fine.

Cheers!

edit: Okay so I understand now that the encoding is UTF-16 (I don’t know a whole lot about character encoding). My main issue is that it’s formatting strangely and I don’t understand why or how to fix it. Thanks!

Tags:

2

Answers


  1. 0

    As I mentioned in my comment, it appears the file you’re trying to import is encoded in UTF-16.

    In this vbaexpress.com article, someone suggested that the following should work:

    Dim GetOpenFile As String
Dim MyData As String
Dim r As Long
GetOpenFile = Application.GetOpenFilename
r = 1
Open GetOpenFile For Input As #1
Do While Not EOF(1)
  Line Input #1, MyData
  Cells(r, 1).Value = MyData
  r = r + 1
Loop
Close #1

    Obviously I can’t test it myself, but maybe it’ll help you.

    Login or Signup to reply.
  2. 0

    Why not just tell excel to import the file. MS has probably put hundreds of thousands of person hours into that code. Record the importation to get easy code.

    Remember Excel is a tool for non programmers to do programming things. Use it instead of trying to replace it.

    These are the replacement file functions that you use for new code. Add a reference to Microsoft Scripting Runtime.

    Opens a specified file and returns a TextStream object that can be used to read from, write to, or append to the file.

    object.OpenTextFile(filename[, iomode[, create[, format]]])

    Arguments
    object
    Required. Object is always the name of a FileSystemObject.

    filename
    Required. String expression that identifies the file to open.

    iomode
    Optional. Can be one of three constants: ForReading, ForWriting, or ForAppending.

    create
    Optional. Boolean value that indicates whether a new file can be created if the specified filename doesn’t exist. The value is True if a new file is created, False if it isn’t created. If omitted, a new file isn’t created.

    format
    Optional. One of three Tristate values used to indicate the format of the opened file. If omitted, the file is opened as ASCII.

    The format argument can have any of the following settings:

    Constant  Value  Description  
TristateUseDefault
 -2
 Opens the file using the system default.

TristateTrue
 -1
 Opens the file as Unicode.

TristateFalse
  0
 Opens the file as ASCII.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search