Convert ASCII file to an excel spreadsheet
Export a range of data in excel to ascii plain text formatted table csv or txt file using vba - Stack Overflow
Importing a huge ASCII file that doesn't have delimiters?
Fixed Width Format for ASCII/Text File based on column positions.
Videos
If I'm not mistaken, a plain text file won't justify a column at all.
That said, give this a try: http://www.excel-easy.com/vba/examples/write-data-to-text-file.html
Well, I got it working.
Sub Convert2Text()
Dim i As Integer, k As Integer, n As Integer, m As Integer
Dim Rng As Range, Dpath As String
Dim FSO As Scripting.FileSystemObject
Dim TxtFile As TextStream
Dim MaxLen As Integer
Set Rng = Application.InputBox("Please Select range.", "-Convert to Text-", , -Range("R2").Left, Range("R2").Top, , , 8)
If Rng Is Nothing Then
Exit Sub
End If
Dpath = Environ("USERPROFILE") & "\Documents\"
Set FSO = New FileSystemObject
Set TxtFile = FSO.CreateTextFile(Dpath & "Outputfile.txt", True)
MaxLen = 1
For i = 1 To Rng.Columns.Count
For k = 1 To Rng.Rows.Count
If MaxLen < Len(Rng.Cells(i, k)) Then
MaxLen = Len(Rng.Cells(i, k))
End If
Next k
Next i
MaxLen = MaxLen + 4
For i = 1 To Rng.Columns.Count
TxtFile.Write "+"
For m = 1 To MaxLen
TxtFile.Write "="
Next m
Next i
TxtFile.Write "+"
TxtFile.WriteLine
For n = 1 To Rng.Rows.Count
For i = 1 To Rng.Columns.Count
TxtFile.Write "|"
TxtFile.Write " "
TxtFile.Write " "
TxtFile.Write Rng.Cells(n, i)
For k = 1 To MaxLen - (Len(Rng.Cells(n, i))) - 2
TxtFile.Write " "
Next k
Next i
TxtFile.Write "|"
TxtFile.WriteLine
For i = 1 To Rng.Columns.Count
TxtFile.Write "+"
For m = 1 To MaxLen
TxtFile.Write "-"
Next m
Next i
TxtFile.Write "+"
TxtFile.WriteLine
Next n
MsgBox "Done!"
Shell "explorer.exe " & Dpath, vbNormalFocus
End Sub
Now, I have to focus on getting the numbers right aligned. Thank you all for your suggestions.
I have some data from the 1970s that is only saved as a massive ASCII file with 3600 columns. There are no delimiters in the file (no commas, no spaces that have meaning, no semi-colons, literally just long strings of numbers). I have an associated codebook file that tells me that certain columns are associated with certain elements of the data (for instance, telling me that "Col 1-4" correspond to one piece of data, while "Col 5" corresponds to another piece of data, etc.).
My question is: Is there some software or feature of Excel that will allow me to import the data and specify these columns without me having to use the "fixed width" option to hand delimit 3600 columns?
As an additional point of reference, I am proficient with Stata. If anyone has an alternative using Stata, I'm all ears.
Thank you so much for anyone who can offer help! This is killing me.