blocks|key|2922248|text|为了便于导入，我建议将数值列转换为文本列。原因是空字符串不是有效的数值。将数值列更改为文本列，导入CSV文件，将空值更新为null或0，然后将列更改回整数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2922249|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I would suggest converting your numeric columns to text columns for the purposes of your import. The reason is that an empty string is not a valid numeric value. Change your numeric columns to text columns, import the CSV file, update the empty values to null or 0, and then change the column back to an integer.

blocks|key|2922282|text|你的陈述是可疑的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2922283|COPY+data+FROM+'data.dat'+USING+DELIMITERS+','+CSV;|code-block|syntax|javascript|2922284|DELIMITERS在7.3之前的版本中使用。为了不破坏旧代码，它仍然被支持，但是不要再使用它了。正确的关键字是DELIMITER。而且您根本不需要指定,，因为它是FORMAT+CSV的默认设置。|offset|length|style|CODE|BOLD|2922285|另外，我引用manual+here|2922286|2922287|+文件名|blockquote|2922288|2922289|输入或输出文件的绝对路径名。Windows用户可能需要使用E''字符串和路径名中使用的所有反斜杠。|2922290|2922291|大胆强调我的观点。在UNIX上用'/path/to/data.dat'替换'data.dat'，在Windows上用E'C:\\path\\to\\data.dat'替换。|2922292|对于7.3%2B版本，请使用：|2922293|COPY+data+FROM+'/path/to/data.dat'+CSV|2922294|对于9.0%2B版本，请使用：|2922295|COPY+data+FROM+'/path/to/data.dat'+(FORMAT+CSV)|2922296|2922297|如果仍然出现此错误：|2922298|2922299|错误:类型numeric:+CONTEXT:+COPY+data的输入语法无效，第13行，列interval_2400：|2922300|2922301|2922302|那么，很明显，源文件与表data的结构不匹配。看一下源文件，转到第13行，看看列interval_2400的值是什么。很有可能，它不是数字。特别是，在数值类型的列中不允许使用empty+string+('')。|2922303|您可以修改源文件，也可以调整表定义|2922304|ALTER+TABLE+data+ALTER+COLUMN+interval_2400+TYPE+text;|2922305|或者任何更合适的类型。从名字来看可能是interval。(但text几乎接受所有输入值。)|2922306|或者，更好的方法是，创建一个修改过的临时文件，对其执行COPY操作，修复有问题的值，然后插入到目标表中，从文本转换。请参见：|2922307|2922308|How+to+ignore+errors+with+psql+\copy+meta-command|unordered-list-item|2922309|How+to+update+selected+rows+with+values+from+a+CSV+file+in+Postgres?|2922310|2922311|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/current/sql-copy.html|1|https://stackoverflow.com/questions/36634360/how-to-ignore-errors-with-psql-copy-meta-command/36635312#36635312|2|https://stackoverflow.com/questions/8910494/how-to-update-selected-rows-with-values-from-a-csv-file-in-postgres/8910810#8910810^0|0|0|0|A|1K|9|24|1|2A|A|H|29|0|0|H|6|B|0|0|0|1|3|0|0|0|1D|T|3|0|0|0|2E|G|J|11|A|1M|P|0|0|D|0|0|0|D|0|0|0|0|A|0|0|0|1O|0|0|0|0|2X|C|4|14|D|2F|C|2T|2|0|0|8|0|0|J|8|U|4|0|R|4|0|0|0|1D|1|0|0|1W|2|0|0^^$0|@$1|2|3|4|5|6|7|28|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|29|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|2A|8|@$I|2B|J|2C|K|L]|$I|2D|J|2E|K|L]|$I|2F|J|2G|K|L]|$I|2H|J|2I|K|L]|$I|2J|J|2K|K|M]]|9|@]|A|$]]|$1|N|3|O|5|6|7|2L|8|@$I|2M|J|2N|K|M]]|9|@$I|2O|J|2P|1|2Q]]|A|$]]|$1|P|3|-4|5|6|7|2R|8|@]|9|@]|A|$]]|$1|Q|3|R|5|S|7|2S|8|@$I|2T|J|2U|K|M]]|9|@]|A|$]]|$1|T|3|-4|5|6|7|2V|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|2W|8|@$I|2X|J|2Y|K|M]|$I|2Z|J|30|K|L]]|9|@]|A|$]]|$1|W|3|-4|5|6|7|31|8|@]|9|@]|A|$]]|$1|X|3|Y|5|6|7|32|8|@$I|33|J|34|K|M]|$I|35|J|36|K|L]|$I|37|J|38|K|L]|$I|39|J|3A|K|L]]|9|@]|A|$]]|$1|Z|3|10|5|6|7|3B|8|@$I|3C|J|3D|K|M]]|9|@]|A|$]]|$1|11|3|12|5|D|7|3E|8|@]|9|@]|A|$E|F]]|$1|13|3|14|5|6|7|3F|8|@$I|3G|J|3H|K|M]]|9|@]|A|$]]|$1|15|3|16|5|D|7|3I|8|@]|9|@]|A|$E|F]]|$1|17|3|-4|5|6|7|3J|8|@]|9|@]|A|$]]|$1|18|3|19|5|6|7|3K|8|@$I|3L|J|3M|K|M]]|9|@]|A|$]]|$1|1A|3|-4|5|6|7|3N|8|@]|9|@]|A|$]]|$1|1B|3|1C|5|S|7|3O|8|@$I|3P|J|3Q|K|M]]|9|@]|A|$]]|$1|1D|3|-4|5|6|7|3R|8|@]|9|@]|A|$]]|$1|1E|3|-4|5|6|7|3S|8|@]|9|@]|A|$]]|$1|1F|3|1G|5|6|7|3T|8|@$I|3U|J|3V|K|M]|$I|3W|J|3X|K|L]|$I|3Y|J|3Z|K|L]|$I|40|J|41|K|L]|$I|42|J|43|K|L]]|9|@]|A|$]]|$1|1H|3|1I|5|6|7|44|8|@$I|45|J|46|K|M]]|9|@]|A|$]]|$1|1J|3|1K|5|D|7|47|8|@]|9|@]|A|$E|F]]|$1|1L|3|1M|5|6|7|48|8|@$I|49|J|4A|K|L]|$I|4B|J|4C|K|L]]|9|@]|A|$]]|$1|1N|3|1O|5|6|7|4D|8|@$I|4E|J|4F|K|L]]|9|@]|A|$]]|$1|1P|3|-4|5|6|7|4G|8|@]|9|@]|A|$]]|$1|1Q|3|1R|5|1S|7|4H|8|@]|9|@$I|4I|J|4J|1|4K]]|A|$]]|$1|1T|3|1U|5|1S|7|4L|8|@]|9|@$I|4M|J|4N|1|4O]]|A|$]]|$1|1V|3|-4|5|6|7|4P|8|@]|9|@]|A|$]]|$1|1W|3|-4|5|6|7|4Q|8|@]|9|@]|A|$]]]|1X|$1Y|$5|1Z|20|21|A|$22|23]]|24|$5|1Z|20|21|A|$22|25]]|26|$5|1Z|20|21|A|$22|27]]]]

