Copy命令中将小数点分隔符从逗号更改为句号

Copy命令中将小数点分隔符从逗号更改为句号

本文介绍了如何在Redshift Copy命令中将小数点分隔符从逗号更改为句号的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在使用以逗号作为小数点分隔符而不是句号(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命令中将小数点分隔符从逗号更改为句号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-24 08:55