Hello,
I’m new to Libre Office Calc. I have a .csv file with a single column and 237,588 lines https://drive.google.com/open?id=1QDzOK0OgIRzhFVlAneInkR9NfE65RGdU .
How to split that single column into N number of columns after every 400 lines. I also want to count the number of '0’s appearing in each column at the end.
If you are familiar using, writing and executing bash shell scripts, I’d suggest you create a new csv file containing 400 entries each line and use LO’s import functions. Otherwise you may need to code a macro performing the same operation. There is no existing function to achieve exactly what you require.
Here are two routines. 1st one is the import routine. 2nd one is used by 1st one to select the file you want to import. Copy and paste the two routines in a module and run the import-routine. Wait until you see a messagebox on your screen. It will take a while. The no of zeros in a column are put at row 403 of each column.
'Add vba support from office
option vbasupport 1
'Import a single column csv file and split every 400 to next column
Sub import_csv()
Dim MyData, textline, myfileno as integer
Dim myrow as long
dim mycolumn as long
dim thezeros as integer
dim mywb as object
'store the workbook into mywb
mywb = thiscomponent
'store first sheet of workbook into sheet
'workbook needs to be saved first for some reason
sheet = mywb.sheets(0)
'getcellbyposition is actually +1, 0 = 1
myrow = 0
mycolumn = 0
'get the csv you want using function to pick file
MyData = fileopendialog("Select your csv file")
'get a free fileno to use as handle
myfileno = freefile
'open file you selected as input
Open MyData For Input As #myfileno
'do as long not eof
Do While Not EOF(myfileno)
'read a line from the file
Line Input #myfileno, textline
'if row < 400 , 0 = 1 for getcellbyposition
if myrow < 400 then
sheet.getcellbyposition(mycolumn,myrow).value = textline
if sheet.getcellbyposition(mycolumn,myrow).value = 0 then
thezeros = thezeros + 1
endif
myrow = myrow +1
else
'start new column when 400 reached and row = 0
'row is actually 1 but getcellbyposition makes it +1
'then the if will be done until new column needed
'add the no of zeros to finished column
sheet.getcellbyposition(mycolumn,myrow+2).value = thezeros
thezeros = 0
myrow = 0
mycolumn = mycolumn +1
sheet.getcellbyposition(mycolumn,myrow).value = textline
if sheet.getcellbyposition(mycolumn,myrow).value = 0 then
thezeros = thezeros + 1
endif
myrow = myrow +1
end if
Loop
'close file
Close #myfileno
'get the last count of zeros if rows are less than 400
if thezeros <> 0 then
sheet.getcellbyposition(mycolumn,myrow+2).value = thezeros
end if
'display nice messagebox
msgbox "last position was : column " & mycolumn & " row " & myrow
End Sub
'This one is used to select the csv you want
Function FileOpenDialog(title As String) As String
filepicker = createUnoService("com.sun.star.ui.dialogs.FilePicker")
filepicker.Title = title
filepicker.execute()
files = filepicker.getFiles()
FileOpenDialog=files(0)
End Function