CSV Files
A comma-separated values (CSV) file is a text file that uses a comma to separate values. Each line of the file is a data record. Each record consists of one or more fields, separated by commas. In MaSH we can read, process and write to CSV files.
Under the hood MaSH uses the CSV package from The League of Extraordinary Packages. You can read more about the package here.
Reading CSV Files
To read a CSV file we call the read method on the mash.csv object. This returns an instance of the League CSV object.
read
mash.csv.read(File: file) -> League\Csv\Reader
Creates a Reader object.
Parameters
file
A Foldr file object whose content will be parsed as a CSV file.
Natural
set file to mash.file(4, "Sales Records.csv")
set csv to mash.csv.read(file)
printline csvStandard
file = mash.file(4, "Sales Records.csv")
csv = mash.csv.read(file)
printline(csv)Output
Reader [
[
"Region",
"Country",
"Item Type",
"Sales Channel",
"Order Priority",
"Order Date",
"Order ID",
"Ship Date",
"Units Sold",
"Unit Price",
"Unit Cost",
"Total Revenue",
"Total Cost",
"Total Profit"
],
[
"Australia and Oceania",
"Tuvalu",
"Baby Food",
"Offline",
"H",
"5/28/2010",
"669165933",
"6/27/2010",
"9925",
"255.28",
"159.42",
"2533654.00",
"1582243.50",
"951410.50"
],
[
"Central America and the Caribbean",
"Grenada",
"Cereal",
"Online",
"C",
"8/22/2012",
"963881480",
"9/15/2012",
"2804",
"205.70",
"117.11",
"576782.80",
"328376.44",
"248406.36"
],
[
"Europe",
"Russia",
"Office Supplies",
"Offline",
"L",
"5/2/2014",
"341417157",
"5/8/2014",
"1779",
"651.21",
"524.96",
"1158502.59",
"933903.84",
"224598.75"
],
[
"Sub-Saharan Africa",
"Sao Tome and Principe",
"Fruits",
"Online",
"C",
"6/20/2014",
"514321792",
"7/5/2014",
"8102",
"9.33",
"6.92",
"75591.66",
"56065.84",
"19525.82"
],
[
"Sub-Saharan Africa",
"Rwanda",
"Office Supplies",
"Offline",
"L",
"2/1/2013",
"115456712",
"2/6/2013",
"5062",
"651.21",
"524.96",
"3296425.02",
"2657347.52",
"639077.50"
],
[
"Australia and Oceania",
"Solomon Islands",
"Baby Food",
"Online",
"C",
"2/4/2015",
"547995746",
"2/21/2015",
"2974",
"255.28",
"159.42",
"759202.72",
"474115.08",
"285087.64"
],
[
"Sub-Saharan Africa",
"Angola",
"Household",
"Offline",
"M",
"4/23/2011",
"135425221",
"4/27/2011",
"4187",
"668.27",
"502.54",
"2798046.49",
"2104134.98",
"693911.51"
],
[
"Sub-Saharan Africa",
"Burkina Faso",
"Vegetables",
"Online",
"H",
"7/17/2012",
"871543967",
"7/27/2012",
"8082",
"154.06",
"90.93",
"1245112.92",
"734896.26",
"510216.66"
],
[
"Sub-Saharan Africa",
"Republic of the Congo",
"Personal Care",
"Offline",
"M",
"7/14/2015",
"770463311",
"8/25/2015",
"6070",
"81.73",
"56.67",
"496101.10",
"343986.90",
"152114.20"
]
]In the example above you’ll note that the header row appears as the first entry. We can tell the CSV reader to use a specific row as the header using the setHeaderOffset method and passing it the numerical index (usually 0) of the row containing the headers. This will change the reader’s internal data to an array of dictionaries keyed by the headers.
Natural
set file to mash.file(4, "Sales Records.csv")
set csv to mash.csv.read(file)
printline csvStandard
file = mash.file(4, "Sales Records.csv")
csv = mash.csv.read(file)
printline(csv)Output
Reader [
{
"Region": "Australia and Oceania",
"Country": "Tuvalu",
"Item Type": "Baby Food",
"Sales Channel": "Offline",
"Order Priority": "H",
"Order Date": "5/28/2010",
"Order ID": "669165933",
"Ship Date": "6/27/2010",
"Units Sold": "9925",
"Unit Price": "255.28",
"Unit Cost": "159.42",
"Total Revenue": "2533654.00",
"Total Cost": "1582243.50",
"Total Profit": "951410.50"
},
{
"Region": "Central America and the Caribbean",
"Country": "Grenada",
"Item Type": "Cereal",
"Sales Channel": "Online",
"Order Priority": "C",
"Order Date": "8/22/2012",
"Order ID": "963881480",
"Ship Date": "9/15/2012",
"Units Sold": "2804",
"Unit Price": "205.70",
"Unit Cost": "117.11",
"Total Revenue": "576782.80",
"Total Cost": "328376.44",
"Total Profit": "248406.36"
},
{
"Region": "Europe",
"Country": "Russia",
"Item Type": "Office Supplies",
"Sales Channel": "Offline",
"Order Priority": "L",
"Order Date": "5/2/2014",
"Order ID": "341417157",
"Ship Date": "5/8/2014",
"Units Sold": "1779",
"Unit Price": "651.21",
"Unit Cost": "524.96",
"Total Revenue": "1158502.59",
"Total Cost": "933903.84",
"Total Profit": "224598.75"
},
{
"Region": "Sub-Saharan Africa",
"Country": "Sao Tome and Principe",
"Item Type": "Fruits",
"Sales Channel": "Online",
"Order Priority": "C",
"Order Date": "6/20/2014",
"Order ID": "514321792",
"Ship Date": "7/5/2014",
"Units Sold": "8102",
"Unit Price": "9.33",
"Unit Cost": "6.92",
"Total Revenue": "75591.66",
"Total Cost": "56065.84",
"Total Profit": "19525.82"
},
{
"Region": "Sub-Saharan Africa",
"Country": "Rwanda",
"Item Type": "Office Supplies",
"Sales Channel": "Offline",
"Order Priority": "L",
"Order Date": "2/1/2013",
"Order ID": "115456712",
"Ship Date": "2/6/2013",
"Units Sold": "5062",
"Unit Price": "651.21",
"Unit Cost": "524.96",
"Total Revenue": "3296425.02",
"Total Cost": "2657347.52",
"Total Profit": "639077.50"
},
{
"Region": "Australia and Oceania",
"Country": "Solomon Islands",
"Item Type": "Baby Food",
"Sales Channel": "Online",
"Order Priority": "C",
"Order Date": "2/4/2015",
"Order ID": "547995746",
"Ship Date": "2/21/2015",
"Units Sold": "2974",
"Unit Price": "255.28",
"Unit Cost": "159.42",
"Total Revenue": "759202.72",
"Total Cost": "474115.08",
"Total Profit": "285087.64"
},
{
"Region": "Sub-Saharan Africa",
"Country": "Angola",
"Item Type": "Household",
"Sales Channel": "Offline",
"Order Priority": "M",
"Order Date": "4/23/2011",
"Order ID": "135425221",
"Ship Date": "4/27/2011",
"Units Sold": "4187",
"Unit Price": "668.27",
"Unit Cost": "502.54",
"Total Revenue": "2798046.49",
"Total Cost": "2104134.98",
"Total Profit": "693911.51"
},
{
"Region": "Sub-Saharan Africa",
"Country": "Burkina Faso",
"Item Type": "Vegetables",
"Sales Channel": "Online",
"Order Priority": "H",
"Order Date": "7/17/2012",
"Order ID": "871543967",
"Ship Date": "7/27/2012",
"Units Sold": "8082",
"Unit Price": "154.06",
"Unit Cost": "90.93",
"Total Revenue": "1245112.92",
"Total Cost": "734896.26",
"Total Profit": "510216.66"
},
{
"Region": "Sub-Saharan Africa",
"Country": "Republic of the Congo",
"Item Type": "Personal Care",
"Sales Channel": "Offline",
"Order Priority": "M",
"Order Date": "7/14/2015",
"Order ID": "770463311",
"Ship Date": "8/25/2015",
"Units Sold": "6070",
"Unit Price": "81.73",
"Unit Cost": "56.67",
"Total Revenue": "496101.10",
"Total Cost": "343986.90",
"Total Profit": "152114.20"
}
]You can loop through the records contained inside a CSV object just like an array.
Natural
set file to mash.file(4, "Sales Records.csv")
set csv to mash.csv.read(file).setHeaderOffset(0)
# A variable which we'll use to total up the profit from all records
set totalProfit to 0
each csv as record
printline "Item: {{record['Item Type']}} - Profit {{record['Total Profit']}}"
set totalProfit to totalProfit + record["Total Profit"]
end
printline
printline "Total Profit {{ totalProfit }}"Standard
file = mash.file(4, "Sales Records.csv")
csv = mash.csv.read(file).setHeaderOffset(0)
# A variable which we'll use to total up the profit from all records
totalProfit = 0
each (csv as record) {
printline("Item: {{record['Item Type']}} - Profit {{record['Total Profit']}}")
totalProfit = totalProfit + record["Total Profit"]
}
printline()
printline("Total Profit {{ totalProfit }}")Output
Item: Baby Food - Profit 951410.50
Item: Cereal - Profit 248406.36
Item: Office Supplies - Profit 224598.75
Item: Fruits - Profit 19525.82
Item: Office Supplies - Profit 639077.50
Item: Baby Food - Profit 285087.64
Item: Household - Profit 693911.51
Item: Vegetables - Profit 510216.66
Item: Personal Care - Profit 152114.20
Total Profit 3724348.94Creating CSV Files
To create a CSV we call the writer method on the mash.csv object. We can pass in an optional string to create the CSV file from.
writer
mash.csv.writer(string: ?data) -> League\Csv\Writer
Creates a Writer object.
Parameters
data (optional)
A string to create the CSV from.
Natural
set header to ['first name', 'last name', 'email']
set records to [
['grace', 'hopper', '[email protected]'],
['ada', 'lovelace', '[email protected]'],
]
set csv to mash.csv.writer()
csv.insertOne(header)
csv.insertAll(records)
printline csvStandard
header = ['first name', 'last name', 'email']
records = [
['grace', 'hopper', '[email protected]'],
['ada', 'lovelace', '[email protected]'],
]
csv = mash.csv.writer()
csv.insertOne(header)
csv.insertAll(records)
printline(csv)Output
"first name","last name",email
grace,hopper,[email protected]
ada,lovelace,[email protected]Saving CSV files
To save the contents of a Writer simply pass it as the data parameter to File.write.
Natural
set outputFolder to mash.file(8, "csv/output")
set header to ['first name', 'last name', 'email']
set records to [
['grace', 'hopper', '[email protected]'],
['ada', 'lovelace', '[email protected]'],
]
set csv to mash.csv.writer()
csv.insertOne(header)
csv.insertAll(records)
set csvFile to outputFolder.write("New Users.csv", csv)
printline csvFileStandard
outputFolder = mash.file(8, "csv/output")
header = ['first name', 'last name', 'email']
records = [
['grace', 'hopper', '[email protected]'],
['ada', 'lovelace', '[email protected]'],
]
csv = mash.csv.writer()
csv.insertOne(header)
csv.insertAll(records)
csvFile = outputFolder.write("New Users.csv", csv)
printline(csvFile)Output
File {
"name": "New Users.csv",
"path": "csv/output/New Users.csv",
"modified": "2022-02-17T19:52:03+00:00",
"size": 91,
"is_dir": false,
"mimetype": "text/x-comma-separated-values",
"hidden": false,
"extra": [],
"share": {
"id": 8,
"name": "Generated Data"
}
}