Can't read from CSV

Question

I followed Jim Scott's instruction to read CSV into a DataTable with the following code:

private OleDbConnection CNN = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Directory.GetCurrentDirectory()+";Extended Properties=\"Text;HDR=Yes\"");
private OleDbCommand CMD;
private OleDbDataAdapter ADT;
private DataTable DT=new DataTable();

protected void Page_Load(object sender, EventArgs e)
{
    CNN.Open();
    CMD = new OleDbCommand(@"select * from [Report.csv]", CNN);
    ADT = new OleDbDataAdapter(CMD);
    ADT.Fill(DT);
}

I had put the Report.csv under the root directory, and tried following things to no avail!

Changed Data Source to localhost, (localhost), ~, ~\\.
Changed Report.csv to Report.
Finally change Data Source to Directory.GetCurrentDirectory() to get it to connect correctly.

Problem: It can NOT find Report.csv!

I wish this to be a webpage, so what I need is a way for the OleDbConnection connect to localhost and pointed to the root directory!

It will be VERY nice if somebody could teach me how to do that!

Somebody please be so kind and tell me where did I do wrong and how to set it up correctly!

Much appreciated!!!

Root directory <> GetCurrentDirectory(). Put it in `c:\\` and change your code to match, and make sure it works. — John Wu, Jan 24 '19 at 01:04
Check the value returned by `Directory.GetCurrentDirectory()` and store it there. You seem to be just hoping you're putting the file in the correct place. — Ben, Jan 24 '19 at 01:06
Thanks for your advice. But please note that my tag includes **ASP.net**! I intended to publish this as a website, so unfortunately the **solution** is not as easy as **Put it in C:**. — PiggyChu515, Jan 24 '19 at 01:07
Directory.GetCurrentDirectory() may give the directory where the current asp.net webpage is located — Gauravsa, Jan 24 '19 at 01:07

Gauravsa · Accepted Answer · 2019-01-24T01:13:19.207

3

Most likely, its a path issue. Can you try doing the following:

protected void Page_Load(object sender, EventArgs e)
{
    CNN.Open();
    string fileName = "C:\Users\username\Desktop\Report.csv";
    string sqlQuery = @"select * from [" + fileName + "]";
    CMD = new OleDbCommand(sqlQuery, CNN);
    ADT = new OleDbDataAdapter(CMD);
    ADT.Fill(DT);
}

Update:

Instead of Directory.GetCurrentDirectory(), you can use HttpContext.Current.Server.MapPath("~") which will give the path of the current root.

If your website is at:

C:\Web\shop

and you are accessing webpage: http://localhost:8080/Application/users/userdetails.aspx

 1. Server.MapPath(".") returns C:\Web\shop\users 
 2. Server.MapPath("~") returns C:\Web\shop\

edited Jan 24 '19 at 01:13

answered Jan 24 '19 at 01:05

Gauravsa

6,330
2
21
30

Possibly also a permissions issue. – ProgrammingLlama Jan 24 '19 at 01:12
Thanks! That solved it! But the encoding is WAY~ off! Let me go see if I can find anything about it! An reference link will be greatly appreciated! – PiggyChu515 Jan 24 '19 at 01:18
See if it helps: https://stackoverflow.com/questions/4467300/how-can-i-get-csv-file-encoding-utf-8-in-c-net (refer to the answer with 4 upvotes).. – Gauravsa Jan 24 '19 at 01:32
I solved it by adding `CharacterSet=65001` under the `Extended Properties` in connection string! – PiggyChu515 Jan 24 '19 at 01:56

Can't read from CSV

1 Answers1