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 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 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 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 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 bonjarno commented Mar 7, 2020

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment