Webmatrix csv导入到SQL Server Compact [英] Webmatrix csv import to SQL Server Compact

问题描述

我刚切换到Webmatrix，因为我使用的服务器停止支持MySQL / PHP。现在我使用SQL Server Compact将我的旧php mysql站点转换为Webmatrix。但我打了一个小钩。我使用脚本直接导入csv文件到我的数据库，在php / mysql这不是那么难，但我不能让它工作在Webmatrix和SQL Server Compact。

I just switched to Webmatrix because the server I was using stopped supporting MySQL / PHP. Now I'm converting my old php mysql site to Webmatrix with SQL Server Compact. But I have hit a little snag. I'm using a script to directly import csv files into my database, in php/mysql this was not that hard to do but I can't get it to work in Webmatrix and SQL Server Compact. Anyone having ideas on why it does not work?

mysql_query("TRUNCATE TABLE `tmp_st_age`");
   $age = 'load data local infile "../csv/tmp_st_age.csv"
        into table `tmp_st_age` 
        fields terminated by ";"
        enclosed by "\""
        LINES TERMINATED BY "\r\n"
        IGNORE 1 LINES';

mysql_query($age) or die(myqsl_error());
echo "Done! ".mysql_affected_rows()." rows inserted into tmp_st_age.<br>";

在Webmatrix，但我似乎不能得到它是正确的，这是只是一个mysql功能吗？如果是，有什么好的解决方法，意味着不使用第三方程序，基于Web的输入是什么寻找。

Above code is something I'm trying to implement in Webmatrix but I can't seem to get it correct. Is it so that this is a mysql feature only? If so, is there any good workaround that means not using a third party program, a web based input is what I'm looking for.

推荐答案

如果您使用SQL Server而不是SQL Compact，可以使用 BULK INSERT 这是一个非常类似的方法你MySQL中的一个你解释的问题。不幸的是， t支持，所以你留下读取csv并单独插入每一行：

If you were using SQL Server instead of SQL Compact, you could use BULK INSERT which is a very similar approach to the MySQL one you illustrated in your question. Unfortunately SQL Compact doesn't support that so you are left with reading the csv and inserting each row individually. Something along the lines of:

var db = Database.Open("your_db");
var data = File.ReadAllLines(path_to_csv_file);
foreach(var row in data){
    var columns = row.Split(new []{';'});
    var sql = "INSERT INTO MyTable (f1, f2, f3, etc) VALUES (@0, @1, @2, etc)";
    db.Execute(sql, item[0], item[1], item[2], etc);
}

这篇关于Webmatrix csv导入到SQL Server Compact的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！