Error in get data from Excel

Question

I'm trying to get data from Excel file using:

OpenFileDialog OpenCSVDialog = new OpenFileDialog();
            OpenCSVDialog.Filter = "Excel |*.xlsx";
            OpenCSVDialog.ShowDialog();
            ExcelFileName = System.IO.Path.GetFileName(OpenCSVDialog.FileName);
            string path = System.IO.Path.GetDirectoryName(OpenCSVDialog.FileName);
            fullpath = Path.Combine(path, ExcelFileName);

            dt_data = new DataSet("CSV File");
            var connString = string.Format(
                @"Provider=Microsoft.Jet.OleDb.4.0; Data Source={0};Extended Properties=""Text;HDR=YES;FMT=Delimited""",
                Path.GetDirectoryName(fullpath)
            );
            using (var conn = new OleDbConnection(connString))
            {
                conn.Open();
                var query = "SELECT * FROM [" + Path.GetFileName(fullpath) + "]";
                using (var adapter = new OleDbDataAdapter(query, conn))
                {
                    adapter.Fill(dt_data);
                }

            }

but when browsing see error coming in this line: adapter.Fill(dt_data);:

I have the Excel file in related path.

Please show the line where the exception *occured*, not where it was caught. Most likely though, you need to escape the filename, eg write `Data Source='{0}'` — Panagiotis Kanavos, Nov 11 '15 at 17:44
Just noticed that you are using the filename in the `FROM` clause. `FROM` is used to select from a specific *sheet*, not file. It should be something like `FROM [Sheet1$]` . — Panagiotis Kanavos, Nov 11 '15 at 17:52

Panagiotis Kanavos · Answer 1 · 2015-11-12T15:24:47.290

0

The FROM [...] clause specifies which sheet (actually, range) you want to read from. It should contain the sheet's name, not the workbook's filename.

If your sheet is called Sheet1, you should use

var query = "SELECT * FROM [Sheet1$]";

There are many answers in SO that show how to extract the first sheet's name if it isn't known etc.

UPDATE

I also noticed that you use the file's directory path in the connection string instead of the full path. You should use the full file path instead:

 var connString = string.Format(
            @"Provider=Microsoft.Jet.OleDb.4.0; Data Source={0};Extended Properties=""Text;HDR=YES;FMT=Delimited""",
            fullpath
        );

edited Nov 12 '15 at 15:24

answered Nov 11 '15 at 17:54

Panagiotis Kanavos

120,703
13
188
236

when using this following error showing. "The Microsoft Jet database engine could not find the object 'Sheet1$.txt'. Make sure the object exists and that you spell its name and the path name correctly." – Tom Nov 11 '15 at 17:58
What did you try? Did you use the file path as data source or something else, like the directory? – Panagiotis Kanavos Nov 11 '15 at 21:10
I changed only this part. "var query = "SELECT * FROM [Sheet1$]"; – Tom Nov 12 '15 at 04:27
Your connection string though uses the *directory's* path as a data source instead of the file's. You should change it to use the full file path – Panagiotis Kanavos Nov 12 '15 at 15:24

score 0 · Answer 2 · answered Nov 14 '15 at 08:17

I have changed the code . Now its working.

OpenFileDialog OpenCSVDialog = new OpenFileDialog();
            OpenCSVDialog.Filter = "Excel |*.xls";
            OpenCSVDialog.ShowDialog();
            ExcelFileName = System.IO.Path.GetFileName(OpenCSVDialog.FileName);
            string path = System.IO.Path.GetDirectoryName(OpenCSVDialog.FileName);
            fullpath = Path.Combine(path, ExcelFileName);

            dt_data = new DataSet("CSV File");

            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
          "Data Source=" + fullpath +
          ";Extended Properties='Excel 8.0;'";

            using (var conn = new OleDbConnection(connString))
            {
                conn.Open();
                string query = string.Format("select * from [{0}$]", "Sheet1");

                using (var adapter = new OleDbDataAdapter(query, conn))
                {
                    adapter.Fill(dt_data);
                }
            }

score -2 · Answer 3 · answered Nov 11 '15 at 17:42

See my class below. Look at the splitting of the full filename into a path and filename.

public class CSVReader
    {

        public DataSet ReadCSVFile(string fullPath, bool headerRow)
        {

            string path = fullPath.Substring(0, fullPath.LastIndexOf("\\") + 1);
            string filename = fullPath.Substring(fullPath.LastIndexOf("\\") + 1);
            DataSet ds = new DataSet();

            try
            {
                if (File.Exists(fullPath))
                {
                    string ConStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}" + ";Extended Properties=\"Text;HDR={1};FMT=Delimited\\\"", path, headerRow ? "Yes" : "No");
                    string SQL = string.Format("SELECT * FROM {0}", filename);
                    OleDbDataAdapter adapter = new OleDbDataAdapter(SQL, ConStr);
                    adapter.Fill(ds, "TextFile");
                    ds.Tables[0].TableName = "Table1";
                }
                foreach (DataColumn col in ds.Tables["Table1"].Columns)
                {
                    col.ColumnName = col.ColumnName.Replace(" ", "_");
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return ds;
        }
    }

Why is this an answer to the OP's question? Posting some code and waiting for others to discover what it does is *not* considered a good practice in SO. If you wanted to say that the `FROM` statement should change, say it explicitly. Not that it will solve the problem though - `FROM` refers to the *sheet* not the file — Panagiotis Kanavos, Nov 11 '15 at 17:51
Because it is better to put this code into a class that to make it inline code. Since I developed this code a year ago it was better to post a better method than just to answer the question. Let the op solve is own issue by looking at code that actually works. — jdweng, Nov 11 '15 at 21:25

Error in get data from Excel

3 Answers3