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

[lonk]

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 ‘|’ ?

[robleyd]

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.

[lonk]

@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.

[erAck]

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 Filter Options - Apache OpenOffice Wiki

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).

[lonk]

@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]

@erAck

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

It’s ok.

[mauricio]

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

[mauricio]

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]

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]

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)