将数据从Excel文件加载到Azure数据仓库 [英] Loading data from Excel file into Azure Datawarehouse
问题描述
我已成功将数据从CSV加载到Azure SQL Server数据仓库，但是现在我有一个excel文件作为源，当我尝试将此excel文件读入BLOB CSV时，它会创建一个带有垃圾字符的文件.任何帮助将不胜感激.
I have succeeded in loading data from CSV to Azure SQL Server data warehouse, however I now have a excel file as a source and when I try reading this excel file into BLOB CSV it creates a file with junk characters. Any help would be appreciated.
推荐答案
要在数据工厂中处理此问题，您需要使用一个自定义活动(DotNotActivity)，该活动首先将Excel文件转换为CSV.然后进行一个下游活动，根据需要处理CSV数据集.
To handle this within data factory you'll need to use a custom activity (DotNotActivity) that first converts the Excel file to CSV. Then have a downstream activity that deals with the CSV dataset as required.
自定义活动将需要编写一些C#类来处理对话.使用Office互操作性库或执行类似的操作将Excel文件视为数据表.
The custom activity will require some C# classes to be written that handle the conversation. Either using the Office Interoperability libraries or by doing something like this treating the Excel file as a data table.
public static string ToCSV(this DataTable table)
{
var result = new StringBuilder();
for (int i = 0; i < table.Columns.Count; i++)
{
result.Append(table.Columns[i].ColumnName);
result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
}
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
result.Append(row[i].ToString());
result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
}
}
return result.ToString();
}
或查看与此相关的其他SO问题.例如:
Or check out other SO questions about the same. Eg:
有没有.xls文件转换为.csv文件的简单方法? (Excel)
就其他Azure Data Factory胶而言，已编译的库将需要存储在Blob存储中，并且实际上将由Azure批处理服务执行.如果要针对您的Azure Data Lake存储进行身份验证，则需要Azure AD服务主体.
In terms of the other Azure Data Factory glue, the compiled libraries will need to be stored in blob storage and will actually get executed by an Azure Batch Service. Which will require an Azure AD service principal if to authenticate against your Azure Data Lake storage.
查看此Blob帖子，以获取有关创建自定义活动的更多详细信息.
Check out this blob post for more details on creating the custom activity.
https://www. purplefrogsystems.com/paul/2016/11/creating-azure-data-factory-custom-activities/
这是用于ADL身份验证的:
And this one for authentication for ADL:
希望这会有所帮助.
这篇关于将数据从Excel文件加载到Azure数据仓库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
