-
-
Save miso-soup/7028c89165de4652cb50 to your computer and use it in GitHub Desktop.
<?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; } | |
} | |
} |
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.
@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.
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?)