Ask Your Question
0

BASIC + Calc : Import a csv file to an existing Sheet

asked 2019-10-09 09:22:43 +0200

lonk gravatar image

updated 2019-10-09 09:23:15 +0200

This is the csv file :

Foreign Exchange Rates as of   8 October 2019
Weighted-average Interbank Exchange Rate = 30.457 Baht/US Dollar  
1. Average Counter Rates Quoted by Commercial Banks   (Baht / 1 Unit of Foreign Currency)  
Country|Currency|Average Buying Rates Sight Bill|Average Buying Rates Transfer|Average Selling Rates
UNITED STATES|USD|30.2012|30.2926|30.6505
UNITED KINGDOM|GBP|36.8800|37.0288|37.8966
EURO ZONE|EUR|32.9382|33.0509|33.7740
JAPAN (100 Yen)|JPY|27.8869|27.9956|28.7627
HONG KONG|HKD|3.8278|3.8453|3.9254
MALAYSIA|MYR|7.1135|7.1634|7.3764
SINGAPORE|SGD|21.7234|21.8029|22.3454
BRUNEI DARUSSALAM|BND|21.6083|21.7347|22.4104
PHILIPPINES|PHP|0.5733|0.5769|0.6004
INDONESIA (1000 Rupiah)|IDR|1.9917|2.0556|2.2800
INDIA|INR|0.3809|0.3994|0.4619
SWITZERLAND|CHF|30.1647|30.2677|30.9296
AUSTRALIA|AUD|20.0623|20.1339|20.8951
NEW ZEALAND|NZD|18.8161|18.8956|19.4940
CANADA|CAD|22.5353|22.6245|23.1661
SWEDEN|SEK|3.0193|3.0361|3.1114
DENMARK|DKK|4.4053|4.4255|4.5257
NORWAY|NOK|3.2791|3.2940|3.3790
CHINA|CNY|4.1799|4.2125|4.3309
 Released on 8 October 2019

Code:

Sub LoadCSVFile
    Dim CSVFileName As String : CSVFileName = "file:///home/User01/Downloads/ER_CSV1_2019-10-08.csv"
    If Len(CSVFileName) > 0 Then
        Dim FPrp(1) As new com.sun.star.beans.PropertyValue
        FPrp(0).Name = "FilterName"
        FPrp(0).Value = "Text - txt - csv (StarCalc)"
        FPrp(1).Name = "FilterOptions"
        FPrp(1).Value = "44,34,76,1,,0,false,true,true,false"
        Dim oDoc As Object : oDoc = StarDesktop.loadComponentFromURL(CSVFileName, "_blank", 0, fileProps())        
    End If
End Sub
  1. The code imports a csv file to a brand new .ods file. Can you please tell me how to edit the code to import it to an existing sheet with a specific sheet name ?
  2. Can you please tell me how to import beginning from the 5th line of the csv file with delimiter '|' ?
edit retag flag offensive close merge delete

Comments

Is there a reason you need a macro to do this? Because you can achieve this using Sheet | Insert from File; then you can choose the file, set the separator and choose how many initial lines to ignore, among other possibilities.

robleyd gravatar imagerobleyd ( 2019-10-09 11:40:12 +0200 )edit

@robleyd

Thank you so much for your concern.

That code is only a part of the project.

I couldn't find any sample codes that match my need.

