Tip if you have trouble importing concept.csv from Athena into Postgres

Developers
1

I’m currently in the process of setting up Atlas, WebAPI, and Achilles in order to start testing our CDM. I have been struggling with getting the concept table populated today and got stuck on this error for way too long.

ERROR: value too long for type character varying(255)
CONTEXT: COPY concept, line 16548, column concept_name: “Rollabout chair, any and all types with casters 5 or greater Device HCPCS HCPCS S E1031 19900101 209…”

There was a double quote here on this line, but even after removing it it still kept trying to read the entire line into concept_name.

Anyways, this psql command seems to have finally worked.

\copy omop.concept (concept_id, concept_name, domain_id, vocabulary_id, concept_class_id, standard_concept, concept_code, valid_start_date, valid_end_date, invalid_reason) FROM <your-path-to-concept.csv> WITH (FORMAT csv, DELIMITER E’\t’, HEADER, QUOTE E’\b’);

1 Like
2

Edit: After realizing I have many more files to import. There was a YouTube video showing how to use DBeaver to import the CSV files which will likely be easier and faster. I did attempt this way with concept.csv initially, but it was stopped at 12,000 during the insert/transfer. I guess it likely that it was this line since it is on line 12,270 in concept.csv.

3

I’ll plug Broadsea here, if you’re able to use Docker. Broadsea has a profile for importing Athena files into a Postgres db instance:

1 Like