File Input Data Package

Harry Bishop
Process Modeling Solutions, Inc.
26 May 2006

hrbishop@pmsi-va.com

Importing data into a notebook for manipulation and analysis is an important file task.  Mathematica comes with the Import function for doing just that.  Certain data however seems resistant to the approaches of this function, for instance part number identification with values such as 23E2300000 and 13D3400.  Import sees these values as scientific numbers with very unwanted results.  Additionally, structured data in the form of tables could be brought in with default values inserted where blank entries exist.  By using a header row and a format row before the first row of data, the FileInputDataDT package provides a means of producing a fixed length data set on each row with a specified format.

Usually a structured dataset is produced from a database.  By providing a set of tools for identifying the data columns and format of the data list the FileInputDataDT package sets up the pattern of a data list, variables for replacing the list with particular values and a function for displaying a data list matched to the format.  The package provides for txt and csv input file structures.  A set of utility functions allows convenience in checking for blank or empty data lists.

The package recognizes formats: word, date, number, serial, currency.  Word is the default format for either no format line or when the format is not recognized.  date reads the string date text and converts it into a date▫time datatype.  number reads any number.  serial reads the text string and unpads leading zeroes so that different versions of serial numbers can be compared.  currency recognizes the $ sign and reads the value as a number.  When a blank or empty value is read in the datafile the package inserts default values in the following way:
word is left blank, date is assigned the file date, serial is left blank, currency and number are assigned i.

The package PMSI`ReadDateTime` is made public to use all the date tools contained in this package, especially those related to reading text strings containing date data. This package can also be found in the Mathematica online library under Information Science and Technology > Information Technology at 
http://library.wolfram.com/infocenter/BySubject/InfoScience/InfoTech/

Reference

Title

File Input Data

Author

Harry Bishop

Summary

This notebook is a package of functions related to reading text and csv files, assigning pattern and variable names and providing data formats based on "word", "number", "date", "serial", and "currency" formats as specified in the column headers.  A number of functions are available for date functions and formatting by using the datatype date▫time from the package PMSI`ReadDateTime`.

Copyright

Rights reserved by, Harry Bishop, Process Modeling Solutions, Inc.

Notebook Version

2.0

Mathematica Version

5.2

History

Reading in data with special formats such as part numbers, serial numbers and currency from database downloads to Excel and then txt. or csv. files caused mis-interpretations by the Mathematica Import function even when Conversion Options were used.  The problems and difficulties with reading parts related databases were the cause of implementing this package.

Keywords

Database datafiles, textfiles, serial number padding, currency, date▫time, and date strings.

Limitation

only the data types mentioned are tested.

Discussion

References:
None.

Requirements

Context`FileInputDataDT`
uses PMSI`ReadDateTime`;

Interface

This part declares the publicly visible functions, options, and values.

Set up the package context, including public imports

BeginPackage["PMSI`FileInputDataDT`",{"PMSI`ReadDateTime`"}]

$ContextPath

Usage messages for the exported functions and the context itself

FileInputDataDT::usage = "FileInputDataDT.m is a package of functions related to reading text and csv files, assigning pattern names and providing data consructs based on \"word\", \"number\", \"date\", \"serial\", and \"currency\" formats.  In addition, a number of functions are available for date and data manipulation. Datatype date▫time is used from package PMSI`ReadDateTime`."

showCurrency::usage = "showCurrency[nr_?NumberQ] pads a number to at most 10 digits with a comma every three digits and inserts a dollar sign."

showArow::usage = "showArow[row_,rowpatt_] provides formatting for dates and currency when a rowpatt is provided such as {word,date,currency}."

displayDates::usage = "displayDates[datalist_List,rowForm_] formats a listing of data records using TableForm and showArow."

showTHErow::usage = "showTHErow[row_,rowpatt_] makes a string from a formatted date for exporting as a true text value."

exportDates::usage = "exportDates[datalist_List,rowForm_] formats a datalisting for export with dates as true text value not wrapped by showDates."

nameContains::usage = "[sName_String,xString_String] provides true or false if string sName contains the target string xString."

isEmpty::usage = "isEmpty[x_List] returns True if x={} otherwise False."

isNotEmpty::usage = "isNotEmpty[x_List] returns False if x={} otherwise True."

isBlank::usage = "isBlank[x_String] returns True if x=\"\" otherwise False."

