Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Importing a CSV file into Azure Table Storage
<?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>
install-package WindowsAzure.Storage
install-package Newtonsoft.Json
<?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>
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; }
}
}
@pomtom

This comment has been minimized.

Copy link

pomtom commented Jan 3, 2020

Getting error on var parser = new TextFieldParser(csvFile, Encoding.UTF8); line. Saying The type or namespace name 'TextFieldParser' could not be found (are you missing a using directive or an assembly reference?)

@bonjarno

This comment has been minimized.

Copy link

bonjarno commented Jan 14, 2020

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.

@MatteoSp

This comment has been minimized.

Copy link

MatteoSp commented Mar 5, 2020

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

@pomtom

This comment has been minimized.

Copy link

pomtom commented Mar 5, 2020

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.

@bonjarno

This comment has been minimized.

Copy link

bonjarno commented Mar 7, 2020

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.