MACRO or Formula to divide data based on Spacing

[richmcgill]

None of this is classified and is open to the public.

We have to load borrower data to the governments SCRA database to look for active service members.
The file that is returned has very specific spacing requirements to be able to use the information. The return file is a text file I need to get into an Excel spreadsheet. Using the Data Wizard is slow and rime consuming.
Anyone can access the user guide at their website SCRA
A typical file looks like this but 10's of thousands rows long. No headers. The headers are the Field Names.

Is there a way via formula or MACRO to add the headers to each column as well as split up the data based on its spacing?

There is a guide that helps determine the spacing aka hope to break up the return data.

Here are the headers and spacing in a spreadsheet format.

MORTGAGOR SSN	MORTGAGOR BIRTH DATE	MORTGAGOR LAST NAME	MORTGAGOR FIRST NAME	LOAN NUMBER	Active Duty Status Date	Blank	On Active Duty on the Active Duty Status Date	Left Active Duty <= Days from the Active Duty Status Date	Notified of Active Duty Recall on Active Duty Status Date	Active Duty End Date	Match Result Code	Error	Date of Match	Active Duty Begin Date	EID Begin Date	EID End Date	Service Component	EDI Service Component	Middle Name	Certificate ID
1-9	10-17	17-23	44-63	64-91	92-99	100	101	102	103	104-111	112	113	114-121	122-129	130-137	138-145	146-147	148-149	150-169	170-184

 

[rlv01]

None of this is classified and is open to the public......Anyone can access the user guide at their website SCRA
A typical file looks like this but 10's of thousands rows long. No headers. The headers are the Field Names.

Do you mean to say that there is a public website where any random internet user can dump the full names, social security numbers, DOB, and service dates for active duty service personnel? How is that not illegal?

 

[richmcgill]

Do you mean to say that there is a public website where any random internet user can dump the full names, social security numbers, DOB, and service dates for active duty service personnel? How is that not illegal?

You have to have a business purpose but you can go to the site and look at the USER guide without an account. We have an account and registered with our business. Not everyone can load information to the site and get service member information in return.

 

[rlv01]

Well that's good to know, at least. Since the data is highly structured you can extract the data fields in a number of ways.

The most expedient way would probably to use the macro recorder and record yourself opening the file and then use the 'Text to columns' wizard to import the data. Here's a macro I recorded following that method.

Sub RecordedImportMacro()
'
' RecordedImportMacro Macro
'
'
    Workbooks.OpenText Filename:="C:\Temp\DataDump.txt", Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
                       FieldInfo:=Array(Array(0, 1), Array(9, 1), Array(17, 1), Array(43, 1), Array(63, 1), _
                       Array(85, 1), Array(99, 1), Array(100, 1), Array(101, 1), Array(102, 1), Array(103, 1), _
                       Array(111, 1), Array(112, 1), Array(113, 1), Array(121, 1), Array(129, 1), Array(137, 1), _
                       Array(145, 1), Array(147, 1), Array(149, 1), Array(169, 1)), TrailingMinusNumbers:=True

    Sheets("DataDump").Select
    Sheets("DataDump").Move Before:=Workbooks("Test.xlsm.xlsx").Sheets(1)
    Columns.AutoFit
End Sub

 

[richmcgill]

Thank you so much! This has been a tremendous help!!!