lonk gravatar imagelonk ( 2019-10-09 12:38:55 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2019-10-10 06:27:10 +0200

mauricio gravatar image

updated 2019-10-10 06:42:51 +0200

Test with

Sub import_csv()
Dim opt(1) As New com.sun.star.beans.PropertyValue

    path = ConvertToURL("/home/mau/test.csv")

    opt(0).Name = "FilterName"
    opt(0).Value = "Text - txt - csv (StarCalc)"
    opt(1).Name = "FilterOptions"
    opt(1).Value = "124,34,76,1,,0,false,false,true,false,false"

    doc = ThisComponent
    cell = doc.Sheets.getByName("Data").getCellRangeByName("A1")
    target = next_cell(cell)

    csv = StarDesktop.loadComponentFromURL(path, "_blank", 0, opt())
    sheet = csv.Sheets.getByIndex(0)
    cell = sheet.getCellRangeByName("A1")
    cursor = sheet.createCursorByRange(cell)
    cursor.collapseToCurrentRegion()
    ra = cursor.RangeAddress

    data = sheet.getCellRangeByPosition(ra.StartColumn, ra.StartRow+4, ra.EndColumn, ra.EndRow).DataArray
    copy_to(target, data)
    csv.close(True)

End Sub


Function copy_to(cell, data)
    ra = cell.RangeAddress
    s = cell.SpreadSheet
    cols = ra.EndColumn + UBound(data(0))
    rows = ra.EndRow + Ubound(data)
    range = s.getCellRangeByPosition(ra.StartColumn, ra.StartRow, cols, rows)
    range.DataArray = data
End Function


Function next_cell(cell)
    cursor = cell.SpreadSheet.createCursorByRange(cell)
    cursor.gotoEnd()
    row = cursor.RangeAddress.EndRow
    next_cell = cell.SpreadSheet.getCellByPosition(0, row + 1)
End Function
edit flag offensive delete link more

Comments

The same code, but using my extension EasyMacro.

Sub import_csv_easymacro()
    app = createUnoSErvice("net.elmau.zaz.EasyMacro")
    path = "/home/mau/test.csv"
    args = Array( _
        Array("FilterName", "Text - txt - csv (StarCalc)"), _
        Array("FilterOptions", "124,34,76,1,,0,false,false,true,false,false") _
    )    
    doc = ThisComponent
    sheet = app.get_sheet(doc, "Data")
    cell = app.get_range(sheet, "A1")
    target = app.get_next_cell(cell)

    csv = app.open_doc(path, args)
    cell = app.get_range(csv, "A1")
    range = app.get_current_region(cell)
    data = range.DataArray
    data = app.slice(data, "[5:]")

    app.set_data(target, data, False)
    csv.close(True)
End Sub
mauricio gravatar imagemauricio ( 2019-10-10 06:43:49 +0200 )edit

The same code, but with Python and easymacro.py

path = '/home/mau/test.csv'
args = {
    'FilterName': 'Text - txt - csv (StarCalc)',
    'FilterOptions': '124,34,76,1,,0,false,false,true,false,false',
}
doc = app.get_document()
csv = app.open_doc(path, **args)
cell = doc.sheets['Data']['A1'].next_cell
data = csv[0]['A1'].current_region.data[5:]
cell.copy_from(data)
csv.close()
mauricio gravatar imagemauricio ( 2019-10-10 07:06:16 +0200 )edit

I have added, in develop version, support for import CSV directly in cell.

doc = app.get_document()
path = '/home/mau/test2.csv'
args = {'delimiter': '|'}
cell = doc['Sheet1']['A1']
cell.import_csv(path, **args)
mauricio gravatar imagemauricio ( 2019-10-11 05:09:39 +0200 )edit
0

answered 2019-10-09 16:18:49 +0200

erAck gravatar image

Can you please tell me how to import beginning from the 5th line of the csv file with delimiter '|' ?

FPrp(1).Value = "124,34,76,5,,0,false,true,true,false"

should do, where 124 is the ASCII value of |and 5 the first line to import. See https://wiki.openoffice.org/wiki/Docu...

Note that if your locale's decimal separator is not . dot then you want to specify an import locale (language identifier) 1033 for en-US, instead of 0 (system).

edit flag offensive delete link more

Comments

@erAck

Thank you so much for your assistance.

Regarding my question #1, the code imported to a brand new .ods file.

I need the code to import to an existing active Sheet.

Or should I read .csv file line by line, split it and place into each cell ?

lonk gravatar imagelonk ( 2019-10-10 04:43:50 +0200 )edit

@erAck

I have used reading .csv file line by line, split it and place into each cell.

It's ok.

lonk gravatar imagelonk ( 2019-10-10 06:10:34 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-10-09 09:22:43 +0200

Seen: 43 times

Last updated: Oct 10