Excel does not render correctly CSV files created by redirecting the standard output of PowerShell or the Command Prompt

You have a PowerShell script that prints a Comma Separated Value file (csv file). For example:

Write-Output "L0,L1,L2,L3,L4,Sizes"
Write-Output "A,AA,AAA,AAAA,AAAAA,10"
Write-Output "B,BB,BBB,BBBB,BBBBB,10"
Write-Output "C,CC,CCC,CCCC,CCCCC,10"
Write-Output "D,DD,DDD,DDDD,DDDDD,10"

It is common to print the output of the script directly to the console, so we can debug the script while we work on it. Once the script is ready, we can simply redirect the output into a file, like this:

.\Make-Csv.ps1 > .\Book5.csv

Bad news! When you open the csv file using Excel, the file is not displayed correctly. Each row is displayed in a single column, the whole line in one cell. You would expect that each row is split on every comma and displayed in multiple columns. The reason is that the file has been encoded using USC-2 Little Endian encoding. Why does Excel do this? Who knows! But there are two solutions for this problem.

The first solution is to add Write-Output "sep=," at the beginning of the script.

The second solution is to encode the file using UTF-8 encoding. For example:

.\Make-Csv.ps1 | Out-File .\Book6.csv -Encoding utf8

The second option requires more work, but it has an advantage. Avoiding the line that contains the sep=, string, makes the csv file compatible with more tools. For example, Power BI does not ignore the sep=, row as Excel does, so omitting the line is a better option if you will use both tools!

How to insert or delete items from a DynamoDB table using Node.js

To get started with DynamoDb, follow these steps:

Introduction

If you are here, you probably have little experience working with DynamoDB. Be aware that DynamoDB is designed for highly scalable storage requirements, and not for traditional SQL queries or tables with low volume of data.

Maybe this is obvious for you, but here is an example of an issue I faced when I started designing application with DynamoDB:

Every column you use in a query must be a partition key or an index. Indexes require additional read capacity units and write capacity units. For example, adding a small index with 5 read capacity units and 5 write capacity units is estimated to cost you USD$2.91 a month.

If you want to write a query equivalent to SELECT * FROM MyTable WHERE Tag = 'Foo' AND Month = 1 AND User = 'Bill' using DynamoDB, where Tag, Month and User are not the primary key, then you will need to set three additional indexes.

You get the idea. If you have not changed you mind, continue reading to get started.

Create a DynamoDB table

First, create a DynamoDB table at https://us-west-2.console.aws.amazon.com/dynamodb . Put attention in the region where you create your database, it is in the URL. In this example, the region is us-west-2.

Create an IAM user

Then, create an IAM user at https://console.aws.amazon.com/iam/home . For simplicity, select the AmazonDynamoDBFullAccess policy. Later you can restrict the permissions of the user. Once the user is created, take note of the Access key ID and the Secret access key.

Install NPM aws-sdk package

Open a terminal/console, and install the AWS Javascript SDK:

npm install aws-sdk --save

Copy-paste Node.js examples

There are two options for Javascript developers: DynamoDB and DocumentClient. The first option has more advance features, but the second one is easier to use because it automatically does some work for you.

Using DynamoDB API

Here is an example using AWS.DynamoDB. Notice how you have to add extra information about data types, and convert all the properties into string values.

Don’t forget to replace the region, the access key ID and the access key ID when copy-pasting the example into a Javascript file.

Using DocumentClient API

Here is an example using AWS.DynamoDB.DocumentClient. Notice there is no need to marshal the primary key or properties:

Archive