:Namespace netCSV ⍝ Namespace to convert a CSV file to a DataTable while preserving the DataType. ⍝ This namespace is using the assembly LumenWorks.Framework.IO.dll ⍝ ⍝ See http://aplwiki.com/netCSV for more information ⍝ ⍝ February 2016 - Initial version (1.0) ⍝ ⍝ April 2016 - version (1.1) ⍝ SimpleCsvFileToDT and CsvFileToDT modified so it will not block the file ⍝ on disk while doing the conversion to a DataTable (⎕IO ⎕ML ⎕WX)←1 3 3 TsToDateString←{ ⍝ ⍵ = ⎕TS ⍝ Result = Date formatted to a String with no ambiguity. ⎕USING←'System,mscorlib.dll' 'System.Xml,System.Xml.dll' XmlConvert.ToString(((⎕NEW DateTime ⍵))(,'dd-MMM-yyyy')) } TsToTimeString←{ ⍝ ⍵ = ⎕TS ⍝ Result = Time formatted to a String with no ambiguity. ⎕USING←'System,mscorlib.dll' 'System.Xml,System.Xml.dll' XmlConvert.ToString(((⎕NEW DateTime ⍵))(,'h:mm:ss tt')) } TsToDateTimeString←{ ⍝ ⍵ = ⎕TS ⍝ Result = Date and Time formatted to a String with no ambiguity. ⎕USING←'System,mscorlib.dll' 'System.Xml,System.Xml.dll' XmlConvert.ToString(((⎕NEW DateTime ⍵))(,'dd-MMM-yyyy h:mm:ss tt')) } ∇ records←{FieldDecSep}AplToCsvRecords aplArray;decimalSeparator;fieldSeparator;test;⎕PP ⍝ Convert an Apl array to individual CSV records (one record per row of the array). ⍝ Use the function 'CsvFileAddRecords' to Append the records to a CSV file. ⍝ Example: fileName CsvFileAddRecords AplToCsvRecords aplArray ⍝ ⍝ Use the function 'CsvFileNew' to Create the initial CSV file with a Header. ⍝ ⍝ aplArray = Apl array ⍝ FieldDecSep = Field and Decimal Separator (like ',.' or ';,' ) ⍝ records = one record per row of 'apl' formatted according to the CSV format :If 0≠⎕NC'FieldDecSep' (fieldSeparator decimalSeparator)←FieldDecSep :Else ⍝ Default values (fieldSeparator decimalSeparator)←',.' ⍝ North America ⍝ (fieldSeperator decimalSeparator)←';,' ⍝ Europe :End ⍝ Used by ⍕ to display the number of significant digits ⎕PP←5 :If ~0∊test←{0=↑1↑0⍴⍵}¨,aplArray ⍝ 'apl' is all Numeric. This expression is optimize for all numeric array. records←fieldSeparator{p←⍵ ⋄ ((p=' ')/p)←⍺ ⋄ ((p='¯')/p)←'-' ⋄ p}¨⍕¨↓aplArray records←{(decimalSeparator,⍵)[('.',⍵)⍳⍵]}¨records :Else ⍝ 'apl' is all characters or a mixture of numeric and characters ⍝ Code inspired from: http://aplwiki.com/CsvToApl (test/,aplArray)←{('-',⍵)[('¯',⍵)⍳⍵]}¨⍕¨test/,aplArray ⍝ Make numbers characters (test/,aplArray)←{(decimalSeparator,⍵)[('.',⍵)⍳⍵]}¨test/,aplArray ⍝ Change the decimal separator test←,'"'∊¨,aplArray ⍝ Double quotes in the text ? (test/,aplArray)←{⍵/⍨1+'"'=⍵}¨test/,aplArray ⍝ Double the double quotes test←∨/¨(⊂fieldSeparator,'"',⎕UCS 13 10)∊¨,aplArray ⍝ Special characters ? (test/,aplArray)←{'"',⍵,'"'}¨test/,aplArray ⍝ Put double quotes on fields with special characters records←{⊃{⍺,fieldSeparator,⍵}/⍵}¨↓aplArray ⍝ Separate fields with fieldSeparator :End ∇ ∇ r←fileName CsvFileAddRecords records;⎕USING ⍝ Function to Add Record(s) to a CSV file. ⍝ ⍝ records = Line(s) of text with a delimiter like ',' or ';' ⍝ fileName = Fully qualified file name with extension. ⍝ r = 1 if successfull, 0 (ERROR) if failure :If 1=≡records records←⊂records :EndIf ⍝ Add a LF and CR to each record(s) and flatten the result. records←∊{⍵,⎕UCS 13 10}¨records :Trap 0 ⎕USING←',mscorlib.dll' System.IO.File.AppendAllText((⊂,fileName),(⊂,records),(System.Text.Encoding.UTF8)) r←1 :Else ⍝ Failure: Unexpected Error While Writing the File r←0('EXCEPTION CsvFileAddRecords: ',⎕EXCEPTION.Message) :EndTrap ∇ ∇ r←fileName CsvFileNew header;⎕USING ⍝ Function to Create a New File with a CSV Header ⍝ ⍝ header = Columns Names separated by a delimiter like ',' or ';' ⍝ fileName = Fully qualified file name with extension. ⍝ r = 1 if successfull, 0 (ERROR) if failure header,←⎕UCS 13 10 ⍝ Add CR and LF to the header :Trap 0 ⎕USING←',mscorlib.dll' System.IO.File.WriteAllText((⊂,fileName),(⊂,header),(System.Text.Encoding.UTF8)) r←1 :Else ⍝ Failure: Unexpected Error While Writing the File r←0('EXCEPTION CsvFileNew: ',⎕EXCEPTION.Message) :EndTrap ∇ ∇ 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 ⍝ Parse a CSV file to a DataTable with DataTypes preserved ⍝ Based on: http://www.codeproject.com/Articles/9258/A-Fast-CSV-Reader ⍝ and https://github.com/phatcher/CsvReader ⍝ The assembly LumenWorks.Framework.IO.dll needs to be in the same directory as Dyalog.exe ⍝ ⍝ fileName = fully qualified file name with extension ⍝ FieldDecSep = Field and Decimal Separator (like ',.' or ';,' ) ⍝ dt = DataTable with DataType preserved ⍝ ⍝ The CSV file must have a Header record, it will be used to give the names to the DataCcolumns of the DataTable ⍝ The Header can have the DataType of the column within {}, otherwise the DataType will be detected automatically. :If 0≠⎕NC'FieldDecSep' (fieldSeparator decimalSeparator)←FieldDecSep :Else ⍝ Default values (fieldSeparator decimalSeparator)←',.' ⍝ North America ⍝ (fieldSeparator decimalSeparator)←';,' ⍝ Europe :End ⎕USING←'System.IO,mscorlib.dll' 'LumenWorks.Framework.IO.Csv,LumenWorks.Framework.IO.dll' ⎕USING,←'System.Data,System.Data.dll' 'System,mscorlib.dll' 'System.Globalization,mscorlib.dll' ⍝ Use your local Culture if not appropriate ⍝ To obtain the Default DecimalSeparator, ThousandsSeparator and CSVSeparator on this computer you can do: ⍝ ⎕USING←'System.Globalization,mscorlib.dll' ⍝ CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator ⍝ CultureInfo.CurrentCulture.NumberFormat.NumberGroupSeparator ⍝ CultureInfo.CurrentCulture.TextInfo.ListSeparator ⍝ To obtain your current culture you can do: ⍝ CultureInfo.CurrentCulture ⍝ See also https://msdn.microsoft.com/en-us/library/system.globalization.cultureinfo%28v=vs.110%29.aspx cultureInfo←⎕NEW CultureInfo(⊂'en-US') cultureInfo.NumberFormat.NumberDecimalSeparator←,decimalSeparator ⍝ Set-up those options before constructing the CsvReader. They cannot be setup later hasHeaders←0 ⍝ Must be zero to be able to define the DataType of the columns. If 1 all columns are automatically of String type. quote←'"' escape←'"' comment←'#' ⍝ First character of a record that will not be parse trimmingOptions←1 ⍝ 0=None, 1=Unquoted Only, 2=Quoted Only, 3=All bufferSize←4096 nullValue←⊂'' ⍝ Construct the StreamReader ⍝ Using a MemoryStream will not block the file to being written. ms←⎕NEW MemoryStream(⊂File.ReadAllBytes(⊂,fileName)) streamReader←⎕NEW StreamReader(ms) ⍝ streamReader←⎕NEW StreamReader(⊂,fileName) ⍝ Read the Header and remove all the blanks headerRecord←(streamReader.ReadLine)~' ' ⍝ Read the first record. Could be multiline. firstRecord←'' :While 0=streamReader.EndOfStream firstRecord,←streamReader.ReadLine :Until 0=1|0.5×+/firstRecord=quote ⍝ Read until even number of quotes ⍝ Reset the StreamReader to the beginning. streamReader.DiscardBufferedData {}streamReader.BaseStream.Seek((Convert.ToInt64 0),SeekOrigin.Begin) streamReader.BaseStream.Position←Convert.ToInt64 0 ⍝ Construct the CsvReader csv←⎕NEW CsvReader(streamReader,hasHeaders,fieldSeparator,quote,escape,comment,trimmingOptions,bufferSize,nullValue) csv.MissingFieldAction←csv.MissingFieldAction.ReplaceByNull csv.DefaultParseErrorAction←csv.DefaultParseErrorAction.AdvanceToNextLine csv.SkipEmptyLines←1 csv.SupportsMultiline←1 ⍝ Split the headerRecord at the fieldSeparator character headers←(~fieldSeparator=headerRecord)⊂headerRecord ⍝ Split the first record at the fieldSeparator character fields←(~fieldSeparator=firstRecord)⊂firstRecord ⍝ Read the Header Record and discard it. {}csv.ReadNextRecord ⍝ Check if the DataType was specify in the Header, otherwise detect it on the first record. colTypes←'' :For header field :In (headers{⍺ ⍵}¨fields) :If ∧/'{}'∊header ⍝ DataType is specified in the Header record ⍝ Could be: Boolean, Byte, Decimal, Double, Single, Int64, Int16, String, DateTime colTypes,←⊂{h←⌽(⍵⍳'{')↓⍵ ⋄ h←⌽(h⍳'}')↓h ⋄ h}header ⍝ Get data type between {} of the header :Else ⍝ DataType was not specified in the Header record ⍝ Analyse the first record to find the DataType ⍝ The DataType detected will be either: DateTime, Double or String ⍝ If the first record has empty field(s) it will default to String colTypes,←⊂cultureInfo DetectDataType field :End :EndFor ⍝ Create the Columns with the DataType, Name and CultureInfo colNames←{h←(¯1+⍵⍳'{')↑⍵ ⋄ h}¨headers ⍝ Get the column names from the split headerRecord cols←⎕NEW¨(⍴headers)⍴Column ⍝ Instantiate the Columns cols.Culture←cultureInfo ⍝ Set the Culture for each columns for proper parsing cols.Name←colNames ⍝ Set the Columns Names cols.Type←⍎¨colTypes ⍝ Set the Columns Types csv.Columns.Add¨cols ⍝ Set all the Columns previously constructed ⍝ Cronstruct the DataTable from the CsvReader dt←⎕NEW DataTable dt.TableName←fileName dt.Load csv ⍝ ↓↓↓ Uncomment to see the DataType of each column(s). ⍝ (⌷dt.Columns).DataType ⍝ Clean-up streamReader.Close ⋄ streamReader.Dispose ⋄ streamReader←⎕NULL ms.Close ⋄ ms.Dispose ⋄ ms←⎕NULL csv.Dispose ⋄ csv←⎕NULL ∇ ∇ noRows←dt DTtoCsvFile fileName;application;excelEngine;fieldDelimiter;workbooks;⎕USING ⍝ Convert a DataTable to a CSV file. ⍝ Using the library of Syncfusion XlsIO. ⍝ It is using the Decimal Separator of the local culture. ⍝ ⍝ fileName = fully qualified CSV file name with extension (could be different than .csv) ⍝ dt = DataTable with columns as DateTime, String or Double types. ⍝ noRows = Number of rows written to fileName. ⎕USING←'Syncfusion.XlsIO,Syncfusion/4.5/Syncfusion.XlsIO.Base.dll' 'System.Text,mscorlib.dll' ⍝ Default fieldDelimiter fieldDelimiter←,',' ⍝ Instantiate the spreadsheet creation engine. excelEngine←⎕NEW ExcelEngine ⍝ Instantiate the excel application object. application←excelEngine.Excel application.DefaultVersion←application.DefaultVersion.Excel2013 ⍝ Create one worksheet workbooks←application.Workbooks.Create 1 ⍝ Import the DataTable to the worksheet noRows←workbooks.Worksheets[0].ImportDataTable dt 1 1 1 1 ⍝ DataTable, KeepHeader, FirstRow, FirstColumn, PreserveType ⍝ Save as CSV file with the UTF-8 format. workbooks.Worksheets[0].SaveAs(fileName fieldDelimiter Encoding.UTF8) ⍝ Clean-up workbooks.Close ⍬ excelEngine.ThrowNotSavedOnDestroy←0 excelEngine.Dispose ∇ ∇ dataType←culture DetectDataType string;dateStyle;numberStyle;ptr;⎕USING ⍝ Detect the DataType of 'string' using CultureInfo ⍝ The 3 types detected are: DateTime, Double and String ⍝ ⍝ string = vector of characters ⍝ culture = CultureInfo object ⎕USING←'System,mscorlib.dll' 'Dyalog' 'System.Globalization,mscorlib.dll' dateStyle←Globalization.DateTimeStyles.None numberStyle←culture.NumberStyles.Any ⍝ Test to detect if 'string' is a Double ptr←⎕NEW ByRef(0) :If 1=Double.TryParse((,string)numberStyle culture ptr) ⍝ Success dataType←'Double' ⋄ →0 :EndIf ⍝ Test to detect if 'string' is a DateTime ptr←⎕NEW ByRef(⎕NEW DateTime ⎕TS) :If 1=DateTime.TryParse((,string)culture dateStyle ptr) ⍝ Success dataType←'DateTime' ⋄ →0 :EndIf ⍝ Default value if DateTime or Double are not detected dataType←'String' ∇ ∇ ShowDT dt;dg;win;⎕USING ⍝ Show a DataTable in a Syncfusion's Grid ⍝ dt = DataTable to Show ⎕USING←'System.Windows,WPF/PresentationFramework.dll' ⎕USING,←⊂'Syncfusion.UI.Xaml.Grid,Syncfusion/4.5/Syncfusion.SfGrid.WPF.dll' win←⎕NEW Window win.(Width Height)←640 480 win.Title←'Show DataTable [ ',(dt.TableName),' ]' dg←⎕NEW SfDataGrid dg.AutoGenerateColumns←1 dg.ItemsSource←dt ⍝ ← the DataTable is set here dg.AllowResizingColumns←1 dg.(ColumnSizer←ColumnSizer.Star) dg.AllowEditing←1 win.Content←dg win.Show ∇ ∇ dt←{fieldSeparator}SimpleCsvFileToDT fileName;bufferSize;comment;csv;escape;hasHeaders;nullValue;quote;streamReader;trimmingOptions;⎕USING;ms ⍝ Simple CSV File parser to a DataTable where all the DataColumns are of DataType 'String' ⍝ Based on: http://www.codeproject.com/Articles/9258/A-Fast-CSV-Reader ⍝ and https://github.com/phatcher/CsvReader ⍝ The assembly LumenWorks.Framework.IO.dll needs to be in the same directory as Dyalog.exe ⍝ ⍝ fieldSeparator = Typically ',' or ';' ⍝ fileName = fully qualified file name with extension ⍝ ⍝ Note: The CSV file must have a Header record, it will be used to give the names to the DataColumns. ⎕USING←'System.IO,mscorlib.dll' 'LumenWorks.Framework.IO.Csv,LumenWorks.Framework.IO.dll' 'System.Data,System.Data.dll' :If 0=⎕NC'fieldSeparator' fieldSeparator←',' ⍝ Default value if absent :End ⍝ Set-up those options before constructing the CsvReader hasHeaders←1 quote←'"' escape←'"' comment←'#' ⍝ First character of a record that will not be parse trimmingOptions←1 ⍝ 0=None, 1=Unquoted Only, 2=Quoted Only, 3=All bufferSize←4096 nullValue←⊂'' ⍝ Construct the StreamReader ⍝ Using a MemoryStream will not block the file to being written. ms←⎕NEW MemoryStream(⊂File.ReadAllBytes(⊂,fileName)) streamReader←⎕NEW StreamReader(ms) ⍝ streamReader←⎕NEW StreamReader(⊂,fileName) ⍝ Construct the CsvReader csv←⎕NEW CsvReader(streamReader,hasHeaders,fieldSeparator,quote,escape,comment,trimmingOptions,bufferSize,nullValue) csv.MissingFieldAction←csv.MissingFieldAction.ReplaceByNull csv.DefaultParseErrorAction←csv.DefaultParseErrorAction.AdvanceToNextLine csv.SkipEmptyLines←1 csv.SupportsMultiline←1 ⍝ Create the DataTable from the CsvReader dt←⎕NEW DataTable dt.TableName←fileName dt.Load csv ⍝ Clean-up streamReader.Close ⋄ streamReader.Dispose ⋄ streamReader←⎕NULL ms.Close ⋄ ms.Dispose ⋄ ms←⎕NULL csv.Dispose ⋄ csv←⎕NULL ∇ :EndNamespace