UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> C# Export A Table From Access Db To Csv.    
 
   
twigleaf
post Dec 22 2014, 09:19 AM
Post#1



Posts: 229
Joined: 18-September 03
From: Halifax, United Kingdom


Hi All

I am building an application, which was originally an access DB. Given most machines at work have Access on them. It is a basic table with forms for input and navigation.

I am looking at C# using Visual Studio 2010. The same DB table has been rebuilt and imported from MS Access into Visual studio as the basis of my data, which is essentially now a flat file with a primary key.

My application is basically a few Windows forms as data entry, with the table stored as an Access DB behind it. Eventually if I can get the C# working I might put it across to Android so it can work on mobile devices etc.

I have done all my input screens and have a page that allows me to move between them etc.

However I want to be able to export the data to a CSV or Excel format. My idea is that I can then have the various users email it as and when required. I am not wanting this little Application to have any network requirements so need that export feature for a local copy.

The export would be of the whole table. (Probably a 1000 records or so)


All my research online has brought up web pages that are either using some ASP.net, or SQL strings and when looking at it, my knowledge is not good enough to know how to alter all of the various errors that crop up. I just don't know in C# yet what a 'class' is etc.

Is there an easy way to just dump the table or a gridview that is bound to the table? And just save it to .CSV or .XLS?

Any help or direction much appreciated.
Go to the top of the page
 
Kamulegeya
post Dec 22 2014, 09:39 AM
Post#2



Posts: 1,767
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


Hello,

Check out the link below

http://www.c-sharpcorner.com/UploadFile/de...tension-method/



Ronald
Go to the top of the page
 
twigleaf
post Dec 23 2014, 05:17 AM
Post#3



Posts: 229
Joined: 18-September 03
From: Halifax, United Kingdom


Hi Ronald.

Thanks for the reply. Looking at that it is like a lot of other ones I have followed.
It talks about creating tables but I already have one, and I am not confident in getting it to point to my existing table. So my attempts don't work, though this is the best one to date my current dataGridView1 isn't working.

Most of the methods I have found on the internet mention creating a table, or linking to SQL. Not actually just showing you how to export an existing and already created table. Via a button.

Following that link word for word and I get various little errors, in this case "OpenSaveFileDialog" doesn't exist. So the button doesn't work for me to test.

I assume visual studio 2010 needs something else installing perhaps to make that code work?

Regards
Go to the top of the page
 
Kamulegeya
post Dec 23 2014, 07:24 AM
Post#4



Posts: 1,767
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


QUOTE (twigleaf @ Dec 23 2014, 01:17 PM) *
Hi Ronald.

Thanks for the reply. Looking at that it is like a lot of other ones I have followed.
It talks about creating tables but I already have one, and I am not confident in getting it to point to my existing table. So my attempts don't work, though this is the best one to date my current dataGridView1 isn't working.

Most of the methods I have found on the internet mention creating a table, or linking to SQL. Not actually just showing you how to export an existing and already created table. Via a button.

Following that link word for word and I get various little errors, in this case "OpenSaveFileDialog" doesn't exist. So the button doesn't work for me to test.

I assume visual studio 2010 needs something else installing perhaps to make that code work?

Regards


Hello,

Actually the table refereed to is not the access table but an in memory object.

Read


http://stackoverflow.com/questions/2374221...le-to-datatable

http://msdn.microsoft.com/en-us/library/5s...=vs.110%29.aspx

http://msdn.microsoft.com/en-us/library/aa...v=vs.71%29.aspx
Go to the top of the page
 
twigleaf
post Dec 23 2014, 08:17 AM
Post#5



Posts: 229
Joined: 18-September 03
From: Halifax, United Kingdom


Hi Kamulegeya

Thanks for the link, I am even more confused now. Perhaps I am not explaining myself (Very likely)

I used the wizard in Visual Studio to bring in the table, it just happened to be in an Access DB as I had a similar app in Access with forms. However as nothing from Microsoft works in Android I wanted to create a self standing app in C# so I could do something for Mobile devices eventually. (Once I learned enough C#)

