From .txt into .csv with set field lengths but no delimeter

[wellconnit]

Hi All,

Hoping to get some advice on how to make this work.

I’ve got a file that gets generated from an external system we use for phone call tracking, unfortunately the daily readouts are .txt files, not CSV’s and our new billing system wants CSV’s.

So I was thinking of using powershell to convert between the two, but I’m stuck with the splitting the variable into a new array so that we can write that to a CSV.

There is no Header Row, so we are setting the Header to “Value” on the way in.

We can Write-Host the $line variable and it will display, but it won’t write the substring into the $CTOP variable.

If anyone has a better / more efficient way of doing this I’d love to hear your thoughts.

I’ve attached the file that i’m importing(with some masks in there)

$var = Import-CSV "C:\File.txt" -Header "Value"

foreach ($line in $var) {
Write-Host $line
$line = $line.Value.ToString()
$CTOP = @()
$CTOP[1] = $line.Substring(0,6)
$CTOP[2] = $line.Substring(7,20)
$CTOP[3] = $line.Substring(27,6)
$CTOP[4] = $line.Substring(33,6)
$CTOP[5] = $line.Substring(39,6)
$CTOP[6] = $line.Substring(45,48)
$CTOP[7] = $line.substring(93,14)
$CTOP[8] = $line.substring(107,14)
$CTOP[9] = $line.substring(121,24)
$CTOP[10] = $line.substring(145,24)
$CTOP[11] = $line.substring(169,10)
$CTOP[12] = $line.substring(179,18)
$CTOP[13] = $line.substring(197,18)
$CTOP[14] = $line.substring(215,18)
$CTOP[15] = $line.substring(233,18)
$CTOP[16] = $line.substring(251,6)
$CTOP[17] = $line.substring(257,10)
$CTOP[18] = $line.substring(267,3)
$CTOP[19] = $line.substring(270,22)
$CTOP[20] = $line.substring(292,10)
$CTOP[21] = $line.substring(302,6)
$CTOP[22] = $line.substring(308,6)
$CTOP[23] = $line.substring(314,18)
$CTOP[24] = $line.substring(332,10)
$CTOP[25] = $line.substring(342,10)
$CTOP[26] = $line.substring(352,255)
$CTOP[27] = $line.substring(607,96)
$CTOP[28] = $line.substring(703,6)
$CTOP[29] = $line.substring(709,6)
$CTOP[30] = $line.substring(715,48)
$CTOP[31] = $line.substring(763,238)
$CTOP | Export-CSV "Converted.csv" -Append -NoTypeInformation
}

fdb03276-77f0-48d1-bc02-c7179b6ba25f-CTOP_2000032812_0000003950_20220105202809.txt (1.96 KB)

[Neally]

Welcome

If you post code, please use the ‘Insert Code’ button. Please and thank you!

PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum!

Hi, and welcome to the PowerShell forum! Don’t apologize for being a “noob” or “newbie” or “n00b.” There’s just no need – nobody will think you’re stupid, and the forums are all about asking questions. Just ask! Use a descriptive subject. Don’t say “Need help” or “PowerShell Help”, actually summarize what the problem is. It helps the rest of us keep track of which problem is which. Don’t post massive scripts. We’re all volunteers and we don’t have time to read all that, nor will we copy…

• Neally Bot

[Neally]

it would help if you post a sample input and what you need the result to look like?

$var = Import-CSV "C:\File.txt" -Header "Value"

possibly ‘get-content’ would be better and then convert it ‘covertfrom-csv’ but details depend on the input file and such, so a sample input and desired output would be helpful.

[wellconnit]

@alexw

Thanks for getting back to me so quickly. I’ve just added the sample file.

Basically I just need to get the sample text and cut it into 31 different parts based on the substring command(keeping in mind there are multiple lines per file).

[Neally]

uh can you check that the file looks right?

looks like the formatting is super messed up?

also as as side note, an array starts at zero

$array[0] is the first element in the array, not $array[1]

[wellconnit]

Yep, Unfortunately that’s how it is.

It’s just a text file with columns at certain character points, it’s not a csv file.

That’s why we need to change it from a .txt to a CSV file using certain spots in the line as the delimeters for the columns.

Basically the output it garbage, they should have given it to us as a CSV, but it seems they only output text.

If you turn off Word-Wrap it’ll all be on one line.

[Neally]

no, even without word wrap it’s not a one liner

some output has leading whitespace, is that desired?

" 3"
vs
“3”

[wellconnit]

Sorry yeah it’s 2 lines, the top one is just the header text

And the second one is the data.

Yep the whitespace is on purpose too.

That’s why we need to break up the content.

It’s an extract of a phone call log, if a call is a mobile call then there may be extra information, or if it is international it may have country information, or costings, or other stuff in that white space, it’s also used for usage data for internet services, so it could have upload / download values for data also, that’s why in this instance there is white space.

The substring locations are fine, it’s more that it’s not outputting to the CSV.

[Neally]

so the output should be something like this??

some rows only have a zero, or no data and I get some substrings that don’t work.

PWTHDR             PWTDET
ABCD20000328120    054219899505601276B
000003             99542
950202             10947
etc

this is a start

