Here is something a MySQL Daredevil is more than willing to try.
I just did the following in MySQL 5.6.14 right on my Laptop
C:\Windows\system32>mysql -Dtest
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.14 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL://localhost/redwards/3306 test :: create table dt (a INT NOT NULL,b CHAR(10) NOT NULL) ENGINE=CSV;
Query OK, 0 rows affected (0.08 sec)
MySQL://localhost/redwards/3306 test :: insert into dt values (1,'rolando'),(2,'pamela'),(3,'dominique'),(4,'diamond');
Query OK, 4 rows affected (0.11 sec)
Records: 4 Duplicates: 0 Warnings: 0
MySQL://localhost/redwards/3306 information_schema :: flush tables;
Query OK, 0 rows affected (0.31 sec)
MySQL://localhost/redwards/3306 test :: exit
Bye
Now, going into the OS:
C:\Windows\system32>cd \MySQL_5.6.14\data\test
C:\MySQL_5.6.14\data\test>dir
Volume in drive C has no label.
Volume Serial Number is A06D-D0B3
Directory of C:\MySQL_5.6.14\data\test
12/18/2013 04:18 PM <DIR> .
12/18/2013 04:18 PM <DIR> ..
10/24/2013 10:20 AM 65 db.opt
12/18/2013 04:20 PM 35 dt.CSM
12/18/2013 04:19 PM 49 dt.CSV
12/18/2013 04:18 PM 8,578 dt.frm
10/29/2013 06:06 PM 0 exp1#P#part00.MYD
10/29/2013 06:06 PM 1,024 exp1#P#part00.MYI
10/29/2013 06:06 PM 0 exp1#P#part01.MYD
10/29/2013 06:06 PM 1,024 exp1#P#part01.MYI
10/29/2013 06:06 PM 0 exp1#P#part02.MYD
10/29/2013 06:06 PM 1,024 exp1#P#part02.MYI
10/29/2013 06:06 PM 336 exp1#P#part03.MYD
11/15/2013 05:44 PM 2,048 exp1#P#part03.MYI
10/29/2013 06:06 PM 0 exp1#P#part99.MYD
10/29/2013 06:06 PM 1,024 exp1#P#part99.MYI
10/29/2013 06:06 PM 8,642 exp1.frm
10/29/2013 06:06 PM 60 exp1.par
10/24/2013 10:30 AM 8,682 menus.frm
10/24/2013 10:30 AM 68 menus.MYD
10/28/2013 03:00 PM 1,024 menus.MYI
10/24/2013 10:30 AM 8,584 pages.frm
10/24/2013 10:30 AM 80 pages.MYD
10/28/2013 03:00 PM 2,048 pages.MYI
22 File(s) 44,395 bytes
2 Dir(s) 1,869,894,590,464 bytes free
C:\MySQL_5.6.14\data\test>type dt.CSV
1,"rolando"
2,"pamela"
3,"dominique"
4,"diamond"
C:\MySQL_5.6.14\data\test>
As show in the above commands in mysql and in the Windows command line, the CSV file that mysql can handle has the following two characteristics:
- fields delimited by commas
- character fields enclosed in double quotes
Since you created the test table as CSV, here is what you should do:
- Make sure the existing CSV file has the two characteristics for mysql to work with it
- Go to the folder
test
in your datadir.
- Rename test.CSV to test1.CSV
- Copy your existing CSV file to test.CSV
- Login to mysql and run
FLUSH TABLES;
Afterwards, the table shold be fully accessiable.
CAVEAT (Windows)
If Windows has the CSV table locked, run these commands
- Make sure the existing CSV file has the two characteristics for mysql to work with it
- Go to the folder
test
in your datadir.
- Run
net stop mysql
- Rename test.CSV to test1.CSV
- Copy your existing CSV file to test.CSV
- Run
net start mysql
Give it a Try !!!
What you want is nearly impossible, no one can know if you want to import '1' as a characters string, n decimal integer, a base-2 integer, an hex integer, a float, a blob, a "boolean", ...
You can try heuristic programs, or import them all as strings and run an analysis program like SELECT... ANALYZE TABLE();
. It would be easier if the csv had the datatypes as an information.
Regarding programs that create the table (with chars) based on the list of columns, that is easier to find (GUI) and program.
Here it is one in bash as an example.
Best Answer
To remove the output at the bottom, omit the
-p
parameter.Result:
According to the documentation on sqlcmd
You can use the
-h
parameter to control the headersBut you can only remove or change the frequency of the headers.
Result:
The not so clean solution that was also used here could be to print or select the column headers before running the select.
Result: