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
– John Wu, Commented Jan 24, 2019 at 1: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
– Ben, Commented Jan 24, 2019 at 1: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
– PiggyChu515, Commented Jan 24, 2019 at 1:07
Directory.GetCurrentDirectory() may give the directory where the current asp.net webpage is located — Gauravsa
– Gauravsa, Commented Jan 24, 2019 at 1:07

Gauravsa · Accepted Answer · 2019-01-24 01:13:19Z

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\

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!
See if it helps: stackoverflow.com/questions/4467300/… (refer to the answer with 4 upvotes)..
I solved it by adding CharacterSet=65001 under the Extended Properties in connection string!

Collectives™ on Stack Overflow

Can't read from CSV

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related