Procedure to Export CSV or Tab-Delimited File

Everything else that doesn't fall into one of the other PB categories.
percy_b
User
User
Posts: 72
Joined: Mon Jan 12, 2015 10:25 am

Procedure to Export CSV or Tab-Delimited File

Post by percy_b »

I received so much support from the members of this forum. So, as a way of saying THANK YOU, here is my humble attempt to give back to the PB community.

The procedure below takes four parameters:

1. First parameter (iGadgetHandle) is the ListIcon gadget handle. I haven't tested with other list gadget types.
2. Second parameter (iDataFormat) is the data format (e.g., #PB_Ascii, #PB_UTF8 or #PB_Unicode).
3. Third parameter (bConfirmation) is a boolean flag to display a confirmation message when completed (non-zero value to display message).
4. Fourth parameter (iFileFormat) indicates whether to write a CSV or Tab-delimited file (#Custom_CSV or #Custom_TSV).

Simply call the procedure with the correct parameters and it takes care of building a CSV or TSV file for you.

Code: Select all

;TextExport.pb
;
#Custom_TAB_Substitute  = "    "

Enumeration 1
  #Custom_CSV
  #Custom_TSV
EndEnumeration

Procedure CountListiconColumn(iGadget, iMaxcolumn, sKeyword.s)
  Define iI.i
  
  If IsGadget(iGadget)
    AddGadgetColumn(iGadget, iMaxcolumn, sKeyword, 0)
    
    For iI = 0 To iMaxcolumn
        If GetGadgetItemText(iGadget, -1, iI) = sKeyword
          Break
        EndIf    
    Next iI
      
    RemoveGadgetColumn(iGadget, iI)
    
    ProcedureReturn iI
  EndIf
EndProcedure

Procedure getColumnCount (iGadgetNum.i)
  ProcedureReturn CountListiconColumn(iGadgetNum.i, 32767, "_This_is_The_tEst_column_")
EndProcedure 

;First parameter (iGadgetHandle) is the ListIcon gadget handle
;Second parameter (iDataFormat) is the data format (e.g., #PB_Ascii, #PB_UTF8 or #PB_Unicode)
;Third parameter (bConfirmation) is a boolean flag to display a confirmation message when completed (non-zero value to display message)
;Fourth parameter (iFileFormat) indicates whether to write a CSV or Tab-delimited file (#Custom_CSV or #Custom_TSV)
Procedure Write_CSV_or_TSV_file (iGadgetHandle.i, iDataFormat.i, bConfirmation.b=0, iFileFormat.i=#Custom_CSV)
  Define iFile1.i
  Define iRows.i
  Define iLine.i
  Define iCol.i
  Define iColCount.i
  Define iFileDecision.i = #PB_MessageRequester_No
  Define iFileSize.i
  Define iIndex.i
  Define sCSV_TSV_filename.s
  Define sDefaultFile.s
  Define sWork.s, sWork2.s
  Define sRecord.s
  Define sTitle.s = ""
  Define sFilePattern.s
  Define sFileExt0.s = ".csv"
  Define sFileExt1.s = ".txt"
  Define sFileExt2.s = ".tsv"
  
  ; NOTE: iFileFormat = 1 is CSV (Comma-separated values), iFileFormat = 2 is TSV (Tab-separated values)
  
  If iFileFormat = #Custom_CSV
    sDefaultFile = "MyFile.csv"
    sFilePattern.s = "CSV (*.csv)|*.csv|All files (*.*)|*.*"
  Else ; #Custom_TSV assumed 
    sDefaultFile = "MyFile.txt"   
    sFilePattern.s = "Text (*.txt)|*.txt|TSV (*.tsv)|*.tsv|All files (*.*)|*.*"
  EndIf 
  
  If iFileFormat = #Custom_CSV
    sCSV_TSV_filename = RTrim(SaveFileRequester("Please choose file name for Comma-Separated Values file", sDefaultFile, sFilePattern, 0))
  Else  ;#Custom_TSV assumed
    sCSV_TSV_filename = RTrim(SaveFileRequester("Please choose file name for Tab-Separated Values file", sDefaultFile, sFilePattern, 0))
  EndIf 
  
  iIndex = SelectedFilePattern()
  
  If sCSV_TSV_filename = "" Or iIndex = -1
    ProcedureReturn
  EndIf 

  ;Is selected filename missing extension?
  If iFileFormat = #Custom_CSV
    If iIndex = 0 And LCase(Right(sCSV_TSV_filename, 4)) <> sFileExt0
      sCSV_TSV_filename = sCSV_TSV_filename + sFileExt0
    EndIf 
  Else  ;#Custom_TSV assumed 
    If iIndex = 0 And LCase(Right(sCSV_TSV_filename, 4)) <> sFileExt1
      sCSV_TSV_filename = sCSV_TSV_filename + sFileExt1
    ElseIf iIndex = 1 And LCase(Right(sCSV_TSV_filename, 4)) <> sFileExt2
      sCSV_TSV_filename = sCSV_TSV_filename + sFileExt2
    EndIf
  EndIf 

  iFileSize = FileSize(sCSV_TSV_filename)
  
  If iFileSize = -1
    iFile1 = CreateFile(#PB_Any, sCSV_TSV_filename, iDataFormat)         ; file does not exist
  ElseIf iFileSize = -2
    MessageRequester ("Error", "File '" + sCSV_TSV_filename + "' is a directory!", #PB_MessageRequester_Ok | #PB_MessageRequester_Error)
    ProcedureReturn
  Else  ;File exists
    iFileDecision = MessageRequester("Overwrite File", "File "+ sCSV_TSV_filename + " already exists! Do you want to overwrite it?", #PB_MessageRequester_YesNo | #PB_MessageRequester_Warning)  ; file exists
    
    If iFileDecision = #PB_MessageRequester_Yes
      iFile1 = CreateFile(#PB_Any, sCSV_TSV_filename, iDataFormat)         ; we create a new file...
    Else
      ProcedureReturn  ;Abort creation of CSV file!
    EndIf
  EndIf
  
  iRows = CountGadgetItems(iGadgetHandle)
  iColCount = getColumnCount (iGadgetHandle)
  
  If iFileFormat = #Custom_CSV
    For iCol = 0 To iColCount - 1
      If iCol = 0
        sTitle = #DQUOTE$ + GetGadgetItemText(iGadgetHandle, -1, iCol) + #DQUOTE$ ; get text of column headers
      Else 
        sTitle = sTitle + "," + #DQUOTE$ + GetGadgetItemText(iGadgetHandle, -1, iCol) + #DQUOTE$ ; get text of column headers
      EndIf
    Next iCol
  ElseIf iFileFormat = #Custom_TSV  
    For iCol = 0 To iColCount - 1
      If iCol = 0
        sTitle = GetGadgetItemText(iGadgetHandle, -1, iCol) ; get text of column headers
      Else 
        sWork2 = GetGadgetItemText(iGadgetHandle, -1, iCol)
        sTitle = sTitle + #TAB$ + ReplaceString(sWork2, #TAB$, #Custom_TAB_Substitute)
      EndIf 
    Next iCol
  EndIf 
  
  WriteStringN(iFile1, sTitle) ; write the title string
  
  If iFileFormat = #Custom_CSV
    For iLine = 0 To iRows - 1 ; number of lines
      For iCol = 0 To iColCount - 1 ; number of columns
        sWork = #DQUOTE$ + GetGadgetItemText(iGadgetHandle, iLine, iCol) + #DQUOTE$ ; get that LOCATION and put it in sWork
        
        If iCol = (iColCount - 1)
          sWork = sWork
        Else 
          sWork = sWork + "," ; add a comma (this is a CSV file)
        EndIf 
        
        sRecord = sRecord + sWork ; add sWork to sRecord
      Next iCol
    
      WriteStringN(iFile1, sRecord) ; write the entire sRecord
      sRecord = "" ; kill value in sRecord
    Next iLine
  ElseIf iFileFormat = #Custom_TSV  
    For iLine = 0 To iRows - 1 ; number of lines
      For iCol = 0 To iColCount - 1 ; number of columns
        sWork = GetGadgetItemText(iGadgetHandle, iLine, iCol) ; get that LOCATION and put it in sWork
        sWork = ReplaceString(sWork, #TAB$, #Custom_TAB_Substitute)
        
        If iCol = (iColCount - 1)
          sWork = sWork  ;Not needed, but leave sWork as is for now. 
        Else 
          sWork = sWork + #TAB$ ; add a Tab (this is a CSV file)
        EndIf 
        
        sRecord = sRecord + sWork ; add sWork to sRecord
      Next iCol
    
      WriteStringN(iFile1, sRecord) ; write the entire sRecord
      sRecord = "" ; kill value in sRecord
    Next iLine
  EndIf 
  
  CloseFile(iFile1)
  
  If bConfirmation
    If iFileFormat = #Custom_CSV
      MessageRequester("Success", "Comma-Separated Values file created!", #PB_MessageRequester_Ok | #PB_MessageRequester_Info)
    Else ;#Custom_TSV assumed 
      MessageRequester("Success", "Tab-Separated Values file created!", #PB_MessageRequester_Ok | #PB_MessageRequester_Info)
    EndIf 
  EndIf 
EndProcedure 
Here is an example (below) of how to call this procedure:

Code: Select all

EnableExplicit 

XIncludeFile "TextExport.pb"

Define iEvent.i
Define iDataFormat.i = #PB_UTF8  ;#PB_Ascii, #PB_UTF8 or #PB_Unicode

If OpenWindow(0, 100, 100, 600, 100, "ListIcon Example - Press F2 to Export", #PB_Window_SystemMenu | #PB_Window_ScreenCentered)
  ;This example uses the F2 key to Export the ListIcon content.
  AddKeyboardShortcut(0, #PB_Shortcut_F2, #PB_Shortcut_F2)
  
  ListIconGadget(0, 5, 5, 580, 90, "Name", 100, #PB_ListIcon_FullRowSelect | #PB_ListIcon_AlwaysShowSelection)
  AddGadgetColumn(0, 1, "Address", 250)
  AddGadgetItem(0, -1, "Harry Rannit"+Chr(10)+"12 Parliament Way, Battle Street, By the Bay")
  AddGadgetItem(0, -1, "Ginger Brokeit"+Chr(10)+"130 PureBasic Road, BigTown, CodeCity")
  AddGadgetItem(0, -1, "John Smith"+Chr(10)+"123 Some Street, Another Town, Some Province")
  
  Repeat
     iEvent = WaitWindowEvent()
            
     If iEvent = #PB_Event_Menu
       If EventMenu() = #PB_Shortcut_F2
         ;First parameter is the ListIcon gadget handle
         ;Second parameter is the data format (e.g., #PB_UTF8)
         ;Third parameter is a boolean flag to display a confirmation message when completed (non-zero value to display message)
         ;Fourth parameter indicates whether to write a CSV or Tab-delimited file (#Custom_CSV or #Custom_TSV)
         Write_CSV_or_TSV_file(0, iDataFormat, #True, #Custom_CSV)
       EndIf 
     EndIf 
   Until iEvent = #PB_Event_CloseWindow
EndIf

End