A CSV editor/viewer. The simplest way to keep notes. Light, clean, and free. Simplenote is now available for iOS, Android, Mac, and the web. Modern CSV is exactly this type of tool. It offers a great array of options and features and at the same time is fast and easy to use. With this in mind, there is a whole list of things that this little program does right when it comes to CSV documents. Duplicate rows, columns, and cells.
Modern CSV is exactly this type of tool. It offers a great array of options and features and at the same time is fast and easy to use. With this in mind, there is a whole list of things that this little program does right when it comes to CSV documents. Quick Editing Multi-cell editing Duplicate rows, columns, and cells. Move rows, columns,. Modern CSV is exactly this type of tool. It offers a great array of options and features and at the same is fast and easy to use. With this in mind, there is a whole list of things that this little program does right when it comes to CSV documents. Quick Editing Multi-cell editing Duplicate rows, columns, and cells. Move rows, columns, and cells. Modern CSV is exactly this type of tool. It offers a great array of options and features and at the same is fast and easy to use. With this in mind, there is a whole list of things that this little program does right when it comes to CSV documents.
-->March 2016
Volume 31 Number 3
Modern Cave Painting Images
[Modern Apps]
By Frank La
Modern Cv Maker
Parsing a Comma Separated Value (CSV) file sounds easy enough at first. Quickly, however, the task becomes more and more intricate as the pain points of CSV files become clear. If you’re not familiar with the format, CSV files store data in plain text. Each line in the file constitutes a record. Each record has its fields typically delineated by a comma, hence the name.
Modern Cavity Pizza
Developers today enjoy standards among data exchange formats. The CSV file “format” harkens to an earlier time in the software industry before JSON, before XML. While there’s a Request for Comments (RFC) for CSV files (bit.ly/1NsQlvw), it doesn’t enjoy official status. Additionally, it was created in 2005, decades after CSV files started to appear in the 1970s. As a result, there exists quite a bit of variation to CSV files and the rules are a bit murky. For instance, a CSV file might have fields separated by tabs, a semicolon or any character.
In practical terms, the Excel implementation of CSV import and export has become the de-facto standard and is seen most widely in the industry, even outside the Microsoft ecosystem. Accordingly, the assumptions I make in this article about what constitutes “correct” parsing and formatting will be based upon how Excel imports/exports CSV files. While most CSV files will fall in line with the Excel implementation, not every file will. Toward the end of this column, I introduce a strategy to handle such uncertainty.
A fair question to ask is, “Why even write a parser for a decades-old quasi-format in a very new platform?” The answer is simple: Many organizations have legacy data systems. Thanks to the file format’s long life span, nearly all of these legacy data systems can export to CSV. Furthermore, it costs very little in terms of time and effort to export data to CSV. Accordingly, there are plenty of CSV-formatted files in larger enterprise and government data sets.
Designing an All-Purpose CSV Parser
Despite the lack of an official standard, CSV files typically share some common traits.
Generally speaking, CSV files: are plain text, contain one record per line, have records in each line separated by a delimiter, have one-character delimiters and present fields in the same order.
These common traits outline a general algorithm, which would consist of three steps:
- Split a string along the line delimiter.
- Split each line along the field delimiter.
- Assign each field value to a variable.
This would be fairly easy to implement. The code in Figure 1 parses the CSV input string into a List<Dictionary<string, string>>.
Figure 1 Parsing the CSV Input String into List<Dictionary<string,string>>
This approach works great using an example like the following office divisions and their sales numbers:
To retrieve values from the string, you would iterate through the List and pull out values in the Dictionary using the zero-based field index. Retrieving the office division field, for example, would be as simple as this:
While this works, the code isn’t as readable as it could be.
A Better Dictionary
Many CSV files include a header row for the field name. The parser would be easier for developers to consume if it used the field name as a key for the dictionary. As any given CSV file might not have a header row, you should add a property to convey this information:
For instance, a sample CSV file with a header row might look something like this:
Ideally, the CSV parser would be able to take advantage of this piece of metadata. This would make the code more readable. Retrieving the office division field would look something like this:
Blank Fields
Blank fields occur commonly in data sets. In CSV files, a blank field is represented by having an empty field in a record. The delimiter is still required. For example, if there were no Employee data for the East office, the record would look like this:
If there were no Unit Sales data, as well as no Employee data, the record would look like this:
Every organization has its own -President Roosevelt'Logic will get you from A to B. Imagination will take you everywhere.' -Albert Einstein
The data in Figure 3 would be represented in CSV as this:
Quote
It might be clearer now that the field is wrapped in quotation marks and that the individual quotation marks in the field’s content are doubled up.
Edge Cases
As I mentioned in the opening section, not all files will adhere to the Excel implementation of CSV. The lack of a true specification for CSV makes it difficult to write one parser to handle every CSV file in existence. Edge cases will most certainly exist and that means the code has to leave a door open to interpretation and customization.
Inversion of Control to the Rescue
Given the CSV format’s hazy standard, it’s not practical to write a comprehensive parser for all imaginable cases. It might be more ideal to write a parser to suit a particular need of an app. Using Inversion of Control lets you customize a parsing engine for a particular need.
To accomplish this, I’ll create an interface to outline the two core functions of parsing: extracting records and extracting fields. I decided to make the IParserEngine interface asynchronous. This makes sure any app using this component will remain responsive no matter how large the CSV file is:
Then I add the following property to the CSVParser class:
I then offer developers a choice: use the default parser or inject their own. To make it simple, I’ll overload the constructor:
The CSVParser class now provides the basic infrastructure, but the actual parsing logic is contained within the IParserEngine interface. For convenience of developers, I created the DefaultParserEngine, which can process most CSV files. I took into account the most likely scenarios developers will encounter.
Reader Challenge
I have taken into account the bulk of scenarios developers will encounter with CSV files. However, the indefinite nature of the CSV format makes creating a universal parser for all cases impractical. Factoring all the variations and edge cases would add significant cost and complexity of the cost along with impacting performance.
I’m certain that there are CSV files out “in the wild” that the DefaultParserEngine will not be able to handle. This is what makes the dependency injection pattern a great fit. If developers have a need for a parser that can handle an extreme edge case or write something more performant, they certainly are welcome to do so. Parser engines could be swapped out with no changes to the consuming code.
The code for this project is available at bit.ly/1To1IVI.
Wrapping Up
CSV files are a leftover from days gone by and, despite the best efforts of XML and JSON, are still a commonly used data exchange format. CSV files lack a common specification or standard and, while they often have common traits, are not certain to be in place in any given file. This makes parsing a CSV file a non-trivial exercise.
Given a choice, most developers would probably exclude CSV files from their solutions. However, their widespread presence in legacy enterprise and government data sets may preclude that as an option in many scenarios.
Simply put, there is a need for a CSV parser for Universal Windows Platform (UWP) apps and a real-world CSV parser has to be flexible and robust. Along the way, I demonstrated here a practical use for dependency injection to provide that flexibility. While this column and its associated code target UWP apps, the concept and code apply to other platforms capable of running C#, such as Microsoft Azure or Windows desktop development.
Frank La Vigneis a technology evangelist on the Microsoft Technology and Civic Engagement team, where he helps users leverage technology in order to create a better community. He blogs regularly at FranksWorld.com and has a YouTube channel called Frank’s World TV (youtube.com/FranksWorldTV).
Thanks to the following technical expert for reviewing this article: Rachel Appel
-->Applies to
- Windows 10
- Windows Holographic, version 2004
Windows Autopilot device registration can be done within your organization by manually collecting the hardware identity of devices (hardware hashes) and uploading this information in a comma-separated-value (CSV) file. Capturing the hardware hash for manual registration requires booting the device into Windows 10. Therefore, this process is intended primarily for testing and evaluation scenarios.
Device owners can only register their devices with a hardware hash. Other methods (PKID, tuple) are available through OEMs or CSP partners.
This article provides step by step guidance to perform manual registration. For an overview of registration and manual registration, see the following topics:
For more information about registering HoloLens 2 devices with Windows Autopilot, see Windows Autopilot for HoloLens 2.
Important
In Windows 10, version 1809 and earlier, it is important to not connect devices to the Internet prior to capturing the hardware hash and creating an Autopilot device profile. This includes collecting the hardware hash, uploading the .CSV into MSfB or Intune, assigning the profile, and confirming the profile assignment. Connecting the device to the Internet before this process is complete will result in the device downloading a blank profile that is stored on the device until it's explicity removed. In Windows 10 version 1809, you can clear the cached profile by restarting OOBE. In previous versions, the only way to clear the stored profile is to re-install the OS, reimage the PC, or run sysprep /generalize /oobe.
After Intune reports the profile ready to go, only then should the device be connected to the Internet.
Note
If OOBE is restarted too many times it can enter a recovery mode and fail to run the Autopilot configuration. You can identify this scenario if OOBE displays multiple configuration options on the same page, including language, region, and keyboard layout. The normal OOBE displays each of these on a separate page. The following value key tracks the count of OOBE retries:
HKCUSOFTWAREMicrosoftWindowsCurrentVersionUserOOBE
To ensure OOBE has not been restarted too many times, you can change this value to 1.
Prerequisites
- Azure Active Directory Premium subscription
Required permissions
Device enrollment can be done by an Intune Administrator or a Policy and Profile Manager. You can also create a custom Autopilot device manager role by using Role Based Access Control and creating this role. Autopilot device management only requires that you enable all permissions under Enrollment programs, with the exception of the four token management options.
Collecting the hardware hash from existing devices using Microsoft Endpoint Configuration Manager
Microsoft Endpoint Configuration Manager automatically collects the hardware hashes for existing Windows 10 devices. For more information, see Gather information from Configuration Manager for Windows Autopilot. You can extract the hash information from Configuration Manager into a CSV file.
Note
Before uploading the CSV file on Intune, please make sure that the first row contains the device serial number, Windows product ID, hardware hash, group tag, and assigned user. If there is header information on the top of CSV file, please delete that header information. See details at Enroll Windows devices in Intune.
Collecting the hardware hash from existing devices using PowerShell
The hardware hash for an existing device is available through Windows Management Instrumentation (WMI), as long as that device is running a supported version of Windows 10 semi-annual channel. You can use a PowerShell script (Get-WindowsAutoPilotInfo.ps1) to get a device's hardware hash and serial number. The serial number is useful to quickly see which device the hardware hash belongs to.
To use this script, you can use either of the following methods:
- Download the script file from the PowerShell Gallery and run it on each computer.
- Install the script directly from the PowerShell Gallery.
To install it directly and capture the hardware hash from the local computer, use the following commands from an elevated Windows PowerShell prompt:
You can run the commands remotely if both of the following are true:
- WMI permissions are in place
- WMI is accessible through the Windows Firewall on the remote computer.
Csv Editor
For more information about running the script, see the Get-WindowsAutoPilotInfo script’s help by using “Get-Help Get-WindowsAutoPilotInfo.ps1”.
Add devices
Now that you have captured hardware hashes in a CSV file, you can add Windows Autopilot devices by importing the CSV file. The following are instructions to import the CSV using Intune:
In the Microsoft Endpoint Manager admin center, choose Devices > Windows > Windows enrollment > Devices (under Windows Autopilot Deployment Program > Import.
Under Add Windows Autopilot devices, browse to a CSV file listing the devices that you want to add. The CSV file should list:
- Serial numbers.
- Windows product IDs.
- Hardware hashes.
- Optional group tags.
- Optional assigned user.
You can have up to 500 rows in the list. The header and line format is shown below:
Device Serial Number,Windows Product ID,Hardware Hash,Group Tag,Assigned User<serialNumber>,<ProductID>,<hardwareHash>,<optionalGroupTag>,<optionalAssignedUser>Important
When you use CSV upload to assign a user, make sure that you assign valid UPNs. If you assign an invalid UPN (incorrect username), your device may be inaccessible until you remove the invalid assignment. During CSV upload the only validation we perform on the Assigned User column is to check that the domain name is valid. We're unable to perform individual UPN validation to ensure that you're assigning an existing or correct user.
Note
The CSV file being imported into the Intune portal must be formatted as described above. Extra columns are not supported. Quotes are not supported. Only ANSI-format text files can be used (not Unicode). Headers are case-sensitive. Editing the file in Excel and saving as a CSV file will not generate a usable file due to these requirements.
Choose Import to start importing the device information. Importing can take several minutes.
After import is complete, choose Devices > Windows > Windows enrollment > Devices (under Windows Autopilot Deployment Program > Sync. A message displays that the synchronization is in progress. The process might take a few minutes to complete, depending on how many devices are being synchronized.
Refresh the view to see the new devices.
Edit Autopilot device attributes
After you've uploaded an Autopilot device, you can edit certain attributes of the device.
- In the Microsoft Endpoint Manager admin center, select Devices > Windows > Windows enrollment > Devices (under Windows Autopilot Deployment Program).
- Select the device you want to edit.
- In the pane on the right of the screen, you can edit:
- Device name.
- Group tag.
- User Friendly Name (if you've assigned a user).
- Select Save.
Note
Device names can be configured for all devices, but are ignored in Hybrid Azure AD joined deployments. Device name still comes from the domain join profile for Hybrid Azure AD devices.
Delete Autopilot devices
You can delete Windows Autopilot devices that aren't enrolled into Intune:
- Delete the devices from Windows Autopilot at Devices > Windows > Windows enrollment > Devices (under Windows Autopilot Deployment Program). Choose the devices you want to delete, then choose Delete. Windows Autopilot device deletion can take a few minutes to complete.
Completely removing a device from your tenant requires you to delete the Intune device, the Azure Active Directory device, and the Windows Autopilot device records. These deletions can all be done from Intune:
- First, delete the devices from Windows Autopilot at Devices > Windows > Windows enrollment > Devices (under Windows Autopilot Deployment Program). Choose the devices you want to delete, then choose Delete. Windows Autopilot device deletion can take a few minutes to complete.
- If the devices are enrolled in Intune, you must delete them from the Intune All devices blade.
- Delete the devices in Azure Active Directory devices at Devices > Azure AD devices.
Next steps
Create device groups: Device groups are used to apply Autopilot deployment profiles.