Your statement is suspicious:

<pre><code>COPY data FROM 'data.dat' USING DELIMITERS ',' CSV;
</code></pre>

<code>DELIMITERS</code> was used in versions before 7.3. It is still supported in order not to break old code, but don't use it any more. The proper keyword is <code>DELIMITER</code>. And you don't need to specify <code>,</code> at all as it is the default for <code>FORMAT CSV</code>. 
Also, I quote the <a href="https://www.postgresql.org/docs/current/sql-copy.html" rel="nofollow noreferrer">manual here</a>:

<blockquote>
 filename
 
 The absolute path name of the input or output file. Windows users might need to use an <code>E''</code> string and double any backslashes used in the path name.
</blockquote>

Bold emphasis mine. Replace <code>'data.dat'</code> with something like <code>'/path/to/data.dat'</code> on UNIX or <code>E'C:\\path\\to\\data.dat'</code> on Windows.

For versions 7.3+ use:

<pre><code>COPY data FROM '/path/to/data.dat' CSV
</code></pre>

For versions 9.0+ use:

<pre><code>COPY data FROM '/path/to/data.dat' (FORMAT CSV)
</code></pre>

<hr>

If you still get this error: 

<blockquote>
<pre><code>ERROR: invalid input syntax for type numeric:
CONTEXT: COPY data, line 13, column interval_2400:
</code></pre>
</blockquote>

Then, obviously, the source file does not match the structure of table <code>data</code>. Have a look at your source file, go to line 13 and see what value is there for column <code>interval_2400</code>. Chances are, it's not numeric. In particular, an <code>empty string</code> (<code>''</code>) is not allowed in columns of numeric type.

You can either fix the source file or adapt the table definition:

<pre><code>ALTER TABLE data ALTER COLUMN interval_2400 TYPE text;
</code></pre>

Or whatever type is more appropriate. Might be <code>interval</code>, judging from the name. (But <code>text</code> accepts almost any input values.)

Or, better yet, create a modified temporary file, <code>COPY</code> to it, fix offending values, then INSERT into the target table, casting from text. See:

<ul>
<li><a href="https://stackoverflow.com/questions/36634360/how-to-ignore-errors-with-psql-copy-meta-command/36635312#36635312">How to ignore errors with psql \copy meta-command</a></li>
<li><a href="https://stackoverflow.com/questions/8910494/how-to-update-selected-rows-with-values-from-a-csv-file-in-postgres/8910810#8910810">How to update selected rows with values from a CSV file in Postgres?</a></li>
</ul>

