Export Parts List to CSV starting on 4th line

[sultan_mustun]

I want to export my parts list to a CSV file from my drawing, but the ERP system we use starts reading for values as from the 4th line in the CSV.
Exporting to an Excel file gives the possibility to choose the starting cell:

oOptions.Add("StartingCell", "A4")

But we lack this option when exporting to CSV. I tried adding commas before the Title, thinking it would add enough columns to skip to the next line, but it didn't work:

Dim oTitleModified = (",,,,,," & oTitleOriginal)

Is there a way around this?

The code I'm using was found on the forum:
https://forums.autodesk.com/t5/inventor-programming-ilogic/export-parts-list-as-csv-to-specific-fold... 
Credit to @WCrihfield 

If ThisApplication.ActiveDocumentType <> DocumentTypeEnum.kDrawingDocumentObject Then
	MsgBox("This rule only works for Drawing Documents.",vbOKOnly, "WRONG DOCUMENT TYPE")
	Exit Sub
End If
Dim oDDoc As DrawingDocument = ThisDrawing.Document
Dim oFileName As String = IO.Path.GetFileNameWithoutExtension(oDDoc.FullFileName)
Dim oPath As String = "C:\temp\"
'Dim oNewFullName = oPath & oFileName & ".xlsx"									'Excel
Dim oNewFullName = oPath & oFileName & ".csv"									'CSV
Dim oPartslist As PartsList = oDDoc.Sheets.Item("Sheet:1").PartsLists.Item(1)
If Not IO.Directory.Exists(oPath) Then
	IO.Directory.CreateDirectory(oPath)
End If
If IO.File.Exists(oNewFullName) Then
	oAns = MsgBox("That file already exists. Do you want to overwrite it?", vbYesNo + vbQuestion, " ")
	If oAns = vbNo Then Exit Sub
End If
'oPartslist.Export(oNewFullName, PartsListFileFormatEnum.kTextFileCommaDelimited)				'Without Options

'Dim oTitleOriginal As String = oPartslist.Title()								'Modify Title to add commas to skip to next line
'Dim oTitleModified = (",,,,,," & oTitleOriginal)
'oPartslist.Title = oTitleModified

Dim oOptions As NameValueMap = ThisApplication.TransientObjects.CreateNameValueMap
'oOptions.Add("TableName", "Parts List for " & oFileName)							'Excel only
oOptions.Add("ExportedColumns", "ITEM;QTY;DESCRIPTION;LENGTH;WIDTH;MATERIAL;PART NO")
oOptions.Add("IncludeTitle",True)
'oOptions.Add("StartingCell", "A4")										'Excel only
'oOptions.Add("Template", "C:\Temp\CSV Parts List Template.xlsx")						'Excel only
'oOptions.Add("AutoFitColumnWidth",True)									'Excel only
'oPartslist.Export(oNewFullName, PartsListFileFormatEnum.kMicrosoftExcel, oOptions)				'Excel
oPartslist.Export(oNewFullName, PartsListFileFormatEnum.kTextFileCommaDelimited, oOptions)

[Michael.Navara · Accepted solution]

In my opinion if you want to export PartsList to CSV it is the best option to manage the export by yourself.

You have much better control over the export process. Here is small sample how to do it

Sub Main
	'Sampl usage
	Dim drawing As DrawingDocument = ThisDoc.Document
	Dim partsList As PartsList = drawing.ActiveSheet.PartsLists(1)

	ExportPartsList(partsList, "C:\Temp\Bom.csv", New String() {"ITEM", "QTY", "DESCRIPTION", "LENGTH", "WIDTH", "MATERIAL", "PART NO"})
End Sub

