none
Importing objects from .xlsx or .txt files RRS feed

  • Question

  • Hello,

    Is it possible to import objects from Excel (or .txt) file to be used in Windows Forms Application project?

    I don't have MS SQL server access to acquire data from there so I was thinking that is there a way to construct a local database by reading an Excel spread sheet?

    • Moved by Amanda Zhu Wednesday, June 12, 2013 7:26 AM move to a more appropriate forum
    Tuesday, June 11, 2013 1:29 PM

Answers

  • Yes.  You can use oledb with a connection string to import data.  I would recommend making the excel file xls or make the text file CSV.  The code is almost identical to the SQL code except you would change the connection string and use OLE class instead a SQL class.  Do you have any similar SQL code that you want modified?  Below is an example of both methods

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.SqlClient;
    using System.Data.OleDb;
    using System.Data;
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                string server = "My Server";
                string database = "Table 1";
                string strCon1 = string.Format("Data Source={0}\\SQL;Initial Catalog={1};" +
                   "Integrated Security=SSPI",
                   server, database);
                string databasetable1 = "table 1";
                string strSQL1 = string.Format("SELECT * FROM {0}", databasetable1);
                DataTable table1 = new DataTable();
                SqlDataAdapter dataAdapter1 = new SqlDataAdapter(strSQL1, strCon1);
                dataAdapter1.Fill(table1);
                string filename = @"c:\temp\abc.xls";
                string strCon2 = string.Format(
                           "Provider=Microsoft.Jet.OLEDB.4.0;" +
                           @"Data Source={0};" +
                           "Extended Properties=\"Excel 8.0;HDR=YES\"",
                           filename);
                string strSQL2 = string.Format("SELECT * FROM {0}$", databasetable1);
                DataTable table2 = new DataTable();
                OleDbDataAdapter dataAdapter2 = new OleDbDataAdapter(strSQL2, strCon2);
                dataAdapter2.Fill(table2);
     
            }
        }
    }


    jdweng

    Tuesday, June 11, 2013 3:39 PM