Script to Convert TXT Flat file to a Fixed width text

PowerShell Help
1 
0

I have to convert a txt file to a fixed width text file. I am learning powershell and I created a script to convert the file but I have issues with the filles (blank space between fields)

I TRY :

# Define the input and output file paths
$inputFilePath = "C:\Users\Desktop\TEST_2.TXT"
$outputFilePath = "C:\Users\Desktop\TEST_3.TXT"

# Read the content of the input file, excluding the header line
$content = Get-Content $inputFilePath | Select-Object -Skip 1

# Initialize an empty array to store the formatted records
$formattedRecords = @()

# Loop through each line in the input file
foreach ($line in $content) {
    # Split the line into fields based on whitespace (assuming fields are separated by spaces or tabs)
    $fields = $line -split '\s+'

    # Ensure that the line has at least 8 fields
    if ($fields.Length -lt 8) {
        Write-Host "Skipping record due to insufficient fields: $line"
        continue
    }

    # Extract the fields
    $recordType = $fields[0]
    $employeeCode = $fields[1]
    $facilityCode = $fields[2]
    
    # Extract the official school name, considering a maximum length of 80 characters
    $officialFacilitylName = $fields[3..10] -join ' '
    $officialFacilitylName = $officialFacilitylName.Substring(0, [Math]::Min($officialFacilitylName.Length, 80)).PadRight(80)

    # Filler length 15, start 92
    $filler = " " * 15
    
    $standardReportFlag = $fields[11]
    $transmissionDate = $fields[12]
    
    # Extract the degree period, considering a maximum length of 80 characters
    $Period = $fields[13]
    $Period = $degreePeriod.Substring(0, [Math]::Min($Period.Length, 80)).PadRight(80)

    # Combine all the fields into a formatted record
    $formattedRecord = "$recordType$employeeCode$facilityCode$officialFacilitylName$filler$standardReportFlag$transmissionDate$Period"

    # Add the formatted record to the array
    $formattedRecords += $formattedRecord
}

# Write the formatted records to the output file
$formattedRecords | Set-Content -Path $outputFilePath

# Output the number of records processed
Write-Host "Conversion complete. Processed $($formattedRecords.Count) records."
results

B08a12345D1Santa Monica California Y 12012023 FALL2023      
expected result

B08a12345D1Santa Monica California      (filler 15 charcters)         Y120120212023type or paste code here
2

Pil,
Welcome to the forum. :wave:t3:

Could you please share some (sanitized) sample lines of your input file. (formatted as code please) And you may explain a little more detailed what your task is about. At the moment it is a little ambiguous. This way we could reproduce your situation.

If your input txt file actually has a structure there might be a better way of getting structured information out of it. You may watch this video:

And BTW:

When you crosspost the same question at the same time to different forums you should at least post links to the other forums along with your question to avoid people willing to help you potentially wasting their time.

Thanks

3

Good morning Olaf

I need to convert a Txt file to format width. The TXT file is the result of a recent SQL query. Fillers between fields (empty spaces or characters used as fillers) need to be considered when determining the format width. Attached is the input file; ann the expected result.

ps1forum
ps1forum1332×345 6.57 KB

4

Hmmm … what comes to my mind first is - why don’t you export the data from an SQL query in a structured format like CSV? :man_shrugging:t3: PowerShell is able to handle CSV very easily.

It is not visible since you did not format your sample data as code as requested. Does this exported data use a tab as delimiter by any chance? In this case you could use Import-Csv. :man_shrugging:t3:

It is not visible since you did not format your expected output as code but it looks like you actually just want to delete some of the white space characters in your data. :man_shrugging:t3:

So please … do not post images of code or sample data or error messages. Instead post the plain text formatted as code.

Thanks in advance.

Edit:

The simplest and most straight forward solution would be to cut the input string into pieces and re-join these pieces as needed:

$InputString = 'd12    a1234S  DI     Santa Monica Facility            Y      12012023       FALL2023'
@(
$InputString[0..2] -join ''
$InputString[7..12] -join ''
$InputString[15,16] -join ''
$InputString[22..55] -join ''
$InputString[63..69] -join ''
$InputString[77..84] -join ''
) -join ''

The output looks like this:

d12a1234SDISanta Monica Facility            Y2012023FALL2023
5

Hi Olaf

Sorry for the late reponse, the file needs to be format width becuase it will feed another system.

Y will try your solution Thank you so much!!

Is my first time in this forum, I will keep mind how to post samples here :slight_smile: