CSV Converter

Simon_Berridge · June 2, 2015, 4:41pm

I have seen a fair amount of discussion about CSV files and the reading/writing of same.

I use CSV a lot in my work and have written a number of different classes over the years to handle these files.

I thought it might be useful to create a global app that will take a CSV file and recreate it in other formats. The outputs available are:

CSV Comma
CSV Tab
CSV Semi-colon
SQL Insert Table script file
Excel format

This app is issued free to the Xojo community as I have received a huge amount of help from here and would like to give something back. As always, please post your issues here.

You can download from here:

OS X: https://dl.dropboxusercontent.com/u/10747116/RealStudioClasses/CSVConverter.zip
Windows: https://dl.dropboxusercontent.com/u/10747116/RealStudioClasses/CSVConverterWIN.zip

Simon.

Richard_Summers · June 2, 2015, 4:44pm

Thank you for sharing that Simon - I’m sure people will appreciate it.

DaveS · June 2, 2015, 4:52pm

I assume it properly handles embedded commas (or other delimiters), processes quotes correctly etc… Just asking as these are things that most converters either neglect entirely, or implement incorrectly.

Also… HTML output might be a consideration

Norman_P · June 2, 2015, 4:57pm

embedded line feeds

Simon_Berridge · June 2, 2015, 5:03pm

Yes, it should.

I haven’t checked this, could be interesting.

I will add this to my list.

Peter_Job · June 2, 2015, 5:09pm

What on earth would a linefeed be doing in a .csv file if not a line ending? Given the line endings on Win Mac and Linux that sounds insane.

Bob_Coleman · June 2, 2015, 5:20pm

It is a standard thing with CSV. We get files from various ecommerce systems and they will have endofline characters in the note fields all the time. As long as the field is quoted properly it is no big deal.

Axel_Schneider · June 2, 2015, 5:23pm

Using this method, I save my listbox as HTML.