blocks|key|249336|text|任何带着小文件来这里的人:这是我找到的解决csv中分隔符数量不一致问题的最简单的方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|249337|249338|打开您的CSV。|ordered-list-item|249339|Ctrl+%2B+Shift+%2B8(选择所有数据)+|249340|Ctrl+%2Bh(打开查找替换)|249341|将查找框留空，以便查找长度为0的字符串。|249342|在替换为框中输入空格。|249343|249344|这将循环遍历整个CSV，并强制它在分隔符(，)中使用正确的列数，即使该列中没有数据。|249345|如果你对Excel没有意见，你也可以把它转换成宏，所以我的宏(Ctrl+%2B+g)可以一下子做到这一点。Creating+a+Macro|offset|length|249346|entityMap|0|LINK|mutability|MUTABLE|url|https://support.office.com/en-us/article/Quick-start-Create-a-macro-455512ef-3532-404e-b8dd-ea6589512c1b^0|0|0|0|0|0|0|0|0|0|1F|G|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|13|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|14|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|15|8|@]|9|@]|A|$]]|$1|H|3|I|5|E|7|16|8|@]|9|@]|A|$]]|$1|J|3|K|5|E|7|17|8|@]|9|@]|A|$]]|$1|L|3|M|5|E|7|18|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|19|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1B|8|@]|9|@$S|1C|T|1D|1|1E]]|A|$]]|$1|U|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|11]]]]

Anyone coming here with smaller files: here's the easiest fix I've found to this and inconsistent number of delimiters in a csv. 

<ol>
<li>Open your CSV. </li>
<li>Ctrl + Shift + 8 (selects all data) </li>
<li>Ctrl + h (opens find replace) </li>
<li>Leave the find box blank so its looking for strings of 0 length. </li>
<li>Enter a space in the replace with box.</li>
</ol>

This will loop through the whole CSV and force it to have the correct column count in delimiters ( , ) even if theres no data in that column. 

If you're alright with Excel you can turn this into a macro too, so my macro (Ctrl + g) does this in one go. <a href="https://support.office.com/en-us/article/Quick-start-Create-a-macro-455512ef-3532-404e-b8dd-ea6589512c1b" rel="nofollow" title="Creating a Macro">Creating a Macro</a>

blocks|key|3136478|text|这是PostgreSQL错误-+csv解析器忽略最后一个空项并抛出错误-+"PG::BadCopyFileFormat:+ERROR:+missing+data+for+column“。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3136479|我用了一个愚蠢的黑客：|3136480|如果最后一项为空，只需在字符串末尾添加一个分隔符：|3136481|1,2,3
1,2,,|code-block|syntax|javascript|3136482|这将添加行中缺少的最后一项以导入数据。|3136483|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|R|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

This is PostgreSQL bug - csv parser ignore last empty item and throw error - "PG::BadCopyFileFormat: ERROR: missing data for column".

i'm use a stupid hack:

If last item is empty, simple add a one delimiter to end of string:

<pre><code>1,2,3
1,2,,
</code></pre>

This add missed last item in row to import data.

blocks|key|2922405|text|一个额外的警告-检查错误的行号，并确保它不是CSV文件中的空行。这将导致postgres抛出关于缺失值的相同错误。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2922406|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

One additional caveat- Check the line number of the error and make sure it is not a blank row in the CSV file. That will cause postgres to throw the same error about missing values.

I'm trying to import a CSV file into PostgreSQL using COPY. It chokes when it hits a row where there are empty values, e.g. the second row below:

<blockquote>
 JAN-01-2001,1,2,3,4,5
 
 JAN-02-2001,6,7,,,
</blockquote>

I've tried this COPY statement, as well as variants using NULL and QUOTE and havent found anything that works.

<blockquote>
 COPY data FROM 'data.dat' 
 USING DELIMITERS ',' CSV;
</blockquote>

Any suggestions? The data file is in a massive 22GB flat file, so I'd like to avoid editing it directly.

PostgreSQL copy from CSV with missing data values

PostgreSQL

Windows

我正在尝试使用COPY将CSV文件导入到PostgreSQL中。当它命中有空值的行时，它会阻塞，例如下面的第二行： JAN-01-2001,1,2,3,4,501-02-2001，6，7，，，我已经尝试了这个COPY语句，以及使用NULL和引号的变体，但都没有找到任何有效的方法。使用分隔符'，‘CSV从'data.da...

问从缺少数据值的CSV进行PostgreSQL拷贝
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从缺少数据值的CSV进行PostgreSQL拷贝EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从缺少数据值的CSV进行PostgreSQL拷贝
EN