Text to columns

[Probus]

I have a text file where all the data is in one row where each data is delimited by a semicolon. The data consists of sets of seven as per below. However as there is no separate rows the LibreOffice puts all data in a unique column. Is there a way to tell the program that there should be a new row after each set of seven data or is there a way to manipulate the data to make a page break after each seventh semicolon?

sep=;Date;Highprice;Lowprice;Closingprice;Averageprice;Totalvolume;Turnover;2020-08-27;1,805.04;1,791.91;1,793.97;;1;;2020-08-26;1,798.31;1,776.33;1,798.31;;1;;2020-08-25;1,807.20;1,786.14;1,786.27;;1;;2020-08-24;1,792.10;1,765.95;1,788.40;;1;;2020-08-21;1,765.85;1,738.97;1,753.13;;1;;

I.e. I want the imported data to look

2020-08-27 1805.04 1791.91 1793.97 1

2020-08-26 1798.31 1776.33 1798.31 1

2020-08-25 1807.2 1786.14 1786.27 1

2020-08-24 1792.1 1765.95 1788.4 1

2020-08-21 1765.85 1738.97 1753.13 1

2020-08-20 1761.51 1743.3 1751.93 1

But what I get is

sep= Date Highprice Lowprice Closingprice Averageprice Totalvolume Turnover 2020-08-27 1805.04 and so onion the same row

[JohnSUN]

Perhaps you will be advised to Find&Replace with some kind of regular expression (or the REGEX() function). I usually like to quickly write a macro that does most of the work.

Function splitToArray(sSource As String, Optional nCount, Optional sDelimiter) As Variant 
Dim aTemp As Variant, aRes As Variant
Dim i As Long, j As Long, n As Long
	If IsMissing(nCount) Then nCount = 7
	If IsMissing(sDelimiter) Then sDelimiter = ";"
	aTemp = Split(sSource, sDelimiter)
	n = Int((UBound(aTemp)+1) / nCount)
	ReDim aRes(1 To n, 1 To nCount)
	On Error Resume Next
	For i = 1 To n
		For j = 1 To nCount
			aRes(i, j) = aTemp((i-1)*nCount + (j-1))
		Next j
	Next i
	splitToArray = aRes
End Function

As a result of executing the array formula (Ctrl+Shift+Enter), we will receive an array of text values. Let’s cut and paste the result of the function to get only the values. And in order to convert dates and numbers, we still perform a simple Find&Replace

[Probus]

Thanks a lot!

[Probus]

I did find a way to go around the problem by identifying a pattern where I could insert a line break in a word document and then convert it back to a .csv document and import it again.

[JohnSUN]

then convert it back to a .csv and import it again

This is an optional step. You could convert the data into a table directly in the Writer (Table-Convert-Text to Table) and paste it into a spreadsheet as RTF

However, it doesn’t matter - the main thing is that the problem has been solved.

BTW, which pattern do you use? (([^;]+)?;){7}

[Probus]

I use ;;1;; as I found that there is no data in the last columns.

[JohnSUN]

Thus, we have three patterns: 1after an empty column, row of seven semicolons, and what @anon73440385 suggested - semicolons and the date YYYY-MM-DD. Another confirmation that a problem can have many solutions.

[anon73440385]

Hello,

you need to perform a pre-processing of your data file (LibreOffice import filter or Text to Columns function cannot giess where there should be a line, which is meant to be the end of the record.

Method 1

Personally (being on a Linux system) I’d use:
sed 's/[[:digit:]]\{4\}\-[[:digit:]]\{2\}\-[[:digit:]]\{2\}/\n&1/g;s/sep=;//' Data-Raw.txt >Data.csv to create a real CSV file, which could be read by LibreOffice Calc (taking the date YYYY-MM-DD as the begin of a new line/start of a new record)

Method 2
Load the text into Writer and and use the Find & Replace"

• Load the file
• Edit -> Find & Replace
  Find: (\d{4}-\d{2}-\d{2})
  Replace: \n$1
  Other Options 1 [x] Regular expressions
  Optional Remove the sep=; in advance
• Click Replace All
• Close the Find & Replace Dialog
• Save the file as text file (i.e. Filter: Text (.txt))
• Rename the file to .csv
• Open a spreadsheet and File -> Open and select the csv file you just created

Note You may use any text editor supporting regular expressions to achieve the same result.

Method 3

Write user code (macro).

Hope that helps.