How to use MySQL (csv, text) to import mssql

The first step is to import the csv file to mysql. The import function of the client provided by mssql is not used. Because this function is too pitfall, if you try N methods, an error is still reported. Therefore, convert it to mysql for intermediate steps.

Import csv/text to MySQL

A batch of large csv data (not csv, But tab-separated fields) is imported into the mssql database.

First, we use awk in cygwin to pre-process the data and only filter the rows with the correct number of characters. Add a column name to the first line so that the import tool can automatically perform column correspondence.

Then, calculate the maximum length of each field. Because there are too many (63) fields, calculate them to construct the create table statement. Varchar (n) is used for fields with a maximum length of less than 255, and longtext is used for fields above 255.

# Refer to the following statements

$ Awk-F "\ t" 'NF = 63 {print $0} 'all.csv> all_63f.txt
$ Head-1 origin_text_file.txt> h.txt
$ Dos2unix h.txt # note that BOM is not recommended; otherwise, save it as needed to avoid trouble
$ Cat h.txt all_63f.txt> a63_with_head.txt

# The maximum length of computing characters is not complicated, but it is not easy to read because it is written in a row.
$ Awk-F "\ t" 'In in {for (I = 1; I <= 63; I ++) {xcount [I] = 0 }}{ for (I = 1; I <= 63; I ++) {if (xcount [I] <length ($ I )) {xcount [I] = length ($ I) }}end {for (I = 1; I <= 63; I ++) {print I, xcount [I]} 'all_63f.txt use Navicat for MySQL to import csv. Navcate can be used for trial and has sufficient functions. If it is frequently used, you are advised to purchase authorization.

MySQL import mssql

The following example shows mssql 2012 in win2008 x64.

Install mysql's. net driver http://dev.mysql.com/downloads/connector/

Create table: create a table, consistent with the mysql structure.

Use the built-in import function of mssql to import Data. Select. Net Framework Data Provider for MySQL as the import Data source, and enter the required host name, logon name, and password, as shown below:

You cannot select the source data table. You must write select statements, such as select f1, f2, f3... From 'DB'. 'Table' where 1

There should be no further difficulties.