obdumper/obloader 导出导入带特殊字符的CSV文件测试

测试版本ob-loader-dumper-4.2.8.2

测试数据in.csv

ID,Name,Description
1,"Item 1, with a comma",This item has a "quote" and a newline
2,"Item 2","This item has a newline
in the description"
3,"Item 3","This one has a ""double quote"" and a comma, plus a newline
at the end"

• 第一行是CSV的表头。
• 第二行展示了如何处理逗号：Item 1, with a comma 被双引号包围，确保逗号被视为数据部分而不是字段分隔符。
• 第三行演示了换行的处理：This item has a newline\nin the description 中的\n在实际的CSV文件中会被视为一个换行符，显示时“in the description”会位于新行。
• 第四行展示了如何在文本中包含双引号：通过使用两个连续的双引号("")来转义内部的双引号，如This one has a ""double quote""。

注意：在实际的CSV文件中，换行符\n不会直接显示，而是作为控制字符影响文本的换行显示。

测试表

obclient -h192.168.56.36 -P2883 -usys@test#obdemo -pAAli88@@1688 -c

create table tcsv(ID number,Name varchar2(100),Description varchar2(100));

使用默认参数导入 --csv --table ‘TCSV’ 导入成功

下面每次测试前清空tcsv表
obloader -h 192.168.56.36 -P 2883 -u sys@test#obdemo -p AAli88@@1688  --no-sys -D SYS --csv --table 'TCSV' --file-regular-expression ".*\\.csv" -f /root/test

obclient [SYS]> select * from tcsv;
+------+----------------------+----------------------------------------------------------------------+
| ID   | NAME                 | DESCRIPTION                                                          |
+------+----------------------+----------------------------------------------------------------------+
|    1 | Item 1, with a comma | This item has a "quote" and a newline                                |
|    2 | Item 2               | This item has a newline
in the description                           |
|    3 | Item 3               | This one has a "double quote" and a comma, plus a newline
at the end |
+------+----------------------+----------------------------------------------------------------------+
3 rows in set (0.022 sec)

使用参数–column-delimiter ‘"’ 导入成功

obloader -h 192.168.56.36 -P 2883 -u sys@test#obdemo -p AAli88@@1688  --no-sys -D SYS --csv --table 'TCSV' --column-delimiter '"'  --file-regular-expression ".*\\.csv" -f /root/test


obclient [SYS]> select * from tcsv;
+------+----------------------+----------------------------------------------------------------------+
| ID   | NAME                 | DESCRIPTION                                                          |
+------+----------------------+----------------------------------------------------------------------+
|    1 | Item 1, with a comma | This item has a "quote" and a newline                                |
|    2 | Item 2               | This item has a newline
in the description                           |
|    3 | Item 3               | This one has a "double quote" and a comma, plus a newline
at the end |
+------+----------------------+----------------------------------------------------------------------+
3 rows in set (0.001 sec)

使用默认参数导出，导入成功

obdumper -h 192.168.56.36 -P 2883 -u sys@test#obdemo -p AAli88@@1688  --no-sys -D SYS --csv --table 'TCSV'   -f /root/test/out.csv

[root@ocp test]# cat out.csv
'ID','NAME','DESCRIPTION'
1,'Item 1, with a comma','This item has a "quote" and a newline'
2,'Item 2','This item has a newline\
in the description'
3,'Item 3','This one has a "double quote" and a comma, plus a newline\
at the end'


obloader -h 192.168.56.36 -P 2883 -u sys@test#obdemo -p AAli88@@1688  --no-sys -D SYS --csv --table 'TCSV'   -f /root/test/out.csv

obclient [SYS]> select * from tcsv;
+------+----------------------+----------------------------------------------------------------------+
| ID   | NAME                 | DESCRIPTION                                                          |
+------+----------------------+----------------------------------------------------------------------+
|    1 | Item 1, with a comma | This item has a "quote" and a newline                                |
|    2 | Item 2               | This item has a newline
in the description                           |
|    3 | Item 3               | This one has a "double quote" and a comma, plus a newline
at the end |

使用参数 --column-delimiter '"'导出，导入正常

obdumper -h 192.168.56.36 -P 2883 -u sys@test#obdemo -p AAli88@@1688  --no-sys -D SYS --csv --table 'TCSV' --column-delimiter '"' -f /root/test/out2.csv

"ID","NAME","DESCRIPTION"
1,"Item 1, with a comma","This item has a \"quote\" and a newline"
2,"Item 2","This item has a newline\
in the description"
3,"Item 3","This one has a \"double quote\" and a comma, plus a newline\
at the end"

obloader -h 192.168.56.36 -P 2883 -u sys@test#obdemo -p AAli88@@1688  --no-sys -D SYS --csv --table 'TCSV' --column-delimiter '"'  -f /root/test/out2.csv
obclient [SYS]> select * from tcsv;
+------+----------------------+----------------------------------------------------------------------+
| ID   | NAME                 | DESCRIPTION                                                          |
+------+----------------------+----------------------------------------------------------------------+
|    1 | Item 1, with a comma | This item has a "quote" and a newline                                |
|    2 | Item 2               | This item has a newline
in the description                           |
|    3 | Item 3               | This one has a "double quote" and a comma, plus a newline
at the end |
+------+----------------------+----------------------------------------------------------------------+
3 rows in set (0.001 sec)

增加复杂度,增加带单引号的字符串,使用–column-delimiter ‘"’ 导出，导入成功

insert into tcsv values  (4,'Item 4','this one has a ''single quote'' description');
obclient [SYS]> select * from tcsv;
+------+----------------------+----------------------------------------------------------------------+
| ID   | NAME                 | DESCRIPTION                                                          |
+------+----------------------+----------------------------------------------------------------------+
|    1 | Item 1, with a comma | This item has a "quote" and a newline                                |
|    2 | Item 2               | This item has a newline
in the description                           |
|    3 | Item 3               | This one has a "double quote" and a comma, plus a newline
at the end |
|    4 | Item 4               | this one has a 'single quote' description                            |
+------+----------------------+----------------------------------------------------------------------+
4 rows in set (0.001 sec)

obdumper -h 192.168.56.36 -P 2883 -u sys@test#obdemo -p AAli88@@1688  --no-sys -D SYS --csv --table 'TCSV' --column-delimiter '"' -f /root/test/out3.csv
obloader -h 192.168.56.36 -P 2883 -u sys@test#obdemo -p AAli88@@1688  --no-sys -D SYS --csv --table 'TCSV' --column-delimiter '"'  -f /root/test/out3.csv

[root@ocp test]# cat out3.csv
"ID","NAME","DESCRIPTION"
1,"Item 1, with a comma","This item has a \"quote\" and a newline"
2,"Item 2","This item has a newline\
in the description"
3,"Item 3","This one has a \"double quote\" and a comma, plus a newline\
at the end"
4,"Item 4","this one has a 'single quote' description"
[root@ocp test]#


obclient [SYS]> select * from tcsv;
+------+----------------------+----------------------------------------------------------------------+
| ID   | NAME                 | DESCRIPTION                                                          |
+------+----------------------+----------------------------------------------------------------------+
|    1 | Item 1, with a comma | This item has a "quote" and a newline                                |
|    2 | Item 2               | This item has a newline
in the description                           |
|    3 | Item 3               | This one has a "double quote" and a comma, plus a newline
at the end |
|    4 | Item 4               | this one has a 'single quote' description                            |
+------+----------------------+----------------------------------------------------------------------+
4 rows in set (0.002 sec)