How do I add a line feed (new line) in a csv file (powershell)

gallis 1 Reputation point
2021-10-07T09:19:23.54+00:00

Hello to everybody :)
I have large csv file that should contain many records. However, for some reason, there are no line feeds or new record delimiters so as to be able to treat the various records separately (example by importing them to excel)*. Is there any way (eg with windows powershell) that I can add a line feed before a given field in the csv file? For example suppose we have an input csv file with contents :

data1,data2,data3,data4,data5,data6,data7,data8,data9,data10;data11;data12

The request is to get an output csv like this (so every record should contains 3 cells / fields....however this should be configurable)

data1,data2,data3
data4,data5,data6
data7,data8,data9,
data10,data11,data12

The above example is for illustration only. Consider that my real case contains a huge amount of data fields that I somehow need to organize.
Thank you very much in advance for every response

*Actually I have deliberately eliminated every new line feed from my source data. I did this to get rid of some unwanted newlines and other formatting characters (\t etc) that existed inside specific cells and totally messed up the structure of the data set. However, this way I lost the required newlines \n as well. Now I want to add them back, selecting the proper position they should be.

p.s. Since I am very new to powershell or scripting in general, sorry if I am making an obvious or trivial question.....

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,456 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Rich Matheisen 45,831 Reputation points
    2021-10-07T15:02:39.303+00:00

    It's not a trivial problem. It's an easily avoidable one, though! :-)

    This code assumes that the string has a multiple of columns and that there isn't any "odd" rows in the string. The one condition I'm not sure whether it's accounted for is empty columns (e.g., something like "1,,3,4"). Nor does the code expect to find commas in a column's data!

    $d = "data1,data2,data3,data4,data5,data6,data7,data8,data9,data10,data11,data12"
    
    # a hash of column names
    $row =  [ordered]@{
                A = ""
                B = ""
                C = ""
                D = ""
            }
    # get just the names of the columns in a row
    [array]$cols = $row.Keys
    
    $lastchar = $d.length
    $startsearch = 0
    
    While ($startsearch -lt $lastchar){
        0..($cols.length - 1)|
            ForEach-Object{
                $pos = $d.Substring($startsearch,$lastchar).IndexOf(",")   # zero based
                $row[$cols[$_]] = $d.Substring($startsearch,$pos)
                $startsearch = $startsearch + $pos + 1
                $lastchar = $lastchar - $pos -1
            }
            [PSCustomObject]$row
    } Export-CSV SomeFileName.csv -NoTypeInformation
    
    0 comments No comments