Function ExportToHTML(List As Listbox, file as FolderItem) As Boolean
  if file = nil then
    Return False
  else
    Dim Lines() As String
    Dim cell() As String
    Dim row() As String
    Dim i, j As Integer
    
    Lines.Append "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.01//EN"""
    Lines.Append """http://www.w3.org/TR/html4/strict.dtd"">"
    Lines.Append "<html lang=""de"">"
    Lines.Append "<head>"
    Lines.Append "<link rel=""stylesheet"" type=""text/css"" href=""axel2.css""/>"
    Lines.Append "<meta http-equiv=""content-type"" content=""text/html; charset=utf-8"">"
    Lines.Append "<title>Homepage</title>"
    Lines.Append "<meta name=""generator"" content=""HTMLEditor"">"
    Lines.Append "</head>"
    Lines.Append "<body>"
    Lines.Append EndOfLine
    if list.TextSize = 0 then
      Lines.Append "<table border=""1em"" bordercolor=""#CDCDCD"" style=""font-size:12px"">"
      
    else
      Lines.Append "<table border=""1em"" bordercolor=""#CDCDCD"" style=""font-size:" _
      + str(List.TextSize) + "px"">"
    end if
    
    Lines.Append "<tr>"
    
    For i = 0 to List.ColumnCount-1
      Redim cell(-1)
      if list.ColumnAlignment(i) = Listbox.AlignCenter then
        cell.Append "<td align=""center""><font face=""" + list.TextFont + """>"
      elseif list.ColumnAlignment(i) = Listbox.AlignRight then
        cell.Append "<td align=""right""><font face=""" + list.TextFont + """>"
      else
        cell.Append "<td><font face=""" + list.TextFont + """>"
      end if
      cell.Append list.Heading(i) + "</font>"
      cell.Append "</th>"
      Lines.Append Join(cell, "")
    Next
    Lines.Append "</tr>"
    
    For i = 0 to List.ListCount-1
      Redim row(-1)
      row.Append "<tr>"
      Lines.Append Join(row, "")
      
      For j = 0 to List.ColumnCount-1
        Redim cell(-1)
        if list.ColumnAlignment(j) = Listbox.AlignCenter then
          cell.Append "<td align=""center""><font face=""" + list.TextFont + """ " _
          + "color=""" + main.lb.CellTag(i,j).StringValue.Replace("&h00", "#") + """" + ">"
        elseif list.ColumnAlignment(j) = Listbox.AlignRight then
          cell.Append "<td align=""right""><font face=""" + list.TextFont + """ " _
          + "color=""" + main.lb.CellTag(i,j).StringValue.Replace("&h00", "#") + """" + ">"
        else
          cell.Append "<td><font face=""" + list.TextFont + """ " _
          + "color=""" + main.lb.CellTag(i,j).StringValue.Replace("&h00", "#") + """" + ">"
        end if
        if List.CellType(i, j) = Listbox.TypeDefault Then
          cell.Append List.Cell(i, j) + "</font>"
        elseif List.CellType(i, j) = Listbox.TypeCheckbox Then
          if List.CellCheck(i, j) = True then
            cell.Append "True" + "</font>"
          else
            cell.Append "False" + "</font>"
          end if
        end if
        cell.Append "</td>"
        Lines.Append Join(cell, "")
      Next
      Lines.Append "</tr>"
    Next
    Lines.Append "</table>" + EndOfLine
    Lines.Append "</body>" + EndOfLine
    Lines.Append "</html>" + EndOfLine
    dim b as BinaryStream = BinaryStream.Create(file, true)
    b.Write Join(Lines, EndOfLine)
    b.Close
  end if
  return true
End Function

Save Button Action

  Dim htmlType As New FileType
  htmlType.Name = "HTML File (*.html)"
  htmlType.MacType = "HTML "
  htmlType.Extensions = "html"
  
  dim f as FolderItem
  dim dlg as new SaveAsDialog
  dlg.Filter = htmlType
  dlg.SuggestedFileName = "Table.html"
  f = dlg.ShowModalWithin(main)
  if f <> nil Then
    if ExportToHTML (main.lb, f) = true then
      f.launch
    else
      MSGBox "Error saving HTML"
    end if
  end if

Axel_Schneider · June 2, 2015, 5:28pm

@Simon Berridge

a small problem
You use the first Row as Heading if checked, but the Heading loses the encoding

Norman_P · June 2, 2015, 5:41pm

inside quotes
" "
perfectly achievable in Excel csv’s - the gold standard for insane CSV’s

its why I wrote my parser to handle whatever excel chucks out

Michel_Bujardet · June 2, 2015, 5:57pm

Are you talking about just LF chr(10) or EndOfLine ?

Insane anyway, but a little less if it is possible to distinguish end of line and linefeed. Then a global replace of chr(10) by or some other token seems possible.

Norman_P · June 2, 2015, 6:06pm

[quote=191463:@Michel Bujardet]Are you talking about just LF chr(10) or EndOfLine ?

Insane anyway, but a little less if it is possible to distinguish end of line and linefeed. Then a global replace of chr(10) by or some other token seems possible.[/quote]
You cant
That is the beauty
you can have
" field value with embedded linefeed ", " field value with embedded linefeed " linefeed
" field value with embedded linefeed ", " field value with embedded linefeed " linefeed

and the linefeeds are indistinguishable from each other

you really need a parser that implements a finite state machine to handle this

Michel_Bujardet · June 2, 2015, 6:08pm

Yuk

Simon_Berridge · June 2, 2015, 6:33pm

Great feedback so far, thank you all.

Now has HTML output.

You can download from here:

OS X: https://dl.dropboxusercontent.com/u/10747116/RealStudioClasses/CSVConverter.zip
Windows: https://dl.dropboxusercontent.com/u/10747116/RealStudioClasses/CSVConverterWIN.zip

Still working on the linefeed issue.

Simon.

Simon_Berridge · June 2, 2015, 6:38pm

[quote=191454:@Axel Schneider]Using this method, I save my listbox as HTML.

Function ExportToHTML(List As Listbox, file as FolderItem) As Boolean
  if file = nil then
    Return False
  else
    Dim Lines() As String
    Dim cell() As String
    Dim row() As String
    Dim i, j As Integer
    
    Lines.Append "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.01//EN"""
    Lines.Append """http://www.w3.org/TR/html4/strict.dtd"">"
    Lines.Append "<html lang=""de"">"
    Lines.Append "<head>"
    Lines.Append "<link rel=""stylesheet"" type=""text/css"" href=""axel2.css""/>"
    Lines.Append "<meta http-equiv=""content-type"" content=""text/html; charset=utf-8"">"
    Lines.Append "<title>Homepage</title>"
    Lines.Append "<meta name=""generator"" content=""HTMLEditor"">"
    Lines.Append "</head>"
    Lines.Append "<body>"
    Lines.Append EndOfLine
    if list.TextSize = 0 then
      Lines.Append "<table border=""1em"" bordercolor=""#CDCDCD"" style=""font-size:12px"">"
      
    else
      Lines.Append "<table border=""1em"" bordercolor=""#CDCDCD"" style=""font-size:" _
      + str(List.TextSize) + "px"">"
    end if
    
    Lines.Append "<tr>"
    
    For i = 0 to List.ColumnCount-1
      Redim cell(-1)
      if list.ColumnAlignment(i) = Listbox.AlignCenter then
        cell.Append "<td align=""center""><font face=""" + list.TextFont + """>"
      elseif list.ColumnAlignment(i) = Listbox.AlignRight then
        cell.Append "<td align=""right""><font face=""" + list.TextFont + """>"
      else
        cell.Append "<td><font face=""" + list.TextFont + """>"
      end if
      cell.Append list.Heading(i) + "</font>"
      cell.Append "</th>"
      Lines.Append Join(cell, "")
    Next
    Lines.Append "</tr>"
    
    For i = 0 to List.ListCount-1
      Redim row(-1)
      row.Append "<tr>"
      Lines.Append Join(row, "")
      
      For j = 0 to List.ColumnCount-1
        Redim cell(-1)
        if list.ColumnAlignment(j) = Listbox.AlignCenter then
          cell.Append "<td align=""center""><font face=""" + list.TextFont + """ " _
          + "color=""" + main.lb.CellTag(i,j).StringValue.Replace("&h00", "#") + """" + ">"
        elseif list.ColumnAlignment(j) = Listbox.AlignRight then
          cell.Append "<td align=""right""><font face=""" + list.TextFont + """ " _
          + "color=""" + main.lb.CellTag(i,j).StringValue.Replace("&h00", "#") + """" + ">"
        else
          cell.Append "<td><font face=""" + list.TextFont + """ " _
          + "color=""" + main.lb.CellTag(i,j).StringValue.Replace("&h00", "#") + """" + ">"
        end if
        if List.CellType(i, j) = Listbox.TypeDefault Then
          cell.Append List.Cell(i, j) + "</font>"
        elseif List.CellType(i, j) = Listbox.TypeCheckbox Then
          if List.CellCheck(i, j) = True then
            cell.Append "True" + "</font>"
          else
            cell.Append "False" + "</font>"
          end if
        end if
        cell.Append "</td>"
        Lines.Append Join(cell, "")
      Next
      Lines.Append "</tr>"
    Next
    Lines.Append "</table>" + EndOfLine
    Lines.Append "</body>" + EndOfLine
    Lines.Append "</html>" + EndOfLine
    dim b as BinaryStream = BinaryStream.Create(file, true)
    b.Write Join(Lines, EndOfLine)
    b.Close
  end if
  return true
End Function

Save Button Action

[code]
Dim htmlType As New FileType
htmlType.Name = “HTML File (*.html)”
htmlType.MacType = "HTML "
htmlType.Extensions = “html”

dim f as FolderItem
dim dlg as new SaveAsDialog
dlg.Filter = htmlType
dlg.SuggestedFileName = “Table.html”
f = dlg.ShowModalWithin(main)
if f <> nil Then
if ExportToHTML (main.lb, f) = true then
f.launch
else
MSGBox “Error saving HTML”
end if
end if
[/code][/quote]
I really hope that this is existing code rather than code you created for this as I was working on a solution too. It already exists in the app now but thank you for the code. I have copied it anyway and will study it in depth later on.

At the moment I am considering finishing for the day (19:35 here) and starting again tomorrow. I want to crack the encoding issue on the header and the linefeed issue in the csv file.

Thanks, all, again. As I thought the CSV conundrum is controversial and generates a lot of interest/discussion.

To be continued…

Simon.

Norman_P · June 2, 2015, 6:44pm

Thanks be to MS

Peter_Job · June 2, 2015, 6:48pm

[quote=191463:@Michel Bujardet]Are you talking about just LF chr(10) or EndOfLine ?

Insane anyway, but a little less if it is possible to distinguish end of line and linefeed. Then a global replace of chr(10) by or some other token seems possible.[/quote]

End-of-line IS a linefeed on some systems ( must be Unix ).

I propose we have a .npf - a Norman Palardy file - chuck anything you want in it, and Norman will sort it out.

Michel_Bujardet · June 2, 2015, 7:01pm

Looks like some Regex wizardry from Kem Tekinay is in order

Bob_Coleman · June 2, 2015, 10:47pm

Most shopping carts, Oracle Apps and others have done this as well.

Either way it is specified in the RFC.
https://tools.ietf.org/html/rfc4180

Norman_P · June 2, 2015, 11:01pm

[quote=191514:@Bob Coleman]Most shopping carts, Oracle Apps and others have done this as well.

Either way it is specified in the RFC.
https://tools.ietf.org/html/rfc4180[/quote]

Heh yeah that one which starts with
This memo provides information for the Internet community. It does
not specify an Internet standard of any kind. Distribution of this
memo is unlimited.

CSV is as “standardly unstandardized” as anything I’ve ever encountered