seed varchar cols with apostrophe's fail

[dataders]

This csv fails to materialize because of line 63 where last_name=O'Gready with the following error:

Expected but did not find a closing quotation mark after the character string '.

Here's the query. I escaped the inner ' because it also broke the GH syntax highlighting 😝 I'm sure the other adapters have a way to escape the quote delimiters within strings.

insert into "dbtsynapseci"."dbt_external_tables_integration_tests_synapse"."data_people" (
    id, first_name, last_name, email, ip_address, created_at, is_active
)
SELECT '60','Anthia','Tolland','atolland1n@hibu.com','124.60.13.101','2016-02-06 14:38:37','True' UNION ALL
SELECT '61','Annamarie','Pipworth','apipworth1o@ftc.gov','53.219.191.107','2017-06-13 08:29:04','True' UNION ALL
SELECT '62','Price','O\'Gready','pogready1p@theatlantic.com','131.188.180.57','2016-09-28 08:44:38','' UNION ALL
SELECT '63','Sergei','Cicero','scicero1q@telegraph.co.uk','100.97.16.84','2017-10-02 15:58:45','' UNION ALL
SELECT '64','Dolorita','Lilion','dlilion1r@vimeo.com','150.43.252.51','2017-09-06 12:39:46','True' UNION ALL
SELECT '65','Perrine','Peetermann','ppeetermann1s@fema.gov','93.27.202.229','2017-07-08 08:49:37','' UNION ALL
SELECT '66','Frieda','Gemelli','fgemelli1t@altervista.org','20.21.177.102','2016-04-18 05:58:59','' UNION ALL
SELECT '67','Webster','Tully','wtully1u@nba.com','61.55.62.136','2016-02-18 11:01:23','True' UNION ALL
SELECT '68','Clara','Dadd','cdadd1v@rakuten.co.jp','67.84.203.36','2017-06-10 22:20:50','' UNION ALL
SELECT '69','Gardener','Clarkin','gclarkin1w@bbc.co.uk','211.175.17.92','2017-11-27 23:33:42','True'

[NandanHegde15]

Hi @swanderz : We need to enclose the quote with another quote character ''

The below code should work : insert into "dbtsynapseci"."dbt_external_tables_integration_tests_synapse"."data_people" ( id, first_name, last_name, email, ip_address, created_at, is_active ) SELECT '60','Anthia','Tolland','atolland1n@hibu.com','124.60.13.101','2016-02-06 14:38:37','True' UNION ALL SELECT '61','Annamarie','Pipworth','apipworth1o@ftc.gov','53.219.191.107','2017-06-13 08:29:04','True' UNION ALL SELECT '62','Price','O\''Gready','pogready1p@theatlantic.com','131.188.180.57','2016-09-28 08:44:38','' UNION ALL SELECT '63','Sergei','Cicero','scicero1q@telegraph.co.uk','100.97.16.84','2017-10-02 15:58:45',''

Solution:

Replace the below code :

'{{row[column].replace("'","''") if row[column]}}'

[NandanHegde15]

Resolved with the PR: Update seed.sql #31