Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Import CSV into datagrid in VB.Net

Posted on 2012-08-17
6
Medium Priority
?
746 Views
Last Modified: 2012-08-21
Using the attached code to bring a csv into a datagrid in vb.net works, the only problem is that it only brings in numbers and dates but will not bring in strings.
Public-Sub-FindFiles.docx
0
Comment
Question by:jeremyj54
  • 3
  • 3
6 Comments
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38307353
It seems that the text driver is scanning the csv file and when it finds numbers or dates, then any strings (in the same column) are no longer accepted.

If the csv files are generated and it's ok to treat all data as text, you could enclose the values in quotes. Otherwise, it may be possible to override this in a schema.ini file.

Can you give an example of (a few rows) of one of your csv files?
0
 

Author Comment

by:jeremyj54
ID: 38307630
I have attached one of the files.
StabilityDetail-501501-1.csv
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38307661
Yup, so the problem is indeed that your fields are being recognised as numbers and dates (starting from line 5). I think what you would need to do is create a file schema.ini (either manually or, if the file names and contents can vary, in your code) containing the column definitions as all Char/Text columns, or use MaxScanRows=1 (pity you can't use that in the connection string) so that it just looks at the first line which contains mixed data so it won't be recognised as numbers/dates (if this is a typical file).

Another option altogether (especially if you just need all the text from the file and no filtering is done) is of course to read the file into the grid with a File/TextStream object.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:jeremyj54
ID: 38307724
That is all I need, how would I use that?
0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 2000 total points
ID: 38307784
a schema.ini file would look like this:
[StabilityDetail-501501-1.csv]
ColNameHeader=False
Format=CSVDelimited
MaxScanRows=1
CharacterSet=ANSI
1=Column1 Char
2=Column2 Char
3=Column3 Char
4=Column4 Char
5=Column5 Char
6=Column6 Char
7=Column7 Char
8=Column8 Char
9=Column9 Char
10=Column10 Char
11=Column11 Char
12=Column12 Char
13=Column13 Char
14=Column14 Char
15=Column15 Char
16=Column16 Char
17=Column17 Char
18=Column18 Char
19=Column19 Char
20=Column20 Char
21=Column21 Char
22=Column22 Char
23=Column23 Char
24=Column24 Char
25=Column25 Char
26=Column26 Char
27=Column27 Char
28=Column28 Char
29=Column29 Char
30=Column30 Char

Open in new window

Problem of course would be that you have to write this out every time if there is a possibility of variation in file names or number of columns (and in every directory). If the assumption about the first line always being obvious strings, not numbers or dates, then you don't need the column definition because now it's only looking at the first line (MaxScanRows=1).

Here's another way to do it, without an oledb connection, note that this breaks when values are quoted, which they never seem to be in your case.
You could add the data to the grid directly but I kept the DataTable because it makes handling the data in the rest of the program easier.
                    Dim DeltaData As New DataTable()
                    Using sr As StreamReader = File.OpenText()
                        While Not sr.EndOfStream
                            Dim vals As Object() = sr.ReadLine().Split(","c)
                            If DeltaData.Columns.Count = 0 Then
                                Dim cols(vals.Count - 1) As DataColumn
                                For c As Integer = 0 To UBound(cols)
                                    cols(c) = New DataColumn("Columns " & (c + 1)) ' vals(c)
                                Next
                                DeltaData.Columns.AddRange(cols)
                            End If
                            DeltaData.Rows.Add(vals)
                        End While
                    End Using
                    DataGridView1.DataSource = DeltaData.DefaultView

Open in new window

If you want the column names on line 4 you should really skip the first 3 lines (or show them differently. You could use the commented out "vals(c)" for that but you would have to do it on line 4.
0
 

Author Closing Comment

by:jeremyj54
ID: 38317197
Solution Worked great, thanks for the help
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question