无法从控制台将.csv导入Cloud SQL [英] Cannot import .csv into Cloud SQL from console

问题描述

我尝试了多种方法将.csv文件导入Cloud SQL（PostgreSQL），但是没有运气。

psql -f import.sql无法正常工作。它说我必须是超级用户才能导入，但是\copy有效。

所以我尝试了\copy，但失败了。我得到：

  postgres => WITH从'H：\data\data.csv'中复制ppw_raw（FORMAT csv）; 
 
服务器意外关闭了连接
这可能意味着服务器在处理请求之前或期间异常终止了
。 
  

所以最后我尝试从 https://console.cloud.google.com/path-to-sql-instance

我将.csv上载到存储桶，然后单击所有按钮以将.csv（在存储桶中）导入到想要的数据库/表以及要导入为的用户（创建表，该表拥有我选择的数据库），

，我会弹出消息：

 导入失败：导入CSV错误：错误：关系< my-table-name> 
不存在。 
  

但确实存在该表。创建表命令成功，当我描述该表时，该表存在。

我现在尝试了三种不同的方式来导入.csv，我落后于

任何地方的任何人都可以通过任何方式成功地将.csv文件导入Google Cloud SQL / PostgreSQL吗？

解决方案

我尝试了其他方法将自定义的 CSV 文件导入CloudSQL（PostgreSQL）数据库实例中。您似乎正在尝试将数据导入数据库中不存在的表中。当我尝试导入不存在的表时，我遇到了同样的错误。

要导入CSV文件，请确保：

• CSV文件具有与数据库表相同的列数。


• 文件中的数据经过适当的结构以表示CSV文件。


• 在将CSV文件导入CloudSQL之前，首先从表中导出几行并打开文件以查看数据如何被代表。将数据与要导入的CSV文件进行比较，并确保以相同的方式表示数据。导出表单控制台时，选择将CSV导出到的存储桶文件夹，选择CSV选项，然后在 SQL查询中添加以下命令 SELECT * FROM产品限制5； 。它只会从数据库中导出前5条记录，因此不会花费太多时间。

CloudSQL（PostgreSQL）数据库中的文件：

首先确保该表存在于数据库中。连接到您的CloudSQl（PostgreSQL）实例。运行命令 SELECT * FROM pg_catalog.pg_tables; 列出所有表。如果在列表中找到了表，则在将数据导入数据库时​​，对表使用完全相同的名称。

1. 将CSV文件上传到存储桶文件夹中




2. 在SQL页面中找到CloudSQL（PostgreSQL）实例，然后单击实例的名称。




3. 在实例详细信息页面中，单击导入。




4. 在 Cloud Storage文件中从存储桶中选择CSV文件。




5. 以导入格式，选择 CSV




6. 在表中输入要在其中导入数据的表的名称。 / p>




7. 单击导入，数据将被导入。

我尝试了上述方法，并且成功将自定义CSV文件上传到CloudSQL（PostgreSQL）。

I have tried multiple ways to import a .csv file into Cloud SQL (PostgreSQL), with no luck.

psql -f import.sql does not work. It says that I must be a superuser to import, but that \copy works.

So I tried \copy , and that fails. I get:

postgres=> \copy ppw_raw FROM 'H:\data\data.csv' WITH (FORMAT csv);

server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

So finally I try from https://console.cloud.google.com/path-to-sql-instance

I upload the .csv to a storage bucket, then click all the buttons to import the .csv (in storage bucket), to the DB/Table I want, and the user I want to import as (the user that created the table, that owns the database I am selecting),

and I get a popup message:

Import failed:  Import CSV error: ERROR: relation "<my-table-name>"
does not exist.

but indeed the table exists. The create table command was successful, and when I describe the table, it exists.

I have tried three different ways now to import a .csv, I am behind on a very demanding deadline for a report.

Has anyone anywhere been able to successfully import a .csv file into Google Cloud SQL / PostgreSQL by any means?

解决方案

I tried different methods to import a custom CSV file into the CloudSQL (PostgreSQL) database instance. It looks like you are trying to import data to a table that doesn't exists in your database. I had the same error, when I was trying to import to table that didn't exist.

To import CSV file, make sure that:

• The CSV file have the same amount of columns as the table in the database.
• The data in the file are properly structured to represent a CSV file.
• Before importing a CSV file to CloudSQL, first export couple of lines from the table and open the file to see how the data is represented. Compare the data to the CSV file you want to import and make sure that the data is represented the same way. When exporting form console, choose the bucket folder where the CSV will be exported to, choose the CSV option and in SQL query add the following command SELECT * FROM products LIMIT 5;. It will only export the first 5 records from the database, therefore it won't take much time.

To import the file in the CloudSQL (PostgreSQL) database:

First make sure that the table exists in the database. Connect to your CloudSQl (PostgreSQL) instance. Run the command SELECT * FROM pg_catalog.pg_tables; to list all the tables. If you have found your table in the list, use the exact same name for Table when importing the data to the database.

1. Upload the CSV file in a bucket folder

2. Find the CloudSQL (PostgreSQL) instance in SQL page and click on the instance's name.

3. In the Instance details page click on Import.

4. In Cloud Storage file select the CSV file from bucket.

5. In Format of import, select CSV

6. In table write the name of the table you want to import the data in.

7. Click on Import and the data will be imported.

I have tried the methods above myself and I uploaded successfully a custom CSV file to CloudSQL (PostgreSQL).

这篇关于无法从控制台将.csv导入Cloud SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！