Importing text to Excel with specific formatting?

Factual Questions
1

I have a bunch of text files (simple ASCII .txt files) that I need to import into Excel. The tricky part is that I need each character in the text files to go into it’s own cell in Excel. There are no commas or tabs (or anything else to use as a delimiter) between the needed characters, so I don’t think it’s as straightforward as a regular text import.

It seems like this might be accomplished with either a script in Excel or by operating on the text file itself and inserting a space between each of the characters. Unfortunately, I do not know how to do either of those things. . . My Google searches haven’t really turned up a straightforward explanation or a matching requirement.

I am using Windows XP and Excel 2003. I have a bunch of these files to process so an automated type of process would be best, even if it takes a bit of effort to setup initially.

Can anyone help me get this accomplished? Many thanks to all respondents!

2

You could go into the Import wizard (Data | Import External Data | Import Data…) and instead of clicking the “Delimited” button on the first page, click “Fixed Width”. On the second page, though, you would need to click between every single character. That would get you what you want, but it would get tedious really fast with a large file or with many files. Hopefully somebody out there with more Excel experience than me can give you a better solution.

3

What shoeless said will allow you to parse out the text by each character. You can make a macro that does text to columns but you would probably have to import it from a query. I have an older version of Excel and it won’t recognize Word in the macro like it would another Excel file.

How many files do you have to import and are they all the same column width of information? If they are not all equal in column width then you need to click on enough columns to cover the largest set of data when you set up the macro.

4

See if any of the solutions suggested in this thread will work as an intermediate step to produce a delimited file Excel can handle automatically.

5

The following VBA code will work:



Option Explicit

Sub ReadFile()

Dim sFileName As String, sChar As String
Dim Col As Integer, Row As Long

sFileName = "c:\Temp	est.txt"

Open sFileName For Input As #1
Col = 1: Row = 1

Do While Not EOF(1)
    sChar = Input(1, #1)
    If sChar = Chr(13) Then
        Col = 1
    ElseIf sChar = Chr(10) Then
        Row = Row + 1
    Else
        Cells(Row, Col) = sChar
        Col = Col + 1
    End If
Loop

Close #1

End Sub