isSpace::usage = "isSpace[x_String] returns True if x=\" \" otherwise False."

\(Date▫TimeQ::usage = "Date▫TimeQ[expr] gives True if the head of x is date▫time and False otherwise."\)

sortBy::usage = "sortBy[list_,f_] sorts the list according to the ordering function f. So that on using pure functions for f any slot construct will work, for instance, #[[1]]& or Mean[#]&."

unPadSerial::usage = "unPadSerial[x_String] deletes leading zeroes from the string. This is used to provide a base number for serial numbers used for comparison when the padded zeroes are not consistent."

readDataFile::usage = "readDataFile[fileName,options] reads a file of data in textfile or CSV format; (tab or comma delimited formats). fileName is the full string specifying file location. readDataFile returns the following list {titleLine,pattern variables,dataFile,fileName abbreviation,column formats,variable names}.  Example input cell is:  {titleLine,pattVars,dataFile,fnAbbr,rowForm,vars} = readFile[ fileFolder<>filename.txt,line2format->True,abbr->test]."

line2format::usage = "line2format is an option indicating that line 2 of the input data file contains data type specifications for the variables contained in the heading row (line 1). Useable formats are word, date, number, serial, and currency. The default option is True."

abbr::usage = "abbr is an option specifying a shorthand name associated with the input data file.The default option is an Input dialog box requesting abbr."

inputDate::usage = "inputDate is an option specifying the date (string) to be used for blank or non-valid entries in columns identified as having a date format. The default option is the FileDate of fileName"

setVarsToRules::usage = "setVarsToRules[fnAbbr,variableNames] assigns \
Symbol[variableName] list to the pattern elements for use in replacements of \
data records by the element."

Error messages for the exported objects

Implementation

This part contains the actual definitions and any auxiliary functions that should not be visible outside.

Begin the private context (implementation part)

Begin["`Private`"]
{$Context,$ContextPath}

Read in any hidden imports

Unprotect any system functions for which definitions will be made

Definition of auxiliary functions and local (static) variables

numberformatpatterns = {"N*", "n*"};
dateformatpatterns = {"D*", "d*"};
serialformatpatterns = {"S*", "s*"};
currencyformatpatterns = {"C*", "c*"};

comma = ",";
tab = "\t";
 cr = "\r";
newline = "\n";

