I am attempting to convert some SQL Server data into mySQL, but keep running into hurdle after hurdle.
I assumed the easiest way would be to export the SQL Server data as CSV and import it that way (little did I know it wasn't that easy...!). Once I figured out how to get it to add quotes to the CSV, I attempted an import into mySQL. Now, for some reason the data was all imported WITH the quotes. Here is my mySQL query:
LOAD DATA INFILE 'file.csv' INTO TABLE test
FIELDS ENCLOSED BY '"'
TERMINATED BY ","
LINES TERMINATED BY "\n"
This imports, but it seems to ignore the quotes, and when commas are present in the data it completely screws up the row. The data is also imported including the quotes, see this data directly from the table:
"1" "1" "0" "{08CA6F70-735D-46ED-8EAB-C17A8BED1FCD}" "Reporting_1" "60" "Reporting" "ABC" "2008-04-21 19:25:28.013000000" "False" "True" "False" "True" "3" "164" "2033" "565077" "7929083" "334980" "2013-01-11 15:35:45.970000000" "False" "" "0" "False" "" "0" "0" "" "" "False" "False" "True" "True" "" "" "False"
I need to do some post processing with PHP on this data, so I assumed I could just strip the quotes out with my code. The quotes were not trimmed using the trim() function (it just wouldn't work), so I str_replaced the quotes and this did seem to work, however:
I am remapping the data and inserting it into another mySQL table (with different column names). When inserting the processed data above, not all of it makes it across with the DB query. Take this query for instance:
INSERT INTO tesxf.xf_node (`node_id`,`parent_node_id`,`title`,`description`,`node_name`,`node_type_id`,`display_in_list`)
VALUES
('2123','2281','Container Name','','38064','Forum','1')
The node_id and title etc make it into the new db, but the parent_node_id never does. The value is always inserted as 0. When I copy the query above and run it manually, it inserts the data correctly. Furthermore, if I run the data through mysql_real_escape_string, it comes out like this (Only converted two fields here):
INSERT INTO tesxf.xf_node
(`node_id`,`parent_node_id`,`title`,`description`,`node_name`,`node_type_id`,`display_in_list`)
VALUES
('673','\0\06\08\08\0\0','\0\0S\0t\0o\0r\0y\0 \0W\0r\0i\0t\0i\0n\0g\0 \0-\0 \0F\0o\0r\0u\0m\0\0','','240576','Forum','1')
I have never seen this before. It makes me think that perhaps the data received from the imported table is in some odd format with some hidden characters I can't see? How can I strip these out? Could this be the reason the data didn't import properly in the first place?
I am running SQL server 2012 I think (not too familiar with it). My mySQL instance is running on OSX 10.6 and is usually fine (I have done similar imports using postgres and oracle etc).
Mysql is : mysql Ver 14.12 Distrib 5.0.92, for apple-darwin10.0 (i386) using EditLine wrapper PHP is: PHP 5.3.26 (cli) (built: Jul 7 2013 18:30:38)
Losing my mind here... I think my next attempt will be piping constructed queries into a text file and then running that manually... I hope there is something simple that I have missed.
CSV files were transferred over FTP in binary mode, and I applied :set ff=unix to them using vim (as I read on here that could be an issue).
I have also attempted to install the SQL Server/freetds wrapper for PHP on a this server + 1 other, but don't get me started on that!!
