How can I append a CSV export with other data

[anotherjoe]

I am making a tool to export spreadsheet items to a csv file that can be imported into draw.io it requires some config data above the CSV data. My thought was to have one sheet that contains the CSV data and one sheet that contains the config data. Is there a way via scripting to accomplish this?

Example of desired output(lines 1-3 from config sheet, lines 4&5 from CSV sheet

# label: %step%
# style: shape=%shape%;fillColor=%fill%;strokeColor=%stroke%;
## CSV starts under this line
id,step,fill,stroke,shape,refs
1,Hello World,#dae8fc,#6c8ebf,rectangle,

RV_System_Diagram_Sample.ods

[JohnSUN]

It is not difficult, most likely the macro will take no more than 10-15 lines. But before showing the code, I would like to see a sample of the data - where are the configuration lines in your sheet, where does the data for the CSV start from?

[JohnSUN]

If your data is presented in this form

then such a code should solve your problem

Sub ExportToDrawIO()
Dim oCursor As Variant, oData As Variant, oRes As Variant, i As Long
	oCursor = ThisComponent.getCurrentController().getActiveSheet().createCursor()
	oCursor.gotoEndOfUsedArea(True)
	oData = oCursor.getDataArray()
	ReDim oRes(LBound(oData) To UBound(oData))
	For i = LBound(oData) To UBound(oData)
		If Left(oData(i)(0),1) = "#" Then
			oRes(i) = Trim(oData(i)(0))
		Else
			oRes(i) = Join(oData(i), ",")
		EndIf 
	Next i
	GlobalScope.BasicLibraries.LoadLibrary("Tools")
	SaveDataToFile(ConvertToURL("C:\Output\DrawIO.csv"), oRes)
End Sub

Update. In case the data is on different sheets of the spreadsheet, the code can be like this

Sub ExportToDrawIOFom2Sheets()
Dim oSheets As Variant
Dim oSheet As Variant
Dim oCursor As Variant, oData As Variant, oRes As Variant
Dim i As Long, nRow As Long

	oSheets = ThisComponent.getSheets()
	oSheet = oSheets.getByName("diagram_config")
	oCursor = oSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	oData = oCursor.getDataArray()
	ReDim oRes(LBound(oData) To UBound(oData))
	For i = LBound(oData) To UBound(oData)
		oRes(i) = Trim(oData(i)(0))
	Next i

	oSheet = oSheets.getByName("Airstream_Appliances")
	oCursor = oSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	oData = oCursor.getDataArray()
	nRow = UBound(oRes) + 1
	i = nRow + UBound(oData)
	ReDim Preserve oRes(i)
	For i = LBound(oData) To UBound(oData)
		oRes(nRow + i) = Join(oData(i), ",")
	Next i
	GlobalScope.BasicLibraries.LoadLibrary("Tools")
	SaveDataToFile(ConvertToURL("C:\Output\DrawIO.csv"), oRes)
End Sub

[erAck]

If the data is already in that form then it doesn’t even need a macro to export it… or what did I miss?

Also note that in case a cell contains the , comma field separator the content needs to be enclosed in double quotes and embedded double quotes need to be escaped by doubling them.

[JohnSUN]

@erAck I can’t say for sure - I did not carefully read the description of the configuration lines for that resource (I am not going to use it). Perhaps extra commas in the “header” lines of the file will break something in its operation

[anotherjoe]

Hi, thanks for the help. I Attached a sheet with sample data. It contains three sheets

1. devices - this is the data that should be at the bottom of the CSV file
2. color_mapping - no bearing on the final output, it is used to set color codes
3. diagram_config - the config data that should appear at the top to the file, the number of rows could change depending on desired config

[JohnSUN]

@anotherjoe I looked at a sample of your spreadsheet. Does it seem to me that lines 14:19 on the diagram_config sheet are superfluous? This will not change the meaning of the script. The code will become longer, but its essence will remain the same - collect all the strings for the output file in one array and execute the SaveDataToFile procedure from the Tools library, specifying the desired location and file name.

[anotherjoe]

That is a good point, this was my attempt on how to accomplish the desired output but your way sounds better. Do you have an example?

[JohnSUN]

Example? Of course have. I updated my answer 8 minutes before you asked

[anotherjoe]

Works perfect thanks again, also sorry my example was unclear. “Does it seem to me that lines 14:19 on the diagram_config sheet are superfluous” that data was there as an example of the desired output.