Given it was so easy to bring in I didn't think the export would be this tricky. Creating record sets and connection strings is a little over complicated. And 33 web sites (including Youtube videos) and counting have given me so many ways not to succeed, each one slightly different in how it approaches exporting my 'dataGridView1' to a CSV.

Currently three windows forms are bound to the table, allowing me to update, remove, add and edit data. A gridview on one form is bound and lets me get an overview of data which I can sort and filter as needed using C# code, to make sure some people are not blank entries (they prompt further action). So I assume it is loaded into memory as it must be visible and must work. Actually there is two copies of the table in Visual studio, a Review and a Debug version of the DB (three if I Publish) because Visual Studio works like that.

private void Temp_Load(object sender, EventArgs e)
{
this.EmployeeTableAdapter.Fill(this.database1DataSet.Employee);

DataView dt = database1DataSet.Tables[0].DefaultView;
dt.RowFilter = string.Format("[Department No] LIKE ''");
dataGridView1.DataSource = dt;

dataGridView1.Sort(dataGridView1.Columns[0], ListSortDirection.Ascending);
}


If I can run code like that it can see a table without any recordsets or connections strings, so why do I need to create a connection, or another table to then export what is already in use, working and visible?

Just need the equivalent of a "Right click -> Export, or "Right click -> Save As."


That help explain a little better?

Regards
Go to the top of the page
 
Kamulegeya
post Dec 23 2014, 09:16 AM
Post#6



Posts: 1,767
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


QUOTE (twigleaf @ Dec 23 2014, 04:17 PM) *
Hi Kamulegeya

Thanks for the link, I am even more confused now. Perhaps I am not explaining myself (Very likely)

I used the wizard in Visual Studio to bring in the table, it just happened to be in an Access DB as I had a similar app in Access with forms. However as nothing from Microsoft works in Android I wanted to create a self standing app in C# so I could do something for Mobile devices eventually. (Once I learned enough C#)

Given it was so easy to bring in I didn't think the export would be this tricky. Creating record sets and connection strings is a little over complicated. And 33 web sites (including Youtube videos) and counting have given me so many ways not to succeed, each one slightly different in how it approaches exporting my 'dataGridView1' to a CSV.

Currently three windows forms are bound to the table, allowing me to update, remove, add and edit data. A gridview on one form is bound and lets me get an overview of data which I can sort and filter as needed using C# code, to make sure some people are not blank entries (they prompt further action). So I assume it is loaded into memory as it must be visible and must work. Actually there is two copies of the table in Visual studio, a Review and a Debug version of the DB (three if I Publish) because Visual Studio works like that.

private void Temp_Load(object sender, EventArgs e)
{
this.EmployeeTableAdapter.Fill(this.database1DataSet.Employee);

DataView dt = database1DataSet.Tables[0].DefaultView;
dt.RowFilter = string.Format("[Department No] LIKE ''");
dataGridView1.DataSource = dt;

dataGridView1.Sort(dataGridView1.Columns[0], ListSortDirection.Ascending);
}


If I can run code like that it can see a table without any recordsets or connections strings, so why do I need to create a connection, or another table to then export what is already in use, working and visible?

Just need the equivalent of a "Right click -> Export, or "Right click -> Save As."


That help explain a little better?

Regards


Hello,

So you used the wizard.

I believe you dont need to open a connection again.

