Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Monday, September 12, 2011 6:13 PM
Hi
I need to import the data from a text file into a specific SQL table and i'm having a hard time with the command. Any help is appricated.
So I do have a text file with 2 columns separated by a space. These two columns store numbers in double form. Below is what I have now for the code and it has a few errors when compiling
public static void Main(string[] args)
{
SqlConnection thisConnection = new SqlConnection("Server=localhost;Port=5432;User Id=postgres;Database=Test;");
SqlCommand nonqueryCommand = thisConnection.CreateCommand();
// StreamReader objReader = new StreamReader("C:\\Users\\account\\Documents\\testFile.txt");
string[] importFile = Directory.GetFiles("C:\\Users\\account\\Documents\\testFile.txt");
try
{
thisConnection.Open();
nonqueryCommand.CommandText = "CREATE TABLE TestTable (Time timestamp, num1 decimal (4,2), num2 decimal(4,2))";
nonqueryCommand.ExecuteNonQuery();
nonqueryCommand.CommandText = "INSERT INTO TestTable VALUES (@num1, @num2)";
nonqueryCommand.Parameters.Add("@num1");
nonqueryCommand.Parameters.Add("@num2");
string[] allLines = File.ReadAllLines(importFile);
{
for(int i=1; i<allLines.Length; i++)
{
string[] items = allLines[i].Split(new char[] { ' ' });
nonqueryCommand.Parameters["@num1"].Value = items[0];
nonqueryCommand.Parameters["@num2"].Value = items[1];
nonqueryCommand.ExecuteNonQuery();
}
}
}
finally
{
thisConnection.Close();
}
}
All replies (9)
Monday, September 12, 2011 6:47 PM âś…Answered
I fixed the build errors by commenting out the Directory.GetFiles call as it is not needed as you have only one file. I also switched the File.ReadAllLines to use that filename.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
SqlConnection thisConnection = new SqlConnection("Server=localhost;Port=5432;User Id=postgres;Database=Test;");
SqlCommand nonqueryCommand = thisConnection.CreateCommand();
// StreamReader objReader = new StreamReader("C:\\Users\\account\\Documents\\testFile.txt");
//string[] importFile = Directory.GetFiles("C:\\Users\\account\\Documents\\testFile.txt");
try
{
thisConnection.Open();
nonqueryCommand.CommandText = "CREATE TABLE TestTable (Time timestamp, num1 decimal (4,2), num2 decimal(4,2))";
nonqueryCommand.ExecuteNonQuery();
nonqueryCommand.CommandText = "INSERT INTO TestTable VALUES (@num1, @num2)";
nonqueryCommand.Parameters.Add("@num1");
nonqueryCommand.Parameters.Add("@num2");
string[] allLines = File.ReadAllLines("C:\\Users\\account\\Documents\\testFile.txt");
{
for (int i = 1; i < allLines.Length; i++)
{
string[] items = allLines[i].Split(new char[] { ' ' });
nonqueryCommand.Parameters["@num1"].Value = items[0];
nonqueryCommand.Parameters["@num2"].Value = items[1];
nonqueryCommand.ExecuteNonQuery();
}
}
}
finally
{
thisConnection.Close();
}
}
}
}
Monday, September 12, 2011 6:41 PM
Are you using postgres? SqlConnection is for sql server.
You can probably switch to OdbcConnection or OleDbConnection instead.
Monday, September 12, 2011 6:46 PM
Are you using postgres? SqlConnection is for sql server.
You can probably switch to OdbcConnection or OleDbConnection instead.
I am using posgres. I thought change it to Sqlcommand would make it simpler for the question here. Also, I am using Npgsql instead of ODBC.
Monday, September 12, 2011 6:48 PM
I am using posgres. I thought change it to Sqlcommand would make it simpler for the question here. Also, I am not using Npgsql instead of ODBC.
We pay attention to all details! :)
Monday, September 12, 2011 7:06 PM
I got the following error: "Index was outside the bounds of the array."
Monday, September 12, 2011 7:09 PM
You probably need to check if you get two values after the split. If you post that you get an error it helps to include details from the exception, message and any inner exceptions.
Monday, September 12, 2011 7:37 PM
actually, it works. I just need to fix a bit. thanks. though, still have a problem when reading. so, in my text file, i have some lines that are empty and some that are type string. So in the above code, when the program scan through the text file, it crashed when it see the null or string line. is there a way i can prevent this?
for example i have a text file that form in this way
num11 num12 num13
.
.
numN1 numN2 numN3
0 0 0
null null null
text1 text 2 text 3
and the same format continues like this onward
Monday, September 12, 2011 8:05 PM
so i figure i am not storing anything in the database unless its a number, zero count as number.
I was thinking of doing something like this but it doesn't work
for (int i = 0; i < allLines.Length; i++)
{
string[] items = allLines[i].Split(new char[] { ' ' });
while(items.GetValue != null && items.GetType != char)
{
nonqueryCommand.Parameters["@num1"].Value = items[0];
nonqueryCommand.Parameters["@num2"].Value = items[1];
nonqueryCommand.Parameters["@num3"].Value = items[2];
nonqueryCommand.ExecuteNonQuery();
}
}
Monday, September 12, 2011 8:52 PM | 1 vote
You wouldn't use a while loop there.
items[0] is getting the value, there is no method GetValue neither GetType.
Since you split a string into a string array you need to check that the first item is numeric. You can do this in many ways, Int32.TryParse is probably most useful as you want it as in that datatype.