Importing a CSV file into Azure Table Storage

App.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <startup>
      <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6" />
  </startup>
  <appSettings>
    <add key="AzureStorageConnectionString" value="DefaultEndpointsProtocol=https;AccountName=*****;AccountKey=*****" />
  </appSettings>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Newtonsoft.Json" publicKeyToken="30ad4fe6b2a6aeed" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-7.0.0.0" newVersion="7.0.0.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

nuget

install-package WindowsAzure.Storage
install-package Newtonsoft.Json

packages.config

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="Microsoft.Azure.KeyVault.Core" version="1.0.0" targetFramework="net46" />
  <package id="Microsoft.Data.Edm" version="5.6.4" targetFramework="net46" />
  <package id="Microsoft.Data.OData" version="5.6.4" targetFramework="net46" />
  <package id="Microsoft.Data.Services.Client" version="5.6.4" targetFramework="net46" />
  <package id="Newtonsoft.Json" version="7.0.1" targetFramework="net46" />
  <package id="System.Spatial" version="5.6.4" targetFramework="net46" />
  <package id="WindowsAzure.Storage" version="5.0.2" targetFramework="net46" />
</packages>

Program.cs

using Microsoft.VisualBasic.FileIO;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Table;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Miso.TableSample
{
    class Program
    {
        private static readonly CloudStorageAccount cloudStorageAccount =
            CloudStorageAccount.Parse(ConfigurationManager.AppSettings["AzureStorageConnectionString"]);

        static void Main(string[] args)
        {
            string[] csvFileList = new string[] 
            {
                @"C:\Users\hoge\Desktop\australlia.csv",
                @"C:\Users\hoge\Desktop\canada.csv",
                @"C:\Users\hoge\Desktop\uk.csv",
                @"C:\Users\hoge\Desktop\us.csv"
            };
            int customerId = 0;

            foreach(var csvFile in csvFileList)
            {
                var parser = new TextFieldParser(csvFile, Encoding.UTF8);
                parser.TextFieldType = FieldType.Delimited;
                parser.SetDelimiters(",");
                parser.HasFieldsEnclosedInQuotes = true;
                parser.TrimWhiteSpace = true;

                var customerList = new List<Customer>();

                while (!parser.EndOfData)
                {
                    string[] row = parser.ReadFields();
                    bool isHeader = (row[0] == "first_name");
                    if (isHeader)
                        continue;

                    var customer = new Customer(row);
                    customer.Id = ++customerId;
                    customerList.Add(customer);
                }
                Insert(customerList, Path.GetFileNameWithoutExtension(csvFile));
            }
        }
        static void Insert(IEnumerable<Customer> customerList, string partitionKey)
        {
            Insert(customerList.Select(customer =>
            {
                var entity = new CustomerTableEtntity(partitionKey, customer.Id);
                entity.Object = JsonConvert.SerializeObject(customer);
                return entity;
            }));
        }

        static void Insert(IEnumerable<ITableEntity> entities)
        {
            var tableClient = cloudStorageAccount.CreateCloudTableClient();
            var table = tableClient.GetTableReference("CustomerTable");
            table.CreateIfNotExists();

            var batchOperations = new TableBatchOperation();

            foreach (var entityGroup in entities.GroupBy(f => f.PartitionKey))
            {
                foreach (var entity in entityGroup)
                {
                    if (batchOperations.Count < 100)
                    {
                        batchOperations.Add(TableOperation.Insert(entity));
                    }
                    else
                    {
                        table.ExecuteBatch(batchOperations);
                        batchOperations = new TableBatchOperation { TableOperation.Insert(entity) };
                    }
                }
                table.ExecuteBatch(batchOperations);
                batchOperations = new TableBatchOperation();
            }

            if (batchOperations.Count > 0)
            {
                table.ExecuteBatch(batchOperations);
            }
        }
    }
    
    public class CustomerTableEtntity : TableEntity
    {
        public CustomerTableEtntity(string partitionKey, int id)
        {
            PartitionKey = partitionKey;
            RowKey = id.ToString("00000000");
        }

        public string Object { get; set; }
    }

    public class Customer
    {
        public Customer(string[] row)
        {
            int i = -1;
            FirstName = row[++i];
            LastName = row[++i];
            CompanyName = row[++i];
            Address = row[++i];
            City = row[++i];
            State = row[++i];
            Post = row[++i];
            Phone1 = row[++i];
            Phone2 = row[++i];
            Email = row[++i];
            Web = row[++i];
        }

        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string CompanyName { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Post { get; set; }
        public string Phone1 { get; set; }
        public string Phone2 { get; set; }
        public string Email { get; set; }
        public string Web { get; set; }
    }
}

@bonjarno: you do really think this is the right place to complain about that?

Have to say I am cynically shocked how obtuse the process of simply importing from a CSV in to an Azure Table is. Have I missed something? As a developer, how many times have I quickly - in SSMS - gone Right-Click the DB, All Tasks, Import, etc.? Is there nothing that straight forward available in Azure? I admin programming is fun, and the above solution is apparently what I will do, but since my employer charges the client by the hour, and "we" convinced them how great Azure is, I feel a little sheepish on this.

I could understand your frustration, but this is the reality.

Yes. Considering the context, the tone i used, and the closing reference to sheepishness felt, i definitely think it was appropriate.Thanks,John MarnoOn Mar 5, 2020 5:05 AM, MatteoSp <notifications@github.com> wrote:@bonjarno: you do really think this is the right place to complain about that? —You are receiving this because you were mentioned.Reply to this email directly, view it on GitHub, or unsubscribe.