.txt to SQL (MS SQL Server) [modified]
-
I have to load .txt data to sql table. Im doing this:
string path = "E:\\fich.txt";
string query; query = "LOAD DATA INFILE '" + path + "'"; query += " INTO TABLE Filmes"; SqlCommand com = new SqlCommand(query, Conn); string q; q = (string)com.ExecuteScalar();But getting error: incorrect syntax near E:\fich.txt Any ideas? What am I doing wrong?
modified on Wednesday, November 11, 2009 3:43 PM
-
I have to load .txt data to sql table. Im doing this:
string path = "E:\\fich.txt";
string query; query = "LOAD DATA INFILE '" + path + "'"; query += " INTO TABLE Filmes"; SqlCommand com = new SqlCommand(query, Conn); string q; q = (string)com.ExecuteScalar();But getting error: incorrect syntax near E:\fich.txt Any ideas? What am I doing wrong?
modified on Wednesday, November 11, 2009 3:43 PM
Maxdd 7 wrote:
StreamReader sr = File.OpenText("E:\\fich.txt");
Why would you open the file in a stream that you never use ? I'd suspect that is locking the file. Unless the file contains single quotes ( which you can fix with a string.Replace call ), I'd be more inclined to use File.ReadAllText and just do a normal insert of that text. Are you sure that execute scalar would return a string in this instance ?
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
-
Maxdd 7 wrote:
StreamReader sr = File.OpenText("E:\\fich.txt");
Why would you open the file in a stream that you never use ? I'd suspect that is locking the file. Unless the file contains single quotes ( which you can fix with a string.Replace call ), I'd be more inclined to use File.ReadAllText and just do a normal insert of that text. Are you sure that execute scalar would return a string in this instance ?
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
-
Ups, I forgot to delete that line in this post. I really would like to use Load Data Infile, just have sintax problems, cant you help me?
Did you try without that line and still got the error ?
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
-
Ups, I forgot to delete that line in this post. I really would like to use Load Data Infile, just have sintax problems, cant you help me?
I'm reading the SQL Server books online and INFILE is not in the help. Are you sure it's supported by SQL Server ?
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
-
Did you try without that line and still got the error ?
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
-
I am reading the books online, where are you getting the documentation that makes you think this will work at all ?
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
-
I am reading the books online, where are you getting the documentation that makes you think this will work at all ?
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
There isnt documentation that makes me think this will work. I used LOAD DATA INFILE a lot of times, and it works perfectly. The problem is, I never used with ASP.NET C# coding, but MS SQL is MS SQL I just need to make the syntax right. http://dev.mysql.com/doc/refman/5.1/en/load-data.html[^]
-
There isnt documentation that makes me think this will work. I used LOAD DATA INFILE a lot of times, and it works perfectly. The problem is, I never used with ASP.NET C# coding, but MS SQL is MS SQL I just need to make the syntax right. http://dev.mysql.com/doc/refman/5.1/en/load-data.html[^]
That's documentation for MySQL. My google search turned that up, too. MS SQL is NOT My SQL. I can find NO docs for INFILE in the docs that come with SQL Server, or on the web.
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
-
That's documentation for MySQL. My google search turned that up, too. MS SQL is NOT My SQL. I can find NO docs for INFILE in the docs that come with SQL Server, or on the web.
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
My mistake. Probably you are right. My apologies. I was seeing that like a horse with closed eyes :rolleyes: I already starting working on natural solution but having another problem now: My file is for example: field1,field2,field3 anotherfield1,anotherfield2,anotherfield3 Just works for first line. I'm really amateur on C#, how can I do read all other lines? (having also triplicated data because the use of for instead foreach, but I will solve this problem later)
string MystringLine; string\[\] MystringArray; char\[\] charArray = new char\[\] { ',' }; FileStream MyFileStrema = new FileStream("E:\\\\fich1.txt", FileMode.Open); StreamReader MyStreamReader = new StreamReader(MyFileStrema); MystringLine = MyStreamReader.ReadLine(); MystringArray = MystringLine.Split(charArray); int tamanho; tamanho = MystringArray.Length; //for (int i = 0; i <= MystringArray.GetUpperBound(0); i++) for (int i = 0; i < tamanho; i++) { // Response.Write(MystringArray\[0\]); string query; query = "INSERT INTO Filmes(Name, Genre, Year)"; query += " VALUES ('" + MystringArray\[0\] + "','" + MystringArray\[1\] + "','" + MystringArray\[2\] + "')"; SqlCommand com = new SqlCommand(query, Conn); SqlDataReader datareader = com.ExecuteReader(); datareader.Close(); } -
My mistake. Probably you are right. My apologies. I was seeing that like a horse with closed eyes :rolleyes: I already starting working on natural solution but having another problem now: My file is for example: field1,field2,field3 anotherfield1,anotherfield2,anotherfield3 Just works for first line. I'm really amateur on C#, how can I do read all other lines? (having also triplicated data because the use of for instead foreach, but I will solve this problem later)
string MystringLine; string\[\] MystringArray; char\[\] charArray = new char\[\] { ',' }; FileStream MyFileStrema = new FileStream("E:\\\\fich1.txt", FileMode.Open); StreamReader MyStreamReader = new StreamReader(MyFileStrema); MystringLine = MyStreamReader.ReadLine(); MystringArray = MystringLine.Split(charArray); int tamanho; tamanho = MystringArray.Length; //for (int i = 0; i <= MystringArray.GetUpperBound(0); i++) for (int i = 0; i < tamanho; i++) { // Response.Write(MystringArray\[0\]); string query; query = "INSERT INTO Filmes(Name, Genre, Year)"; query += " VALUES ('" + MystringArray\[0\] + "','" + MystringArray\[1\] + "','" + MystringArray\[2\] + "')"; SqlCommand com = new SqlCommand(query, Conn); SqlDataReader datareader = com.ExecuteReader(); datareader.Close(); }If the file is well structured, perhaps you could try issuing a BULK INSERT statement to MS SQL server? I just hit a problem where I needed to push > 200k records in multiple tables, worked in under a couple of minutes.
10110011001111101010101000001000001101001010001010100000100000101000001000111100010110001011001011
-
My mistake. Probably you are right. My apologies. I was seeing that like a horse with closed eyes :rolleyes: I already starting working on natural solution but having another problem now: My file is for example: field1,field2,field3 anotherfield1,anotherfield2,anotherfield3 Just works for first line. I'm really amateur on C#, how can I do read all other lines? (having also triplicated data because the use of for instead foreach, but I will solve this problem later)
string MystringLine; string\[\] MystringArray; char\[\] charArray = new char\[\] { ',' }; FileStream MyFileStrema = new FileStream("E:\\\\fich1.txt", FileMode.Open); StreamReader MyStreamReader = new StreamReader(MyFileStrema); MystringLine = MyStreamReader.ReadLine(); MystringArray = MystringLine.Split(charArray); int tamanho; tamanho = MystringArray.Length; //for (int i = 0; i <= MystringArray.GetUpperBound(0); i++) for (int i = 0; i < tamanho; i++) { // Response.Write(MystringArray\[0\]); string query; query = "INSERT INTO Filmes(Name, Genre, Year)"; query += " VALUES ('" + MystringArray\[0\] + "','" + MystringArray\[1\] + "','" + MystringArray\[2\] + "')"; SqlCommand com = new SqlCommand(query, Conn); SqlDataReader datareader = com.ExecuteReader(); datareader.Close(); }This is too much code.
string[] datum = File.ReadAllLines("E:\\fich1.txt");
foreach(string data in datum)
{
string [] MystringArray = data.Split(new char[] {','} );// rest of your SQL code will work from here, although I'd use a paramaterised query, or at a minumum do MyStringArray[0].Replace("'", "''") to double quote the strings so the SQL does not blow up
}
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
-
If the file is well structured, perhaps you could try issuing a BULK INSERT statement to MS SQL server? I just hit a problem where I needed to push > 200k records in multiple tables, worked in under a couple of minutes.
10110011001111101010101000001000001101001010001010100000100000101000001000111100010110001011001011