Question Help with importing data from a comma seperated .csv file into a data-table

[alex101fishing]

Good evening to all!

I'm still very new to vb.net, truth be told to any object orientated programming, I am more used to working with C in PIC's or assembler or even VHDL.

I have managed to learn quite a lot using visual studio 2010 Pro however I have come against a small problem that I am sure one of the fine programmers out there will know the answer to.

To bring you up to date I have created a program that can decrypt an encoded RS-232 signal that's is being transmitted from 4 separate amplifiers via polling through the various address's. The amplified signals are coming from various sensors around a test rig. The program can basically read all of the data readings and show them on screen and then systematically record the data at set intervals into a .csv file. Originally I had got it to plant the data into an .xlsx excel file. However although this method worked it was quite demanding for the computer to have to keep opening and closing excel to update the stored data. Hence I moved to the .csv comma separated file that can still be opened in excel just with less hassle.

Anyway back to the point, the program may sometimes be used on terminals that don't have access to excel or any other form of freeware spreadsheets (ex. open office). Thus I thought it would be a great idea for the user to be able to click a button that would open up a new form showing a data table, and plop in all the data that had been stored in the .csv file into it.

I have been scouting around the web and have found many threads talking about manipulating the columns and rows of data tables where the data from a .csv file has already been imported however I have not found a good example bit of code showing how to do the basics. I wondered if some kind soul could post a couple of lines of code very simply showing how to literally grab the data from the .csv file and populate the table. Once I have that I will be quite happy playing around with organising how it is displayed/ manipulated etc

A big thank you in advance!

Kind Regards

Alex

 

[jmcilhinney]

There are two primary options:

1. Use ADO.NET. You can treat a CSV file like a database table and the folder that contains it like a database. Any ADO.NET information you have read before, like for Access or SQL Server, is relevant because it's done basically the same way. You can use OleDb, create a connection and a data adapter to populate the DataTable. You can find the appropriate connection string at:

ConnectionStrings.com - Forgot that connection string? Get it here!

2. Use a TextFieldParser. The documentation for the class provides a code example for reading data from a CSV file. You'd need to create the columns and rows of the table yourself.

 

[alex101fishing]

Thank you for replying I shall have a look into each of these options and tell you how it goes

 

[alex101fishing]

Hey buddy,

I gave it a fling, I have managed to get it to read from the .csv file and populate the data into the datatable, however it seems to throw everything into only the first coloumn rather than spacing it out where I have set the commas into adjasent rows. Below is what I have achieved to date, thanks for the help!

[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] 
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Using[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] MyReader [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] Microsoft.VisualBasic.FileIO.[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]TextFieldParser[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2](Whole_Address)[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]
            MyReader.TextFieldType = FileIO.FieldType.Delimited
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] 
            MyReader.SetDelimiters(",")
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] currentRow [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]()[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Not[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] MyReader.EndOfData[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]         
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]                    currentRow = MyReader.ReadFields()
                
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] currentField [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] currentField [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] currentRow[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#008000][FONT=Consolas][SIZE=2][COLOR=#008000][FONT=Consolas][SIZE=2][COLOR=#008000]'    MsgBox(currentField)[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]                        DataGridView1.Rows.Add(New String() {currentField})
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]              
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] ex [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] Microsoft.VisualBasic.FileIO.MalformedLineException[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]                    MsgBox([SIZE=2][FONT=Consolas][COLOR=#a31515]"Line "[/COLOR] & ex.Message & [COLOR=#a31515][COLOR=#a31515][COLOR=#a31515]"is not valid and will be skipped."[/COLOR][/COLOR][/COLOR])[/FONT][/SIZE]
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[FONT=Consolas][SIZE=2] 
[/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]       
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Using[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]

 

[alex101fishing]

scratch that by changing

[FONT=Consolas][SIZE=2]DataGridView1.Rows.Add(New String() {currentField})
[/SIZE][/FONT]

to

[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]DataGridView1.Rows.Add(currentRow)
[/SIZE][/FONT][/SIZE][/FONT]

it allows the data to be dropped into appropriate rows, however now it seems to repeat a lot of the lines several times....? Any ideas? Cheers

 

[jmcilhinney]

That's because you are doing this:

ForEach currentField In currentRow
'    MsgBox(currentField)
                        DataGridView1.Rows.Add(New String() {currentField})
Next

That says:

For each field in the current row of the input, create a row in the output containing the current field.

If an input row contains 10 fields then you are adding 10 rows with one field each instead of 1 row with 10 fields.

If you want to pass a String array to the Add method then you should take notice of this from your own code:

Dim [I]currentRow[/I] As [B][U]String()[/U][/B]

 

[alex101fishing]

Haha I beat you to that by 30 seconds! thanks for the help though, any ideas why it is repeating the rows?

scratch that by changing


Code:

DataGridView1.Rows.Add(New String() {currentField})
to

Code:

DataGridView1.Rows.Add(currentRow)
it allows the data to be dropped into appropriate rows, however now it seems to repeat a lot of the lines several times....? Any ideas? Cheers

Cheers Buddy

 

[jmcilhinney]

If you are still adding the rows in a loop then you're still adding one row of output for each field in one row of input. 10 input fields would still produce 10 output rows.

 

[alex101fishing]

of course! Now I understand thank you, got it going!

 

[alex101fishing]

Hope you don't mind, but as your here I want to ask 1 more question, is it possible to merge cells together? in the datatable? I would just exapnd the column width however it makes the data look badly formatted,

Thanks for all the help buddy!

 

[jmcilhinney]

You aren't using a DataTable. A DataTable is an in-memory data structure for storing data, not a control. You are using a DataGridview. It is possible to merge cells but it's not simple. You have to custom-draw the cells. The simpler option and, possibly more logical in your case, would be to merge the data. Just because there are N columns in the input doesn't mean that there has to be N columns in the output. You can process the data any way you want in between.

 

[alex101fishing]

Ah I see, that explains a lot actually. I do want to display all of the data as it pops up, in the end I just played around with the headers and changed the column width. Does the job!

 

[alex101fishing]

The next step for me was working out how to print the datagrid, which I have achieved with the code below:
I used the printDialog and PrintDocument functions.

This code manages to print what ever the user can see at any given time within the datagrid...however I have run into a little issue, because the datagrid will probably hold around 100 records I have to implement the vertical scroll bar, which is all well and good. But when I then print the datagrid it cuts the page off at the point shown on the screen rather than the whole datagrid. Hopefully that makes sense!

I wondered if you might have any thoughts on the subject? cheers

'''''Button 2:

PrintDialog1.showdialog()
PrintDocument1.Print()

'''''PrintDocument1 Sub

Dim Width as Integer = me.datagridview1.width
Dim Height As integer = me.datagridview1.height

Dim bm As New Bitmap(Width,Height)
DataGridView1.DrawToBitmap(bm, New Rectangle(0,0, Width, Height))
e.graphics.DrawImage(bm,0,0)

end sub

 

[jmcilhinney]

You need to draw the data as you want it to be displayed, not a rendering of what's displayed on screen. This might help:

DataGridPrinter - A class to print data grid in a nicely formatted way