How to convert excel file to the text file in c#?

Hi all,

 I have a application that need save excel file to the database. I need convert excel file to the text file and then use the bulk insert in t-sql.

Can someone give me some ideas about how to convert the excel file to the text file.

 

 

Thanks in advance!

0
bjcanada
1/12/2009 10:01:42 PM
asp.net.integration 1945 articles. 0 followers. Follow

3 Replies
904 Views

Similar Articles

[PageSpeed] 32

 

bjcanada:

Can someone give me some ideas about how to convert the excel file to the text file.

You can treat an Excel file as a database to extract information.  This sample binds it to a GridView but you could loop with the DataReader to extract the data and write it to a file.

 

        // using System.Data.OleDb
        OleDbConnection ExcelConection = null;
        OleDbCommand ExcelCommand = null;
        OleDbDataReader ExcelReader = null;
        OleDbConnectionStringBuilder OleStringBuilder = null;

        try
        {
            OleStringBuilder =
                new OleDbConnectionStringBuilder(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';");
            OleStringBuilder.DataSource = MapPath(@"~\App_Datav\MyExcelWorksheet.xls");

            ExcelConection = new OleDbConnection();
            ExcelConection.ConnectionString = OleStringBuilder.ConnectionString;

            ExcelCommand = new OleDbCommand();
            ExcelCommand.Connection = ExcelConection;
            ExcelCommand.CommandText = "Select * From [Sheet1$]";

            ExcelConection.Open();
            ExcelReader = ExcelCommand.ExecuteReader();

            GridView1.DataSource = ExcelReader;
            GridView1.DataBind();
        }
        catch (Exception Args)
        {
            LabelErrorMsg.Text = "Could not open Excel file: " + Args.Message;
        }
        finally
        {
            if (ExcelCommand != null)
                ExcelCommand.Dispose();
            if (ExcelReader != null)
                ExcelReader.Dispose();
            if (ExcelConection != null)
                ExcelConection.Dispose();
        }
 
Steve Wellens

My blog
0
SGWellens
1/12/2009 11:07:14 PM

Thanks a lot!

 Can someone tell how to use bulk copy directly copy excel file into the sql server database?

 

Thanks in advance1

0
bjcanada
1/13/2009 2:34:33 PM

Hi,

Do you mean you want to copy the excel data to SQLServer by using SqlbukCopy, right? You can try the following code:

 

1    // Connect to your Excel sheet.
2    
3    string excelConnectionString = @"Provider=Microsoft    .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended    Properties=""Excel 8.0;HDR=YES;""";
4    
5    
6    using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
7    {
8        OleDbCommand command = new OleDbCommand("Select YourFields FROM [Data$]", connection);
9        connection.Open();
10       
11       // Create DbDataReader to Data Worksheet
12   
13       using (DbDataReader dr = command.ExecuteReader())
14       {
15           // SQL Server Connection String
16           string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";
17   
18           // Bulk Copy to SQL Server
19           using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
20           {
21               bulkCopy.DestinationTableName = "ExcelData";
22               bulkCopy.WriteToServer(dr);
23           }
24       }
25   }
26   
 

Thanks.

 


Michael Jin.
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Nai
1/15/2009 6:41:14 AM
Reply:

Similar Artilces:

converting excel files to text files on the fly
Hi folks, Does anyone know of a way to take a (Windows) Excel table and turn it into a text format (e.g. tab-delimited) while running on a Unix platform? I'm trying to write a program to go out and harvest our online journal holdings information and plunk it into a database (this program would run once a month or so as needed to keep up-to-date). Unfortunately, one of our journal providers favors a proprietary format for their data, so i'm unable to manipulate them appropriately. Does anyone know how I might do this? (I'm envisioning a command-line program that would...

COnverting image file into PDF file in C#.NET
hi friends, i have a problem in creating a PDF file from an image file. first of all pls clarify what is the internal concept in converting to PDF file. if nay one knows pls mail me the related code if you find any solution pls mail me at spend5mins@yahoo.com thank u all hari babu...

How to Create .mdb file from Excel file using C#.net
Hi all I want to create .mdb file that i have created excel feel from MS jet Database... through code behind Help me in this..  Regards RaviPlease click "mark as answer" if this post helped you. How to create an mdb file programmtically:http://support.microsoft.com/kb/317881How to import Excel data into it: http://www.mikesdotnetting.com/Article.aspx?ArticleID=79 Regards Mike [MVP - ASP/ASP.NET]My site...

How can i convert a .xls (excel file) to tab delimited text file
Can i get some tutorial to convert a excel file to a tab delimited text file. Regards Karen Try this link http://www.developerfusion.co.uk/show/4405/ ThanksGirijeshhttp://www.girijesh.in/ Girijesh,   Thanks a lot for ur link , should i add the excel object to th bin directory of my computer before calling it in the project. Regards Karen...

ANSI text file convert to Unicode text file problem
Dear all, I got a problem from my PB 7.03 ANSI version. I need to write a program to convert a ANSI text file to Unicode text file. I had been tried to use the FromANSI, toANSI, FromUnicode and ToUnicode PB function to do that. Since those functions for convert the ANSI file to Unicode file also Unicode file to ANSI file it seem working well. However, when I try to open the Unicode file (which is convert from ANSI to Unicode format by PB function) from my Wordpad, It will show me the result was fail... So now I have a question... As I know the Unicode have different type (eg. UTF-...

How to convert .tiff image file to .pdf file without using any 3rd party tool using C#.net
HI allI have a urgent requirement i want to convert .tiff image to .pdf file Thanks in advance Regards RaviPlease click "mark as answer" if this post helped you. I dont think its possible without doing a lot of code or using third party tools, but if you  are looking for a free tool you can try a combination of http://blog.codebeach.com/2008/02/convert-multipage-tiff-to-pdf-in-net.htmland  http://www.codeproject.com/KB/aspnet/Creating_PDF_documents_in.aspx Thanks and RegardsS.K.Vignesh----------------------Do remember to "Mark as answer" for this pos...

Integrating do I integrate all help files
How do I integrate PFC, sybase and powerbuilderhelp files together help such that I can get help on all issues (PFC, sybase and powerbuilderhelp ) through powerbuilder's help-content menu only . Please don't cross-post. On 1 Nov 2004 12:34:08 -0700, "Damola adesuyi" <someone@microsoft.com> wrote: >How do I integrate PFC, sybase and powerbuilderhelp files together help such >that I can get help on all issues (PFC, sybase and powerbuilderhelp ) >through powerbuilder's help-content menu only . > Bruce Armstrong [TeamSybase] http://www....

How to create and app to convert a text file into another formated text file.
Hello I'm new to the ASP forums, I have a project that I need to create and app that will convert a simple text with a ssn, firstname, lastname, etc.. With the click of the button will will convert the file to another text file but formated as firstname, lastname, ssn, etc. (I hope so far this is making sense). Anyway I have create the file upload control and a upload button to upload the text file. Now I need code for the conversion button and I'm having trouble codeing it. I just need for the button to read the text file and covert it over to new formated text file and save it. Below ...

How to convert the html content in Excel file to csv fomat in Excel file
How to convert the html content in Excel file to csv fomat in Excel file  Hello Tapan,Can you explain what are you trying to do? Do you want to export the html content in Excel or CSV File ?Thanks,Deepesh Verma Dont forget to click “Mark as Answer” on the post that helped you.This credits that member, earns you a point and mark your thread as Resolved for the sake of Future Readers....

Converting Excell File Into Html File
HI AllI need to convert my excell file into html file through asp.net and c# code.Can anyone help me on this or can u provide the code for this.Thanks in advance. You want something like:         string connectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""",path);            string query=String.Format("select * from [{0}$]",sheetName);            OleDbDataAdapter dataAdapter =new OleDbDataAdapt...

How to convert text file to binary file
 Hi,I have a text file and want to convert it to binary file in c#.For e.g i have a file Example.txt 504  Nigeria     508  Ethiopia512  Egypt     516  Congo-Kinshasa520  South Africa524  Tanzania528  Sudan532  Kenya536  Morocco540  Algeria and i want to convert it into binary file.36 0F 81 5B 0D 58 6j­ËÙj36 0C A1 5B 4D 58 6j­ËÙj How can i do that? Guide me.....   hi Hamza, Plz check the link below.It may contain  answer to your question, http://www.tanguay.info/webold/codeExample.php5?...

How i convert a excel file to csv file ?
Hei friends, i'am new in newbie in Perl, and i need how i convert a excel file in csv file in linx ? Can you help me, please ?? Thanks, Paulito There is a module on CPAN called Spreadsheet::WriteExcel you can use to read xls data. =20 -----Original Message----- From: Santana =5Bmailto:paulito.santana=40gmail.com=5D=20 Sent: Tuesday, August 07, 2007 8:22 AM To: beginners=40perl.org Subject: How i convert a excel file to csv file ? Hei friends, i'am new in newbie in Perl, and i need how i convert a excel file in csv file in linx ? Can you help me, please ...

converting excel file to XML file
I have created an application in ASP.net AJAX and I have one excel file which is the database for the project, I want to convert this excel file to XML file. Is there any code to do so. Thanks, Hemil  If the file is in Microsoft Excel 2007 you can choose Save As... and select XML. For Excel 2003 you can download this toolkit from Microsoft. http://www.microsoft.com/downloads/details.aspx?familyid=e315c516-2c2c-4870-a189-d47a5d7ffeb3&displaylang=enJoseph GuadagnoMy Website: http://www.josephguadagno.netMy Blog - http://weblogs.asp.net/jguadagno/ If this has helped Please: Don...

Converting xml file into an excel file
 Hi, Can anyone guide me how to convert an xml file into an excel file in c#.netthanks,   Hi,    You can go through the following links:http://www.carlosag.net/Tools/ExcelXmlWriter/http://www.codeproject.com/KB/office/excelxmlspreadsheet.aspx  -Sri-------------------------------------------------If this post was useful to you, please mark it as answer. Thank you! Hi renuga, Another way is to use xsl file to tranform one xml file to an Excel Spreadsheet. Convert XML To an Excel Spreadsheet Using XSLhttp://aspalliance.com/471_Convert_XML_To_an_Excel_Spr...

Web resources about - How to convert excel file to the text file in c#? - asp.net.integration

List of converts to Buddhism - Wikipedia, the free encyclopedia
Ajahn Brahm (born 1951), Religious teacher and student of the late Thai monk Ajahn Chah . Currently he is the abbot of the Bodhinyana Monastery, ...

Facebook Timeline For Pages: 8 Million Converts, 29 Million To Go
... reach to 98 percent of users who liked its page, as well as to double its engagement rate, during a 28-day period. Dr. Pepper hasn’t yet converted ...

Facebook stock roundup: Execs offload more stock, directors convert shares
... Bowles, the former co-chair of the now-defunct National Commission on Fiscal Responsibility and Reform, and Reed Hastings, Netflix CEO, converted ...

How to Convert Leads Into Buying Customers - The 9 Step Sales Process EVERYONE Should Use
... School of Business in Toronto on the topics of Sales and Entrepreneurship. I shared with them the sales process that I find How to Convert Leads ...

Bitcoin : Should I convert my savings to Bitcoin?
Answer (1 of 7): You should think of this question as "Should I invest all my savings in Bitcoin?", and not "Should I have my savings denominated ...

Coordinates - Calculate and Convert a Position on the App Store on iTunes
Get Coordinates - Calculate and Convert a Position on the App Store. See screenshots and ratings, and read customer reviews.

Catholics should not convert Jews, says Vatican, in push against anti-Semitism
... 50 years since the Vatican's repudiation of the concept of Jewish guilt for Jesus' death.  Vatican City: Catholics should not try to convert ...

Today’s apps gone free: BlockBear, Stock Grader, PDF Smart Convert and more
Block ads and other annoying content, take the emotion out of investing, and convert almost anything into a PDF with todays collection of apps ...

Ford patents rear wheel that converts into a unicycle
Filed under: Government/Legal , Green , Ford , Green Driving , Motorcycle , Electric Ford is thinking about allowing drivers to remove a rear ...

How to convert your old VHS tapes to DVD with this software
Remember those old VHS tapes you have sitting in the corner? Convert them into DVDs with this affordable software so you can easily watch them ...

Resources last updated: 1/15/2016 4:33:06 PM