Importing a txt file with column delimiters (;) but no CR or LF, how to rewrite file

Question

I have a txt file from a supplier that has column delimiters ";" however it has no line breaks at all. (I have never come across this before as ll normally are straight forward.)

I have counted and it has 40 columns and I would like to import the file into SQL using SSIS.

I am expecting to write a script to insert a LF or CR or both after the 40th ; but am unsure how to even compose the google search for that.

I am ok using Power shell or Python, but do not really know where to start.

can anyone point me in the right direction please.

user_11025476 · Answer

I would use the -replace operator for this:

(Get-Content file.csv) -replace '((?:[^;]*;){39}[^;]*);',"`$1`r`n" |
    Set-Content file.csv

-replace has a regex matching part (before the comma) and a substitution part (after the comma). (?:) ensures there is no capture group for the match. [^;] matches anything not ;. ; matches ;. {39} matches 39 consecutive times of the previous match or match group. We use 39 because the 40th capture is in the regex just after. * matches zero or more of the previous match/group (greedy matching).

$1 substitutes capture group 1. This needs to be literal so that PowerShell doesn't parse it as a variable. This is why the $ is escaped. Capture group 1 is everything matched inside the first set of () (do not count (?:) as that is a non-capturing group). `r`n is CRLF.

You can achieve a similar result with the Regex class method Replace():

[regex]::Replace((Get-Content file.csv),'([^;]*;){40}',{$args.Value -replace ';$',"`r`n"}) |
    Set-Content file.csv

The Replace() method works similarly to the operator. However, its substitution can accept a script block. The match object returned from the match is passed as an argument into the script block. From there, you can do string manipulation.

The -replace operator here is used to replace the last ; of the matched text with CRLF.

In PowerShell v7+ or PowerShell Core, the -replace operator supports script block substitutions using input variable $_ as the matched object:

(Get-Content file.csv) -replace '([^;]*;){40}',{$_.Value -replace ';$',"`r`n"} |
    Set-Content file.csv

user_45375 · Answer

To complement AdminOfThings' helpful answer, which provides PowerShell-idiomatic solutions:

An alternative is to make direct use of the .NET [regex] type (System.Text.RegularExpressions.Regex), whose .Matches() method captures all matches of a given regex in a collection.

# Sample input file content with 3 columns.
$content = '1;2;3;4;5;6;7;8;9'  # to read from a file: Get-Content in.csv

