Generate records from text file to CSV file

Question

I have list of IP Ranges and i need to create a csv file like below.

Input data

Start IP            
192.168.100.1       
172.1.1.1

Expected output

192.168.100.1 
192.168.101.1
-----
-----
192.168.254.1

Similarly for 172 series I need to generate the data.

172.1.1.1
172.1.2.1
173.1.3.1
----
----
173.1.254.1

I am not that good in excel. So, i thought to generate the data by PowerShell.

i can read the input either by defining it in script or can read the text file.

But i am not sure how to read the 3rd value of IP and increment it.

Anyone has any advise on it. May be some support in Regex.

foreach($line in Get-Content c:\test\ip.txt) {
    ---- Need some support here. 
}

Updated the question for better understanding.

I assume that you have your **Expected output** and **Input data** reversed. — iRon, Feb 07 '23 at 08:08
"*i can read the input either by defining it in script or can read the text file.*", please add that to your [mcve] as it is unclear how the input data is provided. — iRon, Feb 07 '23 at 09:47
Do you have many IP ranges to process? Is it always just the third part of the IP address that increments up to 254? — Joe, Feb 07 '23 at 10:16
Both of the answers are working properly. Jdweng & iRon. but i find first one is more easy to understand and modify. Hence accepting that as answer. — Roxx, Feb 07 '23 at 11:33

score 1 · Answer 1 · answered Feb 07 '23 at 10:30

$InputData =  @'
Start IP
192.168.100.1
172.1.1.1
'@ -Split '\r?\n'

Foreach ($Line in $InputData) {
    $Bytes = $Line -Split '\.'
    if ($Bytes.Count -eq 4) {
        for($i = [int]$Bytes[2]; $i -le 254; $i++) {
            $Bytes[0], $Bytes[1], $i, $Bytes[3] -Join '.'
        }
    }
}

score 1 · Accepted Answer · answered Feb 07 '23 at 10:37

1

Try following

$outputFilename = 'c:\temp\text.txt'
$ips = @("172.1.1.1", "173.1.254.1")
$splitArray = $ips.Split(',')
$table = @()
foreach($ip in $splitArray)
{
   $splitIP = $ip.Split('.')
   for($i = 1; $i -le 254; $i++)
   {
      $table += $splitIP[0] + '.' + $splitIP[1] + '.' + $i + '.' + $splitIP[3]
   }  
}
$table > $outputFilename

answered Feb 07 '23 at 10:37

jdweng

33,250
2
15
20

For [performance reasons](https://learn.microsoft.com/powershell/scripting/dev-cross-plat/performance/script-authoring-considerations#array-addition), it is a good practice to [avoid using the increase assignment operator (`+=`) to create a collection](https://stackoverflow.com/a/60708579/1701026). In other words: remove the increase assignment `$table +=` and assign the whole pipeline to the variable: `$table = @(foreach($ip in $splitArray) { ... })` (or use something like a [`List ` class](https://learn.microsoft.com/dotnet/api/system.collections.generic.list-1)) – iRon Feb 08 '23 at 08:05

score 1 · Answer 3 · answered Feb 07 '23 at 10:40

As there is already a good answer by @iRon, I'll put this here just for general learning and future info...

Because you said you weren't too great with excel, here is a fairly straightforward way to do this manually with just one formula. If you have lots of IP addresses to do, there are better ways to achieve this, but for your minimal example, it would be achievable quite quickly:

If you put your third part of the IP address in a column, and then use the ctrl key + drag the autofill handle down to increment the value to your total (254), you have all of your values required for this. E.g.:

Then in the column next to it, just add the values we've just made into your IP address using a formula such as:

="192.168."&A1&".1 "

Next, copy and paste the formula, or double click on the autofill handle of the cell containing the formula, so that you have the values all the way down to 254.

Finally, copy the formula range and paste as values (ctrl + c -> ctrl + alt+ v -> paste as values) into your CSV output sheet.

Repeat for the other IP address and save your output sheet as a CSV.

Thanks for your answer. I will check and try the same. – Roxx Feb 07 '23 at 11:35 — Roxx, Feb 07 '23 at 11:35

Generate records from text file to CSV file

3 Answers3