$line = get-content "D:\TestFolder\input.txt"
$CTOP = New-Object System.Collections.Generic.List[string]
$CTOP.add("$($line.Substring(0,6))")
$CTOP.add("$($line.Substring(7,20))")
$CTOP.add("$($line.Substring(27,6))")
$CTOP.add("$($line.Substring(33,6))")
$CTOP.add("$($line.Substring(39,6))")
$CTOP.add("$($line.Substring(45,48))")
$CTOP.add("$($line.substring(93,14))")
$CTOP.add("$($line.substring(107,14))")
$CTOP.add("$($line.substring(121,24))")
$CTOP.add("$($line.substring(145,24))")
$CTOP.add("$($line.substring(169,10))")
$CTOP.add("$($line.substring(179,18))")
$CTOP.add("$($line.substring(197,18))")
$CTOP.add("$($line.substring(215,18))")
$CTOP.add("$($line.substring(233,18))")
$CTOP.add("$($line.substring(251,6))")
$CTOP.add("$($line.substring(257,10))")
$CTOP.add("$($line.substring(267,3))")
$CTOP.add("$($line.substring(270,22))")
$CTOP.add("$($line.substring(292,10))")
$CTOP.add("$($line.substring(302,6))")
$CTOP.add("$($line.substring(308,6))")
$CTOP.add("$($line.substring(314,18))")
$CTOP.add("$($line.substring(332,10))")
$CTOP.add("$($line.substring(342,10))")
$CTOP.add("$($line.substring(352,255))")
$CTOP.add("$($line.substring(607,96))")
$CTOP.add("$($line.substring(703,6))")
$CTOP.add("$($line.substring(709,6))")
$CTOP.add("$($line.substring(715,48))")
$CTOP.add("$($line.substring(763,237))")
$ctop

[wellconnit]

I’ve attached a photo of what each part of the line represents.

Each part of the long string corresponds to the tables.

So some columns shouldn’t have anything in them, whereas others will have a phone number, another will have the destination phone number, another will have the cost.

[wellconnit]

Here’s the first table.

[Neally]

the last line is a problem, 1001 is out of bound

$($line.substring(763,238) → out of bound error

$($line.substring(763,237) → works ok

is this more like what you need?

again I have no idea what you need the output to look like.

CSV is a format, comma-separated values, not a way for things to look, so just saying you need it as CSV does not help a ton

[wellconnit]

Basically where the new ‘column’ starts from the table, I need there to be a comma so that when I upload it as a CSV it knows what the different headings are.

First column is the substring 0,6

Second column is the substring 7,20

Etc, until the end.

The last substring may have an extra one you may be right which could be throwing the OOB.

But otherwise each time there is a substring it should make it a new column in the csv, and then when that’s finished it should loop to the next line and restart the process.

Do you think this is the best approach?

[Neally]

@wellconnit

Each time there is a substring it should make it a new column in the csv, and then when that’s finished it should loop to the next line and restart the process.

aha! that’s more helpful!

$inputFile = get-content "D:\TestFolder\input.txt"

$csv = 
foreach($line in $inputFile){
    $row = 
    @(
        "`"$($line.Substring(0,6    ).trim())`""
        "`"$($line.Substring(7,20   ).trim())`""
        "`"$($line.Substring(27,6   ).trim())`""
        "`"$($line.Substring(33,6   ).trim())`""
        "`"$($line.Substring(39,6   ).trim())`""
        "`"$($line.Substring(45,48  ).trim())`""
        "`"$($line.substring(93,14  ).trim())`""
        "`"$($line.substring(107,14 ).trim())`""
        "`"$($line.substring(121,24 ).trim())`""
        "`"$($line.substring(145,24 ).trim())`""
        "`"$($line.substring(169,10 ).trim())`""
        "`"$($line.substring(179,18 ).trim())`""
        "`"$($line.substring(197,18 ).trim())`""
        "`"$($line.substring(215,18 ).trim())`""
        "`"$($line.substring(233,18 ).trim())`""
        "`"$($line.substring(251,6  ).trim())`""
        "`"$($line.substring(257,10 ).trim())`""
        "`"$($line.substring(267,3  ).trim())`""
        "`"$($line.substring(270,22 ).trim())`""
        "`"$($line.substring(292,10 ).trim())`""
        "`"$($line.substring(302,6  ).trim())`""
        "`"$($line.substring(308,6  ).trim())`""
        "`"$($line.substring(314,18 ).trim())`""
        "`"$($line.substring(332,10 ).trim())`""
        "`"$($line.substring(342,10 ).trim())`""
        "`"$($line.substring(352,255).trim())`""
        "`"$($line.substring(607,96 ).trim())`""
        "`"$($line.substring(703,6  ).trim())`""
        "`"$($line.substring(709,6  ).trim())`""
        "`"$($line.substring(715,48 ).trim())`""
        "`"$($line.substring(763,237).trim())`""
    )
    $row -join ","
}

$csv | Out-File "phonefile.csv"

33031eec-10c3-40f8-81cc-e96a62231474-phonefile.txt (780 Bytes)

[Neally]

if you don’t need the double quotes around , just remove the (`")
Not 100% which CSV layout you need, but you can easily switch

"PWTHDR","ABCD20000328120","000003","950202" 
# ^ this is the default CSV format when you export data from Excel to CSV
vs
PWTHDR,ABCD20000328120,000003,950202

"`"$($line.Substring(0,6    ).trim())`""
#VS
"$($line.Substring(0,6    ).trim())"

[wellconnit]

Thank you so much for all of your help!

Finally have a result that I can work with !

You definitely are the Powershell Expert!

Thanks for everything !