# Split the content into strings of N fields and write them as individual
# lines to an output file via Set-Content.
# Replace {2} with {39} to capture 40 fields.
Set-Content -Encoding utf8 out.csv -Value `
  ([regex] '((?:[^;]*;){2}[^;]*);?').Matches($content).
    ForEach({ $_.Groups[1].Value })

Regex ((?:[^;]*;){2}[^;]*);? captures 3 ;-separated fields at a time in a capture group ((...)) and, if present, consumes the following ;
.ForEach({ $_.Groups[1].Value }) then extracts the value of the (first and only) capture group from each match (System.Text.RegularExpressions.Match)

After running the above, out.csv has the following content:

1;2;3
4;5;6
7;8;9

user_9164015 · Answer

Here is a starting point for you if you want a semi-colon delimited string split in to forty objects per line. I would suggest taking a look at Set-Content for updating your file.

Here's my sample string:

$text = "1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;"

The first thing I will do is define an output array for the final lines of data and split the string in to an array of items using ; as the delimiter.

$Lines = @()
$TextArray = $Text.Split(";")

Then we can loop through the array and increment the count every forty objects while the count is less than the total objects in the array. Within the loop, we will use the current value of the loop or $i and add 39 to it. This effectively gives us a new line in the output object that is $TextArray[0..39], $TextArray[40..79] and so on.

for ($i = 0; $i -lt $array.Count; $i += 40) {
    $To = $i + 39
    $Lines += @($array[$i..$To]) -join ";"
}

Output:

PS Ash> $Lines
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40
1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40

I am sure you can then piece together what you need to do to update your file and add it to your database.

user_398519 · Answer

Here is one way to do it. It reads in the file, uses Regex to split at 40 (note you will need to change this in the script noted by a comment) ; then stores it in a string, substrings out what was added to $Data, and repeats. Then it writes it to a file on new lines, and allows you the use it as a CSV. The main caveat is if your CSV file has ; in the columns data that will throw it off.

$Test = "test;test;test;test;test;test;test;test;test;test;test;test;test;test;test;est;test;test;test;test;test;test;test;test;test;test;test;test;test;"
$Data = ""
while ($Test.Length -gt 0) {        
    if ($Test -Match "(?s)^.*?(?:;.*?){5}") { # Change 5 to 40
        $Data += "$($Matches[0])`r`n"    
        #Reset the main string the matches length up and the total length minus that
        $Test = $Test.SubString($Matches[0].Length, ($Test.Length - $Matches[0].Length)) 
    }else {
        Write-Output "$test was left over"
        break # we have an odd number of ;
    }        
}

$Data | Out-File -enc Ascii -Path c:	emp\Output.csv

$RowedData = Import-CSV -Path "c:	emp\output.csv" -Delimiter ";"

user_10376537 · Answer

I assume since you tagged SSIS you are on SQL Server and if you are SQL Server 2016+, depending on the size or length you could attempt to do this in t-sql.

Convert the string to JSON and use OPEN_JSON() so we can maintain the correct order, do some math and then generate a "line" number based the 40, and use ROW_NUMBER partitioned by that generated line number to get each individual column and then pivot.

Example:

DECLARE @testdata NVARCHAR(MAX) = '1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;';

SELECT *
FROM   (
           SELECT CONVERT(INT, [Key]) / 40 + 1 AS [LineNumber]  --Increment based on groups of 40.  First set of 40 is 1, second set is 2, etc...
                , [Value]
                , CONCAT(   'Column'
                          , ROW_NUMBER() OVER ( PARTITION BY CONVERT(INT, [Key]) / 40 + 1
                                                ORDER BY CONVERT(INT, [Key])
                                              )
                        ) AS [ColumnNumber] --This would be assigning a column number 1-40
           FROM   OPENJSON(CONCAT('["', REPLACE(@testdata, ';', '","'), '"]'))  --Here we are just converting the string to JSON
       ) AS [spt]
PIVOT (   --Then pivot here
          MAX([Value])
          FOR [ColumnNumber] IN ( [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8], [Column9], [Column10], [Column11], [Column12], [Column13], [Column14]
                                , [Column15], [Column16], [Column17], [Column18], [Column19], [Column20], [Column21], [Column22], [Column23], [Column24], [Column25], [Column26], [Column27]
                                , [Column28], [Column29], [Column30], [Column31], [Column32], [Column33], [Column34], [Column35], [Column36], [Column37], [Column38], [Column39], [Column40]
                                )
      ) AS [pvt];

Giving you results of:

LineNumber  Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8 Column9 Column10 Column11 Column12 Column13 Column14 Column15 Column16 Column17 Column18 Column19 Column20 Column21 Column22 Column23 Column24 Column25 Column26 Column27 Column28 Column29 Column30 Column31 Column32 Column33 Column34 Column35 Column36 Column37 Column38 Column39 Column40
----------- ------- ------- ------- ------- ------- ------- ------- ------- ------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- --------
1           1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40
2           1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40
3           1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40
4           1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40
5           1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40
6           1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40
7           1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40
8           1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40
9           1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40
10          1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40
11          1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40
12          1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40
13          1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40
14          1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40
15          1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40
16          1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40
17          1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40
18          1       2       3       4       5       6       7       8       9       10       11       12       13       14       15       16       17       18       19       20       21       22       23       24       25       26       27       28       29       30       31       32       33       34       35       36       37       38       39       40

Build a stored procedure, extract the file data and pass that to the stored procedure and have it return the result set or do an insert right there into a staging table. Not 100% sure how it would perform for you, but might be an option to explore.

UPDATE: I did a performance test, I was curious. I used the below modified code to build out a string that ended up being 1,444,000 characters long and it was generating 4001 rows in under 5 seconds:

DECLARE @datastring NVARCHAR(MAX) = 'one;two;three;four;five;six;seven;eight;nine;ten;eleven;twelve;thirteen;fourteen;fifteen;sixteen;seventeen;eighteen;nineteen;twenty;twenty-one;twenty-two;twenty-three;twenty-four;twenty-five;twenty-six;twenty-seven;twenty-eight;twenty-nine;thirty;thirty-one;thirty-two;thirty-three;thirty-four;thirty-five;thirty-six;thirty-seven;thirty-eight;thirty-nine;forty;';
DECLARE @loop INT = 0;
DECLARE @testdata NVARCHAR(MAX) = '';

--Generate larger string
WHILE @loop <> 4000
    BEGIN
        SET @testdata = @testdata + @datastring;
        SET @loop = @loop + 1;
    END;

SELECT LEN(@testdata);

CREATE TABLE #testdata (
testdata NVARCHAR(MAX)
)

--load it to temp table
INSERT INTO [#testdata] (
                            [testdata]
                        )
VALUES ( @testdata)

--Below was returning results in about 3 seconds and the length of what was generated is 1,444,000 characters
SELECT *
FROM   (
           SELECT CONVERT(INT, [Key]) / 40 + 1 AS [LineNumber] --Increment based on groups of 40
                , CONVERT(NVARCHAR(5), [Value]) AS [Value]
                , CONCAT(   'Column'
                          , ROW_NUMBER() OVER ( PARTITION BY CONVERT(INT, [Key]) / 40 + 1
                                                ORDER BY CONVERT(INT, [Key])
                                              )
                        ) AS [ColumnNumber]                    --This would be assigning a column number 1-40
           FROM [#testdata] td
           CROSS APPLY OPENJSON(CONCAT('["', REPLACE(td.[testdata], ';', '","'), '"]')) --Here we are just convert the string to JSON
       ) AS [spt]
PIVOT (
          --Then pivot here
          MAX([Value])
          FOR [ColumnNumber] IN ( [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8], [Column9], [Column10], [Column11], [Column12], [Column13], [Column14]
                                , [Column15], [Column16], [Column17], [Column18], [Column19], [Column20], [Column21], [Column22], [Column23], [Column24], [Column25], [Column26], [Column27]
                                , [Column28], [Column29], [Column30], [Column31], [Column32], [Column33], [Column34], [Column35], [Column36], [Column37], [Column38], [Column39], [Column40]
                                )
      ) AS [pvt];

Importing a txt file with column delimiters (;) but no CR or LF, how to rewrite file

5 Answers