How to quote/escape single quote character in the name of the file?
"C:\Program Files\MonetDB\MonetDB5\mclient.bat" --port=50000 --user=monetdb
Hello, I am trying to load data from a text file that has single quote character in its name into a table: password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP2) Database: MonetDB v11.21.13 (Jul2015-SP2), 'soft10_20160318_172621' Type \q to quit, \? for a list of available commands auto commit mode: on sql> sql> sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin's_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; more> more> Everything works fine if there is no single quote character in the name of the file: sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachins_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; 120 affected rows (12.3s) I was trying to precede the single quote character by a \ (backslash) or to double it, but it still did not work: sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; could not open file 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv': No such file or directory sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin''s_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; could not open file 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv': No such file or directory
dir c:\tmp\sa*.csv Volume in drive C is S3A9506D003 Volume Serial Number is 00B9-C775
Directory of c:\tmp 2016-03-18 17:12 144,202 sachin's_data_with_requested_column_03_03_2016.csv 2016-03-18 17:12 144,202 sachins_data_with_requested_column_03_03_2016.csv 2 File(s) 288,404 bytes 0 Dir(s) 377,532,665,856 bytes free I am using the following version of MonetDB: # MonetDB 5 server v11.21.13 "Jul2015-SP2" # Serving database 'soft10_20160318_172621', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 3.799 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # Start processing logs sql/sql_logs version 52200 # Start reading the write-ahead log 'sql_logs\sql\log.11' # Finished reading the write-ahead log 'sql_logs\sql\log.11' # Finished processing logs sql/sql_logs # MonetDB/SQL module loaded
Thank you, -- Gennadiy
Looks like a bug. Can you please report this on http://bugs.monetdb.org? Thanks. On 03/22/2016 07:25 PM, Gennadiy wrote:
Hello,
I am trying to load data from a text file that has single quote character in its name into a table:
"C:\Program Files\MonetDB\MonetDB5\mclient.bat" --port=50000 --user=monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP2) Database: MonetDB v11.21.13 (Jul2015-SP2), 'soft10_20160318_172621' Type \q to quit, \? for a list of available commands auto commit mode: on sql> sql> sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin's_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; more> more>
Everything works fine if there is no single quote character in the name of the file:
sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachins_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; 120 affected rows (12.3s)
I was trying to precede the single quote character by a \ (backslash) or to double it, but it still did not work:
sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; could not open file 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv': No such file or directory
sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin''s_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; could not open file 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv': No such file or directory
dir c:\tmp\sa*.csv Volume in drive C is S3A9506D003 Volume Serial Number is 00B9-C775
Directory of c:\tmp
2016-03-18 17:12 144,202 sachin's_data_with_requested_column_03_03_2016.csv 2016-03-18 17:12 144,202 sachins_data_with_requested_column_03_03_2016.csv 2 File(s) 288,404 bytes 0 Dir(s) 377,532,665,856 bytes free
I am using the following version of MonetDB:
# MonetDB 5 server v11.21.13 "Jul2015-SP2" # Serving database 'soft10_20160318_172621', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 3.799 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # Start processing logs sql/sql_logs version 52200 # Start reading the write-ahead log 'sql_logs\sql\log.11' # Finished reading the write-ahead log 'sql_logs\sql\log.11' # Finished processing logs sql/sql_logs # MonetDB/SQL module loaded
Thank you,
-- Sjoerd Mullender
It seems to me that you should escape this quote, since you use the same single quotes to surround your file path, hence the sql interpreter thinks you are still inside a string when you hit enter. What happens if you try this: COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; On 22-03-16 19:25, Gennadiy wrote:
Hello,
I am trying to load data from a text file that has single quote character in its name into a table:
"C:\Program Files\MonetDB\MonetDB5\mclient.bat" --port=50000 --user=monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP2) Database: MonetDB v11.21.13 (Jul2015-SP2), 'soft10_20160318_172621' Type \q to quit, \? for a list of available commands auto commit mode: on sql> sql> sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin's_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; more> more>
Everything works fine if there is no single quote character in the name of the file:
sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachins_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; 120 affected rows (12.3s)
I was trying to precede the single quote character by a \ (backslash) or to double it, but it still did not work:
sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; could not open file 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv': No such file or directory
sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin''s_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; could not open file 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv': No such file or directory
dir c:\tmp\sa*.csv Volume in drive C is S3A9506D003 Volume Serial Number is 00B9-C775
Directory of c:\tmp
2016-03-18 17:12 144,202 sachin's_data_with_requested_column_03_03_2016.csv 2016-03-18 17:12 144,202 sachins_data_with_requested_column_03_03_2016.csv 2 File(s) 288,404 bytes 0 Dir(s) 377,532,665,856 bytes free
I am using the following version of MonetDB:
# MonetDB 5 server v11.21.13 "Jul2015-SP2" # Serving database 'soft10_20160318_172621', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 3.799 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # Start processing logs sql/sql_logs version 52200 # Start reading the write-ahead log 'sql_logs\sql\log.11' # Finished reading the write-ahead log 'sql_logs\sql\log.11' # Finished processing logs sql/sql_logs # MonetDB/SQL module loaded Thank you,
I already tried to precede the single quote character by a \ (backslash):
sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED;
could not open file 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv': No such file or directory
sql>
Thank you,
--
Gennadiy
On Tue, 22 Mar 2016 20:12:12 +0100
Robin Cijvat
It seems to me that you should escape this quote, since you use the same single quotes to surround your file path, hence the sql interpreter thinks you are still inside a string when you hit enter. What happens if you try this:
COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED;
Too bad. Indeed, this should be files as a bug then. On 22-03-16 20:33, Gennadiy wrote:
I already tried to precede the single quote character by a \ (backslash):
sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; could not open file 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv': No such file or directory sql>
Thank you,
Done: https://www.monetdb.org/bugzilla/show_bug.cgi?id=3965
--
Gennadiy
On Wed, 23 Mar 2016 06:57:46 +0100
Robin Cijvat
Too bad. Indeed, this should be files as a bug then.
On 22-03-16 20:33, Gennadiy wrote:
I already tried to precede the single quote character by a \ (backslash):
sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; could not open file 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv': No such file or directory sql>
Thank you,
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (3)
-
Gennadiy -
Robin Cijvat -
Sjoerd Mullender