Solved

Import CSV file vb.net

Posted on 2011-03-14
7
1,496 Views
Last Modified: 2012-05-11
Hi, i need to import a csv file in vb.net.
The data columns only start from ronw 4, the first 3 rows is some description information.
Is it better to import it into a stream reader or a dataset?
I first need to delete the first 3 rows in the csv file before processing the data

thanks
0
Comment
Question by:CraigLazar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 10

Expert Comment

by:Asim Nazir
ID: 35125910
Use dataset based approach. That is better.
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35126269
First use OPENROWSET OR OPENDATASOURCE in SQL Server to insert the contents of csv file into a temporary table.Then perform the deletion ( and other validations if required) and finally insert the data into main table.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35126417
It depends what you intend to do with it. You could read it all into memory by way of a string array, or you could read it line-by-line (skipping the first three lines) and then split out each line. You could also read it into a dataset, but you would need to do something about those first 3 rows first.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35126635
Method to read csv file through OPENROWSET in SQL Server.

Initial Configuration:-
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE with override
GO

Open in new window


Read:-
SELECT * from OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\;','select * from new.csv' )

Open in new window


where the full path of csv file is : C:\new.csv

0
 
LVL 4

Author Comment

by:CraigLazar
ID: 35128459
Hi Guys, thanks for the responces
For using SQL, it is not as easy, i have used the bulk insert command before but had to clean up the files before that works well. So i am not able to use the SQL commands to process the file. I am going to have to process the file in ,.net and insert each row into my sql table.
I have over 60 columns of data of which i am not processig all of them. This is driven off a paramters table which i check when processing which columns  to process

to carl_tawn
i wil lhave to clean up the first few rows, how would i read the data into a dataset?

thanks
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 250 total points
ID: 35128592
In that case I wouldn't even bother loading them into a DataSet. I'd just do a straight read using a StreamReader and parse/insert each line as I came to it. If you're not planning to hold the data in memory and manipulate the data as a set then loading it all into memory in one go is a waste of resources.

As far as reading goes, something like the following perhaps:
        Dim sr As New System.IO.StreamReader("c:\test.csv")
        Dim valid As Boolean = True
        Dim line As String

        For i As Integer = 1 To 3
            line = sr.ReadLine()
            If line Is Nothing Then
                valid = False
                Exit For
            End If
        Next

        If valid Then

            line = sr.ReadLine()
            Do
                '// parse the line

                line = sr.ReadLine()

            Loop While Not line Is Nothing

        End If

        sr.Close()

Open in new window

0
 
LVL 4

Author Closing Comment

by:CraigLazar
ID: 35144031
thanks a lot, got it going
cheers
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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