Ask Your Question
0

Split a Single Column to N number of columns and count number of '0's

asked 2019-04-04 11:42:52 +0100

Vinayk gravatar image

updated 2019-04-04 11:46:12 +0100

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=1QDz... .

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.

edit retag flag offensive close merge delete

Comments

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.

Opaque gravatar imageOpaque ( 2019-04-05 11:18:20 +0100 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2019-04-06 01:55:23 +0100

RedTemplar gravatar image

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
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-04-04 11:42:52 +0100

Seen: 43 times

Last updated: Apr 06