Need help with text file to excel

[MoeEsguerra]

Hi guys,

so I have this massive text file that I need to get into Excel.

The files is like this in text:

Alaska
Southcentral Foundation-Alaska Native Medical
315 Diplomacy Drive
Anchorage, AK 99508
Program Information:
Program: General Practice Residency 24 Months
Program Director: Dr. James M. Logan
Phone: (907) 729-6543
Accreditation Status: Approval without Reporting Requirements
Last Accreditation Visit: 2014
Next Accreditation Visit: 2021

Alabama
University of Alabama School of Dentistry at UAB
1530 3rd Avenue S.
SDB 406
Birmingham, AL 35294-0007
WebAddress: UAB - Home - UAB School of Dentistry
Program Information:
Program: Advanced Education in General Dentistry 12 Months
Program Director: Dr. James R. Pignataro Jr.
Phone: 205.934.1021
Accreditation Status: Approval without Reporting Requirements
Last Accreditation Visit: 2014
Next Accreditation Visit: 2021

There are about 200 records in this file.

I want to bring it into Excel but I need to have each line be it's own column, so like the picture attached. I don't care about the Accreditation information at the end so I will just delete that from the file.

I am okay if City State and Zip comes as one column as I can fix that afterwards.

I have tried to import this then transpose. I get all the data as one giant row. This is no bueno.

I need to have each individual line a column and then when the next entry starts, a new row, then each individual line in a column.

Any ideas on how to make this happen? And easily? I don't want cutting and pasting 200+ records to be in anyone's future...

Thank you!

 

[LockeGarmin]

Here is some code I wrote up that I think will do what you are asking for. You'll just need to put it in a new module, edit the file path and worksheet (if necessary), and run it.

Option Explicit

Sub Text_To_Excel()

Dim wsOutput As Worksheet
Dim FilePath As String
Dim FileContents As String

Dim vSection As Variant

Dim vLines As Variant
Dim lRowNumber As Long

  Set wsOutput = ActiveSheet
  FilePath = "C:\Info.txt"
  lRowNumber = 1

  With CreateObject("Scripting.FileSystemObject").OpenTextFile(FilePath, 1) 'Read Only
    FileContents = .ReadAll
    .Close
  End With

  For Each vSection In Split(FileContents, vbCrLf & vbCrLf)
    vLines = Split(vSection, vbCrLf)
    If UBound(vLines) > -1 Then 'In case there are several blank lines in the document
      wsOutput.Rows(lRowNumber).Resize(, UBound(vLines) - LBound(vLines) + 1) = vLines
    End If
    lRowNumber = lRowNumber + 1
  Next vSection
  
End Sub

 

[MoeEsguerra]

Hi! Thank you for your help. So what should I do to start this? I know I need to add a new macro but I how should I even start getting the data into the worksheet from the text file? I have it saved as a Unicode .txt and I can open it but I am stuck with how to start...

Thanks!

Here is some code I wrote up that I think will do what you are asking for. You'll just need to put it in a new module, edit the file path and worksheet (if necessary), and run it.

Option Explicit

Sub Text_To_Excel()

Dim wsOutput As Worksheet
Dim FilePath As String
Dim FileContents As String

Dim vSection As Variant

Dim vLines As Variant
Dim lRowNumber As Long

  Set wsOutput = ActiveSheet
  FilePath = "C:\Info.txt"
  lRowNumber = 1

  With CreateObject("Scripting.FileSystemObject").OpenTextFile(FilePath, 1) 'Read Only
    FileContents = .ReadAll
    .Close
  End With

  For Each vSection In Split(FileContents, vbCrLf & vbCrLf)
    vLines = Split(vSection, vbCrLf)
    If UBound(vLines) > -1 Then 'In case there are several blank lines in the document
      wsOutput.Rows(lRowNumber).Resize(, UBound(vLines) - LBound(vLines) + 1) = vLines
    End If
    lRowNumber = lRowNumber + 1
  Next vSection
  
End Sub

 

[LockeGarmin]

Hmm, I'm a little confused by your question.

Are you asking how to put this macro into excel and run it? If so see this video:

[video]https://www.youtube.com/watch?v=T--ZZSQhGqU[\video]

Before you run the code just change the following line:

FilePath = "C:\Info.txt"

To wherever the file path is on your computer and it will put it into the active spreadsheet for you.

 

[MoeEsguerra]

Hmm, I'm a little confused by your question.

Are you asking how to put this macro into excel and run it? If so see this video:

[video]https://www.youtube.com/watch?v=T--ZZSQhGqU[\video]

Before you run the code just change the following line:

FilePath = "C:\Info.txt"

To wherever the file path is on your computer and it will put it into the active spreadsheet for you.

Sorry again...I tried to do this and it is still not working. I put it in and this is what is happening...

Am I doing this right? I have to enter the Macro into excel, right? I watched the video, still don't 100% understand what I am doing...help someone, please...

This is what I did with the Macro. Do I have to open a sheet in excel? Or no? I am so lost...