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