Create Tab delimited file from Datatable having double quote issue

Question

I am using spreadsheetgear to generate Tab delimited file, it works fine but the issue is if any column in datatable has value with comma("," character) then spreadsheetgear automatically surround the text with double quotes.

Is there way to not putting double quotes if the text has comma character?

Here is the code I am using to generate tab delimited file:

Dim workbook As SpreadsheetGear.IWorkbook
Dim worksheet As SpreadsheetGear.IWorksheet
workbook = SpreadsheetGear.Factory.GetWorkbook()
worksheet = workbook.Worksheets("Sheet1")
Dim cells As SpreadsheetGear.IRange
cells = worksheet.Cells
cells.CopyFromDataTable(dt, Data.SetDataFlags.None)
workbook.SaveAs(strFilePath, SpreadsheetGear.FileFormat.UnicodeText)
worksheet = Nothing
workbook.Close()
workbook = Nothing

Answer 1

You could loop over the text of each cell in cells and take out the quotes. Quotes can be placed inside (or found in) strings using a double-double-quote ("") which can look strange, but gets the job done. For example, you could use cellText.Replace("""", "") to remove quotes.

Answer 2

No options are available to modify this behavior in SpreadsheetGear. SpreadsheetGear generally models its behavior after Excel and you'll notice that Excel does the same thing when it encounters commas when saving to Unicode Text.

You may just need to build your own "SaveToUnicodeText" routine which constructs a file based on your application's particular requirements. Below is some sample code that might help you get started with such a routine. Note this code does not handle every possible "weird" scenario you might encounter with incoming cell data. For instance, I didn't check to see how cases involving linefeeds might need to be treated. This code is merely a starting-out point, so you'll want to consider adding to it as needed:

' Create workbook
Dim workbook As SpreadsheetGear.IWorkbook
Dim worksheet As SpreadsheetGear.IWorksheet
workbook = SpreadsheetGear.Factory.GetWorkbook()
worksheet = workbook.Worksheets("Sheet1")
Dim cells As SpreadsheetGear.IRange
cells = worksheet.Cells

' Copy in your data
cells.CopyFromDataTable(dt, Data.SetDataFlags.None)

' Create a couple StringBuilders to help us out.
Dim sb = New StringBuilder()
Dim sbRow = New StringBuilder()

' IWorkbook.SaveAs(...) effectively saves the UsedRange for the worksheet
' for tab-delimited files, so we'll use the same range.
Dim rangeToSave = worksheet.UsedRange

' Iterate through each row of the used range.
For row = 0 To rangeToSave.RowCount
    sbRow.Length = 0
    ' Now iterate through each cell in the row.
    For col = 0 To rangeToSave.ColumnCount
        ' Build up a tab-delimited string consisting of the formatted text
        ' for each cell in this row.
        sbRow.Append(rangeToSave(row, col).Text).Append(vbTab)
    Next
    ' Trim off any dangling tab characters due to empty cells at the end
    ' of the row and append as a new line to the main StringBuilder.
    sb.AppendLine(sbRow.ToString().TrimEnd(vbTab))
Next

' Save file to disk.
Using stream = New StreamWriter(strFilePath)
    stream.Write(sb.ToString())
End Using