如何在Redshift Copy命令中将小数点分隔符从逗号更改为句号 [英] How to change decimal separator from comma to fullstop in redshift copy command

问题描述

我正在使用以逗号作为小数点分隔符而不是句号(3,99而不是3.99)的原始数据.有没有一种方法可以直接在redshift copy命令中将其转换，而不必先上传然后再进行更改?

I'm working with raw data that has a comma as the decimal separator rather than fullstop (3,99 instead of 3.99). Is there a way to convert this directly in redshift copy command rather than having to upload then change afterwards?

推荐答案

要考虑两个问题:

• 字段分隔符
• 替换字符

Amazon Redshift中的默认分隔符 除非使用了CSV选项，否则COPY 命令是竖线字符(|)，在这种情况下，默认的分隔符是逗号(，).

The default delimiter in the Amazon Redshift COPY command is a pipe character ( | ), unless the CSV option is used, in which case the default delimiter is a comma ( , ).

因此，如果您的文件由非逗号字符(例如pip"|"符号)分隔，则数字中的逗号将不会跨字段分割.

Thus, if your file is delimited by a non-comma character (eg a pip "|" symbol), then the comma in a number will not split across fields.

关于将逗号转换为小数，这不可能.您将需要将字段作为字符串加载，然后运行UPDATE命令以将字符串复制到数字字段(也需要一些字符替换).

As to converting the comma into a decimal, this is not possible. You will need to load the field as a string, and then run an UPDATE command to copy the string into a numeric field (with a bit of character replacement too).

或者，尝试在文件加载到Redshift之前(例如通过sed)对其进行预处理，因此在加载到Redshift之前它是干净的.

Alternatively, try to pre-process the file before loading into Redshift (eg through sed), so it is clean before loading into Redshift.

这篇关于如何在Redshift Copy命令中将小数点分隔符从逗号更改为句号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！