英文:
azure data factory - Copy data activity mapping "hh:mm" to SQL "Time" column
问题
我试图将一个CSV文件导入Azure SQL，其中包含一个时间数据类型的列。CSV文件中的值采用这种格式："hh:mm"。我已经将我的转换设置配置为以下设置：
但是我遇到了以下错误：
ErrorCode=TypeConversionFailure，在将类型为String（精度：，标度：）的列名为'systime'的值'23:55'转换为类型为TimeSpan（精度：255，标度：7）时发生异常。附加信息：输入字符串格式不正确。
不确定如何解决这个问题。有人能帮助我吗？
提前感谢。
英文:
I am trying to ingest a CSV file into Azure SQL with a column with data type of time. The value in the csv file is in this format "hh:mm". I have setup my conversion setting into these settings:
But then I am having this error:
ErrorCode=TypeConversionFailure,Exception occurred when converting value '23:55' for column name 'systime' from type 'String' (precision:, scale:) to type 'TimeSpan' (precision:255, scale:7). Additional info: Input string was not in a correct format.
Not sure how to solve this issue. Can someone please help me.
Thanks in advance.
答案1
得分: 0
我将Timespan格式设置为hh\:mm，并尝试调试具有复制活动的流水线，以将数据从CSV文件复制到SQL数据库。
映射设置：
- 收到与问题图像中相同的错误消息。
>"message": "ErrorCode=TypeConversionFailure，将值'24:00'转换为列名'Datecolumn1'的类型'String'（精度：，刻度：）时发生异常，转换为类型'TimeSpan'（精度：255，刻度：7）。附加信息：由于数字组件中至少有一个超出范围或包含太多位数，无法解析TimeSpan。"
这是因为源CSV中的一个时间数据是24:00。
hh表示时间跨度中的小时，其值仅在0到23之间。要解决此问题，您可以修改源数据，以删除时间数据类型的小时组件范围之外的任何值。在这种情况下，您可以删除等于或大于24的任何值。您还可以在ADF中使用映射数据流将'24:00'替换为'00:00'。
在数据流中执行以下步骤：
- 在源变换中获取源CSV数据集
- 然后使用派生列变换，将表达式设置为
iif(valuation_time == '24:00', '00:00', valuation_time)
- 使用SQL数据集获取接收变换。
英文:
I gave the Timespan format as hh\:mm and tried to debug the pipeline that has the copy activity to copy data from csv file to SQL database.
Mapping settings:
- Received the same error as in the question's image.
>"message": "ErrorCode=TypeConversionFailure,Exception occurred when converting value '24:00' for column name 'Datecolumn1' from type 'String' (precision:, scale:) to type 'TimeSpan' (precision:255, scale:7). Additional info: The TimeSpan could not be parsed because at least one of the numeric components is out of range or contains too many digits."
This is because one of the time data in source csv is 24:00.
hh which represents the hour in the timespan and its value ranges from 0 to 23 only. To resolve this issue, you can modify the source data to remove any values that are out of range for the hour component of the Time data type. In this case, you can remove any values that are equal to or greater than 24. You can also use mapping dataflow in ADF to replace '24:00' with '00:00'.
Steps to do in dataflow:
- Take the source csv dataset in source transformation
- Then take the derived column transformation and give the expression as
iif(valuation_time == '24:00', '00:00', valuation_time )
- Take the sink transformation with SQL dataset.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论