[RESOLVED] Import data from text and excel

Hello, I am working on a C# web application under visual studio 2005.

I want to import data from text file or excel file into a SQL 2005 database.
I have 3 columns in the text file separated by |, example of the text file format:

1|11122222|Name1
2|22299809|Name2

Can you tell me how to import data from text or excel file?

Another question, if you provide a solution for the import from excel, can you note which office (2003 or 2007 or 2010)?

Thank you.

lebdeveloper

Another question, if you provide a solution for the import from excel, can you note which office (2003 or 2007 or 2010)?
 

Check this solution..

http://weblogs.asp.net/rajbk/archive/2009/05/02/uploading-an-excel-file-to-sql-through-an-asp-net-webform.aspx

lebdeveloper

I have 3 columns in the text file separated by |, example of the text file format:

Break each line into string array with char separator "|". After that you can use above code to upload.

Hi, firt of all, you need to know the path of the file. I recommend you use cvs files for working with excel.

Any Excel can open this kind of file. Then You should do something like this:

StreamReader sw = new StreamReader("path of file + file + .extension");


while(!sw.EndofStream)
{
string[] partsOfString = sw.ReadLine().split('|');


set a parameter = partsOfString[0];
set a parameter = partsOfString[1];
set a parameter = partsOfString[2];


command.parameters.add(params);
command.ExecuteInsert();
}


All this doing normal process to insert into a database right?


lebdeveloper

Can you tell me how to import data from text or excel file?

You can use OLeDB connection for reading a Xls file...and you can use StreamReader to read the txt file....

When you read xls file, you can fill the dataset using that data and then call a stored proc in Sql DB to make bulk insert....the easient way to do is, convert the dataset to Xml, and give that xml as input to the stored proc...and in the stored proc you can easily parse the xml and make inserts into the DB....

For txt file, you got to parse the string which you get out of streamreader, and then write some code to convert it into DataTable which will be merged to a dataset, then follow the above procedure....

PS - I have given you conceptual idea on how it works...you got read with some articles on line on how to accomplish the above said tasks...may be you can break them into smaller tasks and go one at a time...

Thanks.

thoughts on "[RESOLVED] Import data from text and excel"

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>