CODEWITHSUNDEEP
sqlpostgresqlamazon-rds
Tim
Tim

Reputation: 583

Import S3 CSV to RDS Postgres

Trying to import a csv from S3 into RDS postgres:

The data in some columns contains ",". for example the address column.

The data is enclosed in "".

Here is our select:

SELECT aws_s3.table_import_from_s3(
   'Change_National_IDs',
   '',
   'DELIMITER '',''',
   aws_commons.create_s3_uri('data-migration-s3-bucket', 'Change_IDs_WqlResults_20xxxxx4_xxxxxx.csv', 'us-west-2')
);

Tried many different combinations. How can we handle the "," in the data?

Current error:

ERROR: extra data after last expected column

Upvotes: 0

Views: 422

Answers (1)

Sergio N
Sergio N

Reputation: 306

you should change your column delimiter to another different than comma. Something like | and then use code like this.

SELECT aws_s3.table_import_from_s3(
   'Change_National_IDs',
   '',
   'DELIMITER ''|''',
   aws_commons.create_s3_uri('data-migration-s3-bucket', 
'Change_IDs_WqlResults_20xxxxx4_xxxxxx.csv', 'us-west-2')
);

More Information https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PostgreSQL.S3Import.html#USER_PostgreSQL.S3Import.FileFormats.CustomDelimiter

Upvotes: 1

Related Questions