1 :Namespace netCSV
   2 
   3     ⎕USING←0⍴⊂''
   4 
   5     (⎕IO ⎕ML ⎕WX)←1 3 3
   6 
   7       TsToDateString←{
   8         ⍝ ⍵ = ⎕TS
   9         ⍝ Result = Date formatted to a String with no ambiguity.
  10      
  11           ⎕USING←'System,mscorlib.dll' 'System.Xml,System.Xml.dll'
  12           XmlConvert.ToString(((⎕NEW DateTime ⍵))(,'dd-MMM-yyyy'))
  13       }
  14 
  15       TsToTimeString←{
  16         ⍝ ⍵ = ⎕TS
  17         ⍝ Result = Date formatted to a String with no ambiguity.
  18      
  19           ⎕USING←'System,mscorlib.dll' 'System.Xml,System.Xml.dll'
  20           XmlConvert.ToString(((⎕NEW DateTime ⍵))(,'h:mm:ss tt'))
  21       }
  22 
  23       TsToDateTimeString←{
  24         ⍝ ⍵ = ⎕TS
  25         ⍝ Result = Date formatted to a String with no ambiguity.
  26      
  27           ⎕USING←'System,mscorlib.dll' 'System.Xml,System.Xml.dll'
  28           XmlConvert.ToString(((⎕NEW DateTime ⍵))(,'dd-MMM-yyyy h:mm:ss tt'))
  29       }
  30 
  31     ∇ records←{FieldDecSep}AplToCsvRecords aplArray;decimalSeparator;fieldSeparator;test;⎕PP
  32     ⍝ Convert an Apl array to individual CSV records (one record per row of the array).
  33     ⍝ Use the function 'CsvFileAddRecords' to Append the records to a CSV file.
  34     ⍝ Example:  fileName CsvFileAddRecords AplToCsvRecords aplArray
  35     ⍝
  36     ⍝ Use the function 'CsvFileNew' to Create the initial CSV file with a Header.
  37     ⍝
  38     ⍝ aplArray    = Apl array
  39     ⍝ FieldDecSep = Field and Decimal Separator (like ',.' or ';,' )
  40     ⍝ records     = one record per row of 'apl' formatted according to the CSV format
  41      
  42       :If 0≠⎕NC'FieldDecSep'
  43           (fieldSeparator decimalSeparator)←FieldDecSep
  44       :Else
  45         ⍝ Default values
  46           (fieldSeparator decimalSeparator)←',.' ⍝ North America
  47     ⍝     (fieldSeperator decimalSeparator)←';,' ⍝ Europe
  48       :End
  49      
  50     ⍝ Used by ⍕ to display the number of significant digits
  51       ⎕PP←5
  52      
  53       :If ~0∊test←{0=↑1↑0⍴⍵}¨,aplArray
  54         ⍝ 'apl' is all Numeric. This expression is optimize for all numeric array.
  55           records←fieldSeparator{p←⍵ ⋄ ((p=' ')/p)←⍺ ⋄ ((p='¯')/p)←'-' ⋄ p}¨⍕¨↓aplArray
  56           records←{(decimalSeparator,⍵)[('.',⍵)⍳⍵]}¨records
  57      
  58       :Else
  59         ⍝ 'apl' is all characters or a mixture of numeric and characters
  60         ⍝ Code inspired from: http://aplwiki.com/CsvToApl
  61           (test/,aplArray)←{('-',⍵)[('¯',⍵)⍳⍵]}¨⍕¨test/,aplArray             ⍝ Make numbers characters
  62           (test/,aplArray)←{(decimalSeparator,⍵)[('.',⍵)⍳⍵]}¨test/,aplArray  ⍝ Change the decimal separator
  63           test←,'"'∊¨,aplArray                                 ⍝ Double quotes in the text ?
  64           (test/,aplArray)←{⍵/⍨1+'"'=⍵}¨test/,aplArray         ⍝ Double the double quotes
  65           test←∨/¨(⊂fieldSeparator,'"',⎕UCS 13 10)∊¨,aplArray  ⍝ Special characters ?
  66           (test/,aplArray)←{'"',⍵,'"'}¨test/,aplArray          ⍝ Put double quotes on fields with special characters
  67           records←{⊃{⍺,fieldSeparator,⍵}/⍵}¨↓aplArray          ⍝ Separate fields with fieldSeparator
  68      
  69       :End
  70     ∇
  71 
  72     ∇ r←fileName CsvFileAddRecords records;⎕USING
  73     ⍝ Function to Add Record(s) to a CSV file.
  74     ⍝
  75     ⍝ records =  Line(s) of text with a delimiter like ',' or ';'
  76     ⍝ fileName = Fully qualified file name with extension.
  77     ⍝ r        = 1 if successfull, 0 (ERROR) if failure
  78      
  79       :If 1=≡records
  80           records←⊂records
  81       :EndIf
  82      
  83     ⍝ Add a LF and CR to each record(s) and flatten the result.
  84       records←∊{⍵,⎕UCS 13 10}¨records
  85      
  86       :Trap 0
  87           ⎕USING←',mscorlib.dll'
  88           System.IO.File.AppendAllText((⊂,fileName),(⊂,records),(System.Text.Encoding.UTF8))
  89           r←1
  90       :Else
  91         ⍝ Failure: Unexpected Error While Writing the File
  92           r←0('EXCEPTION CsvFileAddRecords: ',⎕EXCEPTION.Message)
  93       :EndTrap
  94     ∇
  95 
  96     ∇ r←fileName CsvFileNew header;⎕USING
  97     ⍝ Function to Create a New File with a CSV Header
  98     ⍝
  99     ⍝ header   = Columns Names separated by a delimiter like ',' or ';'
 100     ⍝ fileName = Fully qualified file name with extension.
 101     ⍝ r        = 1 if successfull, 0 (ERROR) if failure
 102      
 103       header,←⎕UCS 13 10 ⍝ Add CR and LF to the header
 104      
 105       :Trap 0
 106           ⎕USING←',mscorlib.dll'
 107           System.IO.File.WriteAllText((⊂,fileName),(⊂,header),(System.Text.Encoding.UTF8))
 108           r←1
 109       :Else
 110         ⍝ Failure: Unexpected Error While Writing the File
 111           r←0('EXCEPTION CsvFileNew: ',⎕EXCEPTION.Message)
 112       :EndTrap
 113     ∇
 114 
 115     ∇ dt←{FieldDecSep}CsvFileToDT fileName;bufferSize;colNames;cols;colTypes;comment;csv;cultureInfo;escape;fieldCount;fields;firstRecord;hasHeaders;headerRecord;headers;ms;nullValue;quote;sr;streamReader;trimmingOptions;⎕USING;decimalSeparator;fieldSeparator
 116     ⍝ Parse a CSV file to a DataTable with DataTypes preserved
 117     ⍝ Based on: http://www.codeproject.com/Articles/9258/A-Fast-CSV-Reader
 118     ⍝ and https://github.com/phatcher/CsvReader
 119     ⍝ The assembly LumenWorks.Framework.IO.dll needs to be in the same directory as Dyalog.exe
 120     ⍝
 121     ⍝ fileName    = fully qualified file name with extension
 122     ⍝ FieldDecSep = Field and Decimal Separator (like ',.' or ';,' )
 123     ⍝ dt          = DataTable with DataType preserved
 124     ⍝
 125     ⍝ The CSV file must have a Header record, it will be used to give the names to the DataCcolumns of the DataTable
 126     ⍝ The Header can have the DataType of the column within {}, otherwise the DataType will be detected automatically.
 127      
 128       :If 0≠⎕NC'FieldDecSep'
 129           (fieldSeparator decimalSeparator)←FieldDecSep
 130       :Else
 131         ⍝ Default values
 132           (fieldSeparator decimalSeparator)←',.' ⍝ North America
 133     ⍝     (fieldSeparator decimalSeparator)←';,' ⍝ Europe
 134       :End
 135      
 136       ⎕USING←'System.IO,mscorlib.dll' 'LumenWorks.Framework.IO.Csv,LumenWorks.Framework.IO.dll'
 137       ⎕USING,←'System.Data,System.Data.dll' 'System,mscorlib.dll' 'System.Globalization,mscorlib.dll'
 138      
 139     ⍝ Use your local Culture if not appropriate
 140     ⍝ To obtain the Default DecimalSeparator, ThousandsSeparator and CSVSeparator on this computer you can do:
 141     ⍝      ⎕USING←'System.Globalization,mscorlib.dll'
 142     ⍝      CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator
 143     ⍝      CultureInfo.CurrentCulture.NumberFormat.NumberGroupSeparator
 144     ⍝      CultureInfo.CurrentCulture.TextInfo.ListSeparator
 145     ⍝ To obtain your current culture you can do:
 146     ⍝      CultureInfo.CurrentCulture
 147     ⍝ See also https://msdn.microsoft.com/en-us/library/system.globalization.cultureinfo%28v=vs.110%29.aspx
 148       cultureInfo←⎕NEW CultureInfo(⊂'en-US')
 149       cultureInfo.NumberFormat.NumberDecimalSeparator←,decimalSeparator
 150      
 151     ⍝ Set-up those options before constructing the CsvReader. They cannot be setup later
 152       hasHeaders←0       ⍝ Must be zero to be able to define the DataType of the columns. If 1 all columns are automatically of String type.
 153       quote←'"'
 154       escape←'"'
 155       comment←'#'        ⍝ First character of a record that will not be parse
 156       trimmingOptions←1  ⍝ 0=None, 1=Unquoted Only, 2=Quoted Only, 3=All
 157       bufferSize←4096
 158       nullValue←⊂''
 159      
 160     ⍝ Construct the StreamReader
 161       streamReader←⎕NEW StreamReader(⊂,fileName)
 162      
 163     ⍝ Read the Header and remove all the blanks
 164       headerRecord←(streamReader.ReadLine)~' '
 165      
 166     ⍝ Read the first record. Could be multiline.
 167       firstRecord←''
 168       :While 0=streamReader.EndOfStream
 169           firstRecord,←streamReader.ReadLine
 170       :Until 0=1|0.5×+/firstRecord=quote ⍝ Read until even number of quotes
 171      
 172      ⍝ Reset the StreamReader to the beginning.
 173       streamReader.DiscardBufferedData
 174       {}streamReader.BaseStream.Seek((Convert.ToInt64 0),SeekOrigin.Begin)
 175       streamReader.BaseStream.Position←Convert.ToInt64 0
 176      
 177     ⍝ Construct the CsvReader
 178       csv←⎕NEW CsvReader(streamReader,hasHeaders,fieldSeparator,quote,escape,comment,trimmingOptions,bufferSize,nullValue)
 179       csv.MissingFieldAction←csv.MissingFieldAction.ReplaceByNull
 180       csv.DefaultParseErrorAction←csv.DefaultParseErrorAction.AdvanceToNextLine
 181       csv.SkipEmptyLines←1
 182       csv.SupportsMultiline←1
 183      
 184     ⍝ Split the headerRecord at the fieldSeparator character
 185       headers←(~fieldSeparator=headerRecord)⊂headerRecord
 186      
 187     ⍝ Split the first record at the fieldSeparator character
 188       fields←(~fieldSeparator=firstRecord)⊂firstRecord
 189      
 190     ⍝ Read the Header Record and discard it.
 191       {}csv.ReadNextRecord
 192      
 193     ⍝ Check if the DataType was specify in the Header, otherwise detect it on the first record.
 194       colTypes←''
 195       :For header field :In (headers{⍺ ⍵}¨fields)
 196           :If ∧/'{}'∊header
 197             ⍝ DataType is specified in the Header record
 198             ⍝ Could be: Boolean, Byte, Decimal, Double, Single, Int64, Int16, String, DateTime
 199               colTypes,←⊂{h←⌽(⍵⍳'{')↓⍵ ⋄ h←⌽(h⍳'}')↓h ⋄ h}header  ⍝ Get data type between {} of the header
 200      
 201           :Else
 202             ⍝ DataType was not specified in the Header record
 203             ⍝ Analyse the first record to find the DataType
 204             ⍝ The DataType detected will be either: DateTime, Double or String
 205             ⍝ If the first record has empty field(s) it will default to String
 206      
 207               colTypes,←⊂cultureInfo DetectDataType field
 208           :End
 209      
 210       :EndFor
 211      
 212     ⍝ Create the Columns with the DataType, Name and CultureInfo
 213       colNames←{h←(¯1+⍵⍳'{')↑⍵ ⋄ h}¨headers    ⍝ Get the column names from the split headerRecord
 214       cols←⎕NEW¨(⍴headers)⍴Column              ⍝ Instantiate the Columns
 215       cols.Culture←cultureInfo                 ⍝ Set the Culture for each columns for proper parsing
 216       cols.Name←colNames                       ⍝ Set the Columns Names
 217       cols.Type←⍎¨colTypes                     ⍝ Set the Columns Types
 218       csv.Columns.Add¨cols                     ⍝ Set all the Columns previously constructed
 219      
 220     ⍝ Cronstruct the DataTable from the CsvReader
 221       dt←⎕NEW DataTable
 222       dt.TableName←fileName
 223       dt.Load csv
 224      
 225     ⍝ ↓↓↓ Uncomment to see the DataType of each column(s).
 226       (⌷dt.Columns).DataType
 227      
 228     ⍝ Clean-up
 229       streamReader.Close ⋄ streamReader.Dispose ⋄ streamReader←⎕NULL
 230       csv.Dispose ⋄ csv←⎕NULL
 231     ∇
 232 
 233     ∇ noRows←dt DTtoCsvFile fileName;application;excelEngine;workbooks;⎕USING;fieldDelimiter
 234     ⍝ Convert a DataTable to a CSV file.
 235     ⍝ Using the librairy of Syncfusion XlsIO.
 236     ⍝ It is using the Decimal Separator of the local culture.
 237     ⍝
 238     ⍝ fileName = fully qualified CSV file name with extension (could be different than .csv)
 239     ⍝ dt       = DataTable with columns as DateTime, String or Double types.
 240     ⍝ noRows   = Number of rows written to fileName.
 241      
 242       ⎕USING←'Syncfusion.XlsIO,Syncfusion/4.5/Syncfusion.XlsIO.Base.dll' 'System.Text,mscorlib.dll'
 243      
 244     ⍝ Default fieldDelimiter
 245       fieldDelimiter←,','
 246      
 247     ⍝ Instantiate the spreadsheet creation engine.
 248       excelEngine←⎕NEW ExcelEngine
 249      
 250     ⍝ Instantiate the excel application object.
 251       application←excelEngine.Excel
 252       application.DefaultVersion←application.DefaultVersion.Excel2013
 253      
 254     ⍝ Create one worksheet
 255       workbooks←application.Workbooks.Create 1
 256      
 257     ⍝ Import the DataTable to the worksheet
 258       noRows←workbooks.Worksheets[0].ImportDataTable dt 1 1 1 1 ⍝ DataTable, KeepHeader, FirstRow, FirstColumn, PreserveType
 259      
 260     ⍝ Save as CSV file with the UTF-8 format.
 261       workbooks.Worksheets[0].SaveAs(fileName fieldDelimiter Encoding.UTF8)
 262      
 263     ⍝ Clean-up
 264       workbooks.Close ⍬
 265       excelEngine.ThrowNotSavedOnDestroy←0
 266       excelEngine.Dispose
 267     ∇
 268 
 269     ∇ dataType←culture DetectDataType string;dateStyle;numberStyle;ptr;⎕USING
 270     ⍝ Detect the DataType of 'string' using CultureInfo
 271     ⍝ The 3 types detected are: DateTime, Double and String
 272     ⍝
 273     ⍝ string  = vector of characters
 274     ⍝ culture = CultureInfo object
 275      
 276       ⎕USING←'System,mscorlib.dll' 'Dyalog' 'System.Globalization,mscorlib.dll'
 277      
 278       dateStyle←Globalization.DateTimeStyles.None
 279       numberStyle←culture.NumberStyles.Any
 280      
 281     ⍝ Test to detect if 'string' is a Double
 282       ptr←⎕NEW ByRef(0)
 283       :If 1=Double.TryParse((,string)numberStyle culture ptr)
 284     ⍝ Success
 285           dataType←'Double' ⋄ →0
 286       :EndIf
 287      
 288     ⍝ Test to detect if 'string' is a DateTime
 289       ptr←⎕NEW ByRef(⎕NEW DateTime ⎕TS)
 290       :If 1=DateTime.TryParse((,string)culture dateStyle ptr)
 291     ⍝ Success
 292           dataType←'DateTime' ⋄ →0
 293       :EndIf
 294      
 295     ⍝ Default value if DateTime or Double are not detected
 296       dataType←'String'
 297     ∇
 298 
 299     ∇ ShowDT dt;dg;win;⎕USING
 300     ⍝ Show a DataTable in a Syncfusion's Grid
 301     ⍝ dt = DataTable to Show
 302      
 303       ⎕USING←'System.Windows,WPF/PresentationFramework.dll'
 304       ⎕USING,←⊂'Syncfusion.UI.Xaml.Grid,Syncfusion/4.5/Syncfusion.SfGrid.WPF.dll'
 305      
 306       win←⎕NEW Window
 307       win.(Width Height)←640 480
 308       win.Title←'Show DataTable [ ',(dt.TableName),' ]'
 309      
 310       dg←⎕NEW SfDataGrid
 311       dg.AutoGenerateColumns←1
 312       dg.ItemsSource←dt           ⍝ ← the DataTable is set here
 313       dg.AllowResizingColumns←1
 314       dg.(ColumnSizer←ColumnSizer.Star)
 315       dg.AllowEditing←1
 316      
 317       win.Content←dg
 318       win.Show
 319     ∇
 320 
 321     ∇ dt←{fieldSeparator}SimpleCsvFileToDT fileName;bufferSize;comment;csv;escape;hasHeaders;nullValue;quote;streamReader;trimmingOptions;⎕USING
 322     ⍝ Simple CSV File parser to a DataTable where all the DataColumns are of DataType 'String'
 323     ⍝ Based on: http://www.codeproject.com/Articles/9258/A-Fast-CSV-Reader
 324     ⍝ and https://github.com/phatcher/CsvReader
 325     ⍝ The assembly LumenWorks.Framework.IO.dll needs to be in the same directory as Dyalog.exe
 326     ⍝
 327     ⍝ fieldSeparator = Typically ',' or ';'
 328     ⍝ fileName       = fully qualified file name with extension
 329     ⍝
 330     ⍝ Note: The CSV file must have a Header record, it will be used to give the names to the DataColumns.
 331      
 332       ⎕USING←'System.IO,mscorlib.dll' 'LumenWorks.Framework.IO.Csv,LumenWorks.Framework.IO.dll' 'System.Data,System.Data.dll'
 333      
 334       :If 0=⎕NC'fieldSeparator'
 335           fieldSeparator←',' ⍝ Default value if absent
 336       :End
 337      
 338     ⍝ Set-up those options before constructing the CsvReader
 339       hasHeaders←1
 340       quote←'"'
 341       escape←'"'
 342       comment←'#' ⍝ First character of a record that will not be parse
 343       trimmingOptions←1 ⍝ 0=None, 1=Unquoted Only, 2=Quoted Only, 3=All
 344       bufferSize←4096
 345       nullValue←⊂''
 346      
 347     ⍝ Construct the StreamReader
 348       streamReader←⎕NEW StreamReader(⊂,fileName)
 349      
 350     ⍝ Construct the CsvReader
 351       csv←⎕NEW CsvReader(streamReader,hasHeaders,fieldSeparator,quote,escape,comment,trimmingOptions,bufferSize,nullValue)
 352       csv.MissingFieldAction←csv.MissingFieldAction.ReplaceByNull
 353       csv.DefaultParseErrorAction←csv.DefaultParseErrorAction.AdvanceToNextLine
 354       csv.SkipEmptyLines←1
 355       csv.SupportsMultiline←1
 356      
 357     ⍝ Create the DataTable from the CsvReader
 358       dt←⎕NEW DataTable
 359       dt.TableName←fileName
 360       dt.Load csv
 361      
 362     ⍝ Clean-up
 363       streamReader.Close ⋄ streamReader.Dispose ⋄ streamReader←⎕NULL
 364       csv.Dispose ⋄ csv←⎕NULL
 365     ∇
 366 
 367 :EndNamespace