\(\(entryToNumber[v_] := 
        Module[{trueStr = StringQ[v], xStrNumber, 
            replaceSet = {"$" -> "", " " -> "", "," -> "", """ -> ""}}, 
          xStrNumber = 
            If[trueStr, StringReplace[v, replaceSet], 
              v]; 
Which[
            SameQ[xStrNumber, Null] || Not[trueStr] || 
              isBlank[xStrNumber], I, 
            trueStr && 
              DigitQ[StringReplace[
                  xStrNumber, {"." -> "", "-" -> ""}]], ToExpression[xStrNumber], True, I]];\)\)

dateconvert[x_String, usefileDate_date▫time] := 
        With[{strX = 
              StringReplace[
                x, {"\"" -> "", "\\" -> ""}]}, 
          Which[isBlank[strX] || isSpace[strX] || 
              Not[valid▫dateQ[strX]], usefileDate, 
            True, make…date▫time[
              deconstructDate[strX]]]];

isNumberIndicator[x_] := 
        Block[{StringMatchQ, Or}, 
          Thread[StringMatchQ[x, Or @@ numberformatpatterns], 
            Or]];
isDateIndicator[x_] := 
        Block[{StringMatchQ, Or}, 
          Thread[StringMatchQ[x, Or @@ dateformatpatterns], Or]];
isSerialIndicator[x_] := 
        Block[{StringMatchQ, Or}, 
          Thread[StringMatchQ[x, Or @@ serialformatpatterns], 
            Or]];
isCurrencyIndicator[x_] := 
        Block[{StringMatchQ, Or}, 
          Thread[StringMatchQ[x, Or @@ currencyformatpatterns], 
            Or]];

deleteLead0s[{first_, rest__} /; SameQ[first, "0"]] := 
        deleteLead0s[{rest}];
deleteLead0s[l_List] := l;

fixedLength[list_List, length_Integer, blankList_List] := 
        With[{lsize = Length[list]}, 
          Which[lsize == length, 
            list, 
lsize > length, 
            Take[list, length], 
True, 
            Join[list, Take[blankList, length - lsize]]]];

textInFormat[s_String] := 
        Which[isNumberIndicator[s], "number", 
          isDateIndicator[s], "date", isSerialIndicator[s], "serial", 
          isCurrencyIndicator[s], "currency", 
          True, "word"];

\(\(textToFormat[x_List, toFormat_List, 
          usefileDate_date▫time] := \(\(\(\(MapThread[{#1, #2} &, {toFormat, 
                      x}] /. 
{form_, value_} /; 
                      form == "word" :> 
                    value\) /. 
{form_, value_} /; 
                    form == "number" :> 
                  entryToNumber[value]\) /. {form_, value_} /; 
                  form == "date" :> 
                dateconvert[value, usefileDate]\) /. {form_, value_} /; 
                form == "serial" :> 
              unPadSerial[value]\) /. {form_, value_} /; 
              form == "currency" :> 
            entryToNumber[value];\)\)

\(\(assignTest[format_String] := 
Switch[
          format, 
"word", "_?StringQ", 
"number", "_?NumberQ", 
"date", "_?Date▫TimeQ", 
"serial", "_?StringQ", 
"currency", "_?NumberQ", 
_, "_?StringQ"];\)\)

assignForm[fn_String, v_List, form_String] := 
        With[{symbolName = fn <> form}, 
          Clear[symbolName]; 
Switch[
            form, 
"rowForm", 
            Evaluate[Symbol[symbolName]] = v, 
"vars", 
            Evaluate[Symbol[symbolName]] = v, 
"patt", 
            Evaluate[Symbol[symbolName]] = 
              MapThread[
                ToExpression["e" <> ToString[#1] <> 
                      assignTest[#2]] &, {Range[Length[v]], 
                  v}]]
];

setRule[symbolName_, rule_] := 
        With[{}, Clear[symbolName]; 
          Evaluate[Symbol[symbolName]] = rule];
eVars[n_Integer] := 
        Table[Symbol["e" <> ToString[i]], {i, 1, n}];

Definition of the exported functions

formatting functions

showCurrency[nr_?NumberQ] := 
        PaddedForm[nr, {10, 2}, DigitBlock -> 3, 
          NumberSeparator -> ",", 
          NumberSigns -> {"-$", "$"}, 
          ExponentFunction -> (If[-10 < # < 10, 
                  Null, #] &)];

showArow[row_, rowpatt_] := 
        MapThread[
Which[#2 == "date", 
              ddMMMyy[#1], 
#2 == "currency", 
              showCurrency[#1], 
True, #1] &, {row, 
            rowpatt}];

displayDates[datalist_List, rowForm_] := 
        TableForm[Map[showArow[#, rowForm] &, datalist]];

showTHErow[row_, rowpatt_] := 
        MapThread[
          If[#2 == "date", 
              ToString[displayDTG[#1, "dd-MMM-yyyy"]], #1] &, {row, 
            rowpatt}];

exportDates[datalist_List, rowForm_] := 
        Map[showTHErow[#, rowForm] &, datalist];

utility functions

nameContains[sName_String, xString_String] := 
        StringPosition[sName, xString] ≠ {};

isEmpty[x_List] := Flatten[x] == {};

isNotEmpty[x_List] := Flatten[x] ≠ {};

isBlank[x_String] := x == "";

isSpace[x_String] := x == " ";

sortBy[list_, f_] := list[[Ordering[Map[f, list]]]];

unPadSerial[x_String] := 
        StringJoin[deleteLead0s[Characters[x]]];

Date▫TimeQ[x_date▫time] := 
        True;
Date▫TimeQ[x_] := False;

setVarsToRules[fnAbr_, varStr_] := 
        MapThread[
          setRule[#1, 
              Symbol[fnAbr <> "patt"] :> #2] &, {varStr, 
            eVars[Length[varStr]]}];

readfile

Options[readDataFile] = {line2format -> True, 
          abbr -> "", inputDate -> ""};

readDataFile[fileName_, opts___?OptionQ]:=Module[{fn, fnAbbr, returnedDate, result, nullresult, r, rHead, 
              size, blankLine, readFormat, headingLine, header, formatLine, 
              line2indication, inFormat, rList, separator, datestring, 
              refFileDate},
nullresult = {Null, Null, Null, Null, Null, Null};
{line2indication, fnAbbr, 
                  datestring} = {line2format, abbr, inputDate} /. {opts} /. 
                  Options[readDataFile];
returnedDate = FileDate[fileName];
result=Catch[
If[SameQ[returnedDate, $Failed],Throw[nullresult],
(refFileDate = 
                            dateconvert[datestring, 
                              make…date▫time[
                                returnedDate]];
fn=Switch[fnAbbr,"",
ToString[Input[fileName<>"   - abbreviation?"]],_,fnAbbr]<>"▫";
separator = 
                            Which[nameContains[fileName, ".csv"], comma, 
                              nameContains[fileName, ".mcsv"], comma, 
                              nameContains[fileName, ".txt"], 
                              tab, 
True, 
                              Input["word separator is? comma=, or tab=\t? \
"]];
rseparator = {cr, newline};
r = OpenRead[fileName];
rHead = Read[r, String];\(rHead = 
                            StringReplace[
                              rHead, {" " -> "", "_" -> "", "[" -> "", "]" -> "", "(" -> "", ")" -> ""}]\);
size = 
                            Length[
                                Union[
                                  Flatten[
                                    StringPosition[rHead, separator]]]] + 1;
readFormat = 
                            Table[Word, {i, 1, size}];
blankLine = 
                            Table["", {i, 1, size}];
If[
                            line2indication, (formatLine = 
                                Read[r, readFormat, 
                                  WordSeparators -> separator, 
                                  RecordSeparators -> rseparator, 
                                  NullWords -> 
                                    True]; 
inFormat = 
                                Map[textInFormat[#] &, 
                                  formatLine]; 
Skip[r, 
                                Word, WordSeparators -> separator, 
                                RecordSeparators -> rseparator, 
                                NullWords -> 
                                  True]), 
inFormat = 
                              Table["word", {i, 1, size}]];rList = 
                            ReadList[r, Word, 
                              WordSeparators -> separator, 
                              RecordSeparators -> rseparator, 
                              NullWords -> True, 
                              RecordLists -> True];
rList = 
                            Map[fixedLength[#, size, blankLine] &, rList];
rList = 
                            If[line2indication, 
                              Map[textToFormat[#, inFormat, refFileDate] &, 
                                rList], rList];
headingLine = 
                            Read[StringToStream[rHead], readFormat, 
                              WordSeparators -> separator, 
                              NullWords -> True];
header = 
                            Map[StringInsert[#, fn, 1] &, headingLine];
rowFormat = 
                            assignForm[fn, inFormat, "rowForm"];
pattFormat = 
                            assignForm[fn, inFormat, "patt"];
varsFormat = 
                            assignForm[fn, header, "vars"];
Close[r];
Print[headingLine];
Print[
                            fn <> "rowForm =", rowFormat];
Print[fn <> "patt =", 
                            pattFormat];
Print[fn <> "vars =", 
                            varsFormat];
{headingLine, pattFormat, rList, fn, 
                            rowFormat, varsFormat})]];
\(If[
                SameQ[result, 
                  nullresult], 
\((Print[
StyleForm["No such file ", 
                      FontFamily -> "Century Schoolbook", 
                      FontWeight -> "Bold", FontColor -> Red], 
                    StyleForm[fileName, FontFamily -> "Courier", 
                      FontSlant -> "Italic"]]; 
nullresult)\), 
result]\)
];

End the private context

End[ ]

Epilog

This section protects exported symbols and ends the package.

Protect exported symbol

Alternative: protect all exported symbols

Protect[Evaluate[$Context <> "*"]]

End the package context

EndPackage[ ]

Examples, Tests

Examples, tests for the use of the package can go here.

The options allow specification of a format line (default is true), a filename abbreviation (default is to ask for input), and an input Date to use for blank or invalid date entries.
The default date is the file date.

Options[readDataFile]

File Path results in an invalid file.

{titleLine, pattVars, dataFile, fnAbbr, rowForm, vars} = 
        readDataFile[ "/File Organizer/OR Tools/test file"];

Correcting the filename by putting on the extension results in a valid read and return of variables.

{titleLine, pattVars, dataFile, fnAbbr, rowForm, vars} = 
        readDataFile[ "/File Organizer/OR Tools/test file.txt"];

dataFile

Establish rules between the data list pattern and individual elements identified by the variable names

setVarsToRules[fnAbbr, vars];

vars

Individual elements of a data list can be retrieved in the following way:

dataFile /. test▫Cost
dataFile /. test▫Note