Sub ExportPartsList(partsList As PartsList, fileName As String, exportedColumns As String())

	'Other possible options
	Dim delimiter As Char = ";"
	Dim encoding As System.Text.Encoding = System.Text.Encoding.UTF8
	Dim emptyColumnsCount As Integer = 3

	'Get exported column indices
	Dim columnIndices As New List(Of Integer)
	For Each exportedColumn As String In exportedColumns
		For i = 1 To partsList.PartsListColumns.Count
			Dim column As PartsListColumn = partsList.PartsListColumns(i)
			If column.Title.Equals(exportedColumn, StringComparison.CurrentCultureIgnoreCase) Then
				columnIndices.Add(i)
			End If
		Next
	Next

	'Get partsList data
	Dim bom As New List(Of String())
	For Each row As PartsListRow In partsList.PartsListRows
		Dim bomRow As New List(Of String)
		For Each columnIndex As Integer In columnIndices
			bomRow.Add(row(columnIndex).Value)
		Next
		bom.Add(bomRow.ToArray())
	Next

	'Converts PartsList data to string
	Dim emptyColumns As New String(delimiter, emptyColumnsCount)
	Dim fileContent As New System.Text.StringBuilder
	For Each bomRow As String() In bom
		Dim rowString = String.Format("{0}{1}", emptyColumns, String.Join(delimiter, BOMRow))
		fileContent.AppendLine(rowString)
	Next

	'Save PartsList data to file
	System.IO.File.WriteAllText(fileName, fileContent.ToString(), encoding)

End Sub

[WCrihfield]

Hi @sultan_mustun.

When you say that your ERP system starts reading from the fourth line...do you mean the fourth row or the fourth column?

I very rarely use .csv files myself, so I do not have a lot of experience exporting data to that file type, or using those types of files from other software later.  My post from 3 1/2 years ago was just primarily just me fixing the original poster's new path & file name, showing how to create and use a NameValueMap to fill in options.

I agree with @Michael.Navara, that since you need your data to be formatted a special way that the built-in PartsList.Export method does not support, handling that export process with a customized code routine is the better route to take.  And the example he showed above looks pretty good to me.

However, if you meant that the ERP system starts reading from the fourth 'row', instead of from the fourth 'column', then a couple tweaks may be needed in his example above before it will be exactly as you need.

[sultan_mustun]

Thank you @Michael.Navara, your answer put me on the right path for the solution.

@WCrihfield, it was actually the 4th row, I should have been more specific.

Here is the solution I came up with for the Sub to Export to CSV:

Sub ExportPartsList(partsList As PartsList, fileName As String, exportedColumns As String())

	'Other possible options
	Dim delimiter As Char = ","
	Dim encoding As System.Text.Encoding = System.Text.Encoding.UTF8
	Dim emptyColumnsCount As Integer = 0															'ADDS EMPTY COLUMNS AT START

	'Get exported column indices
	Dim columnIndices As New List(Of Integer)
	For Each exportedColumn As String In exportedColumns
		For i = 1 To partsList.PartsListColumns.Count
			Dim column As PartsListColumn = partsList.PartsListColumns(i)
			If column.Title.Equals(exportedColumn, StringComparison.CurrentCultureIgnoreCase) Then
				columnIndices.Add(i)
			End If
		Next
	Next

	'Get partsList data
	Dim bom As New List(Of String())
	For Each row As PartsListRow In partsList.PartsListRows
		Dim bomRow As New List(Of String)
		For Each columnIndex As Integer In columnIndices
			bomRow.Add(row(columnIndex).Value)
		Next
		bom.Add(bomRow.ToArray())
	Next

	'Converts PartsList data to string
	Dim emptyColumns As New String(delimiter, emptyColumnsCount)								'ADDS THE AMOUNT OF EMPTY COLUMNS SET ABOVE
	Dim fileContent As New System.Text.StringBuilder
	
	Dim StartRow = 4																			'ADDS NEW LINE UNTIL StartRow
	Dim j = 1
	While j < (StartRow - 1)
		fileContent.AppendLine()
		j = j + 1
	End While
	
	fileContent.Append("ITEM" & delimiter & "QTY" & delimiter & "DESCRIPTION" & delimiter & "LENGTH" & delimiter & "WIDTH" & delimiter & "MATERIAL" & delimiter & "PART NO")		'ADDS THE HEADERS ONE LINE BEFORE StartRow
	fileContent.AppendLine()
	
	For Each bomRow As String() In bom
		Dim rowString = String.Format("{0}{1}", emptyColumns, String.Join(delimiter, BOMRow))
		fileContent.AppendLine(rowString)
	Next

	'Save PartsList data to file
	System.IO.File.WriteAllText(fileName, fileContent.ToString(), encoding)

End Sub

I also added the Headers on Row 3. If there is a cleaner way of adding these, rather than manually, like I did. I would be happy to learn how.