But cant u get a table object from the dataset? and then use that code from link?(http://www.c-sharpcorner.com/UploadFile/deveshomar/export-datatable-to-csv-using-extension-method/)

That method accepts a data table as an argument...

Unfortunately i dont have any demo to work with here.

But i believe that method in the link can solve your issue.
Cant you get a table from a dataset?

e.g Table t= DataSet.Tables("something")?

Then use the method from the link???

Please search Google on how to get a table from a dataset.

Ronald
Go to the top of the page
 
twigleaf
post Dec 24 2014, 04:40 AM
Post#7



Posts: 229
Joined: 18-September 03
From: Halifax, United Kingdom


Hi Ronald.

I looked at tablesets already from a couple of MSDN web links, and still working on how to get them to work. Either the code to display the CSV falls over, or most of the time I think the code to call the table drops off because it can't understand my attempts to alter it.

My lack of understanding of C# I think is more to blame.


Problem with the link you showed me, when I download it or recreate it, then you get "OperationsUtility" not known or "ToCSV" not known, so something breaks in those instructions. Adding to my confusion. Even if I just ran it from the download something breaks which suggests it wasn't designed for Visual Studio. So not sure if there is another C# program out there that would work.

Given Visual studio has nothing to export data by wizard or properties control I find it really odd that everything in C# is about looking at and filling-in data but there is no easy or clear way to get it back out afterwards. Access was so much easier in letting you get your data back.

I'll carry on looking at table sets as the direction to go in.

Regards
Go to the top of the page
 
twigleaf
post Dec 24 2014, 06:45 AM
Post#8



Posts: 229
Joined: 18-September 03
From: Halifax, United Kingdom


I think is the nearest match to what I am after as I understand how it grabs data and works with a button click. It is dumping a datagrid which I can put on a winform easy enough. But there is no error check for Null so falls over quickly.


Solution 2
http://www.codeproject.com/Questions/46172...iew-to-csv-file
Go to the top of the page
 
datAdrenaline
post Dec 29 2014, 11:09 PM
Post#9


UtterAccess Editor
Posts: 17,929
Joined: 4-December 03
From: Northern Virginia, USA


First thing to remember ... in Visual Studio you are not "bringing in" the Access Table Object. What the Wizard did for you is create a "DataSet" object that contains "DataTable" objects. The DataTable objects are C# classes that waiting to be instantiated and populated with data from your Access Table object. The way your DataTable objects get populated is by connecting to your Access Table Object and reading data into memory. The code snippet you provided can be interrogated to see what C# is actually doing. To interrogate those methods and procedures you can do a right click on them, then choose "Definition".

When you do that you will see that the Fill() method creates and opens a connection to your database, then issues a SELECT command through a DataAdaptor, then pumps all that data from the DataAdaptor into your in-memory DataTable object, then the connection to your database closes -- you are now disconnected from your database. When you make changes to the data in your DataTable object, those changes are not written to your Access Table Object until you tell the DataAdapter to update all the data. The in-memory collection of data in the DataTable that has changed, been deleted, or inserted will be written to the Access Table Object. There is some other magic that happens too, that that is the gist of what is happening.

As a point of note, the Wizard --- while pretending to be your friend --- will often get you in bind, especially when you are trying to switch from an Access UI perspective to C#/Visual Studio. I know --- I've been there --- I am extremely well versed in Access, and when I expanded to C# it was a bit of a mind jarring experience. The biggest thing to understand is that C# (Visual Studio) works off of a "disconnected" approach to data. That means, your code will read the data, pump the data into a collection of a Class (DataTable is a collection of DataRows; there are other 'collections'). You then work with the Classes holding your data, then push the changes BACK to your repository -- in C#, you are responsible for everything (even if VS generates the code for you). However, with Access, the mindset is a persistently bound connection to the data --- you can view the raw table data; make changes and Access will save changes for you. Forms can be bound to your Field of a Table object --- its awesome! But Access is just hiding all the plumbing of the CRUD operations because as an Access user, you just want to work with your data.

I bring all this up because when I hear you say "I used the wizard in Visual Studio to bring in the table" or "Given Visual studio has nothing to export data by wizard or properties" I want to re-enforce that Visual Studio is not a UI wrapper to a database engine like Access is. Visual Studio is a development environment in which you develop that wrapper, so you are in control of whatever you want to do.
Go to the top of the page
 
twigleaf
post Jan 5 2015, 10:19 AM
Post#10



Posts: 229
Joined: 18-September 03
From: Halifax, United Kingdom


Hi DatAdrenaline.

That helps greatly in understanding. I am no clearer in being able to export my table, but some of the threads I have read make more sense as to why it brings in and recreates what I thought was already in there to start with.

Regards
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 12:14 PM