Field Terminator 와 Line Terminator 의 첫문자가 같을 경우 잘 동작하는지가 궁금.
어느 한쪽이 다른쪽의 부분집합이 되면 당연히 안될 것 같구.
▼ Oracle
$ cat x.ctl
LOAD DATA
INFILE 'x.dat' "STR 'AB'"
INTO TABLE t1
FIELDS TERMINATED BY 'ACD'
(
c1
,c2
)
$ cat x.dat
11ACD111AB22ACD222AB
$ sqlldr userid=test/test control=x.ctl
SQL*Loader: Release 12.1.0.2.0 - Production on Wed Jan 10 15:00:32 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 2
Table T1:
2 Rows successfully loaded.
Check the log file:
x.log
for more information about the load
$ sqlplus -S test/test
select * from t1;
C1
----------
C2
-----------
11
111
22
222
잘됨!
▼ mysql
rel@playstation:~$ mysql -uroot -pmanager
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 27
Server version: 10.2.11-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
MariaDB [(none)]> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [test]> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | kim |
| 2 | heni |
| 3 | AB |
| 4 | AXY |
+------+------+
4 rows in set (0.00 sec)
MariaDB [test]> SELECT * FROM (
-> (
-> SELECT
-> 'ID' AS 'ID',
-> 'NAME' AS 'NAME'
-> ) UNION (
-> SELECT
-> id,
-> name
-> FROM t1
-> )
-> ) AS t1
-> INTO OUTFILE 'test_t1.csv'
-> FIELDS TERMINATED BY 'AB'
-> LINES TERMINATED BY 'AXY';
Query OK, 5 rows affected (0.00 sec)
MariaDB [test]> LOAD DATA INFILE 'test_t1.csv'
-> INTO TABLE t2
-> FIELDS TERMINATED BY 'AB'
-> LINES TERMINATED BY 'AXY' (id, name);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
MariaDB [test]> select * from t2;
+------+------+
| id | name |
+------+------+
| ID | NAME |
| 1 | kim |
| 2 | heni |
| 3 | AB |
| 4 | AXY |
+------+------+
5 rows in set (0.00 sec)
컬럼명도 row로 취급되는데, 뭐 방법이 있겠지. 일단 궁금한것은 해결(데이터 문제없음)
▼ mssql
1> select * from t1;
2> go
id name
---------- ----------
1 kim
2 heni
3 AB
4 AXY
(4 rows affected)
[root@localhost ~]# bcp t1 out ~/t1.csv -S localhost -U sa -P password -d TESTDB -c -t 'AB' -r 'AXY'
Starting copy...
4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (4000.0 rows per sec
[root@localhost ~]# bcp t2 in ~/t1.csv -S localhost -U sa -P password -d TESTDB -c -t 'AB' -r 'AXY'
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unexpected EOF encountered in BCP data-file
4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 6 Average : (666.7 rows per sec.)
[root@localhost ~]#
[root@localhost ~]# sqlcmd -U sa
Password:
1> use TESTDB
2> go
데이터베이스 컨텍스트가 'TESTDB'(으)로 변경되었습니다.
1> select * from t1;
2> go
id name
---------- ----------
1 kim
2 heni
3 AB
4 AXY
(4 rows affected)
1> select * from t2
2> go
id name
---------- ----------
1 kim
2 heni
3 AB
4 NULL
(4 rows affected)
[root@localhost ~]# cat t1.csv
1ABkimAXY2ABheniAXY3ABABAXY4ABAXYAXY
문제음슴.