Solved

Import CSV file vb.net

Posted on 2011-03-14
7
1,485 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Iteration Help (Asp.net VB) 5 24
How to limit User Input 2 27
Updating specific fields only in MVC/Entity controller 2 18
Access/Visual Basic Question 3 25
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…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 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