?
Solved

Import CSV file vb.net

Posted on 2011-03-14
7
Medium Priority
?
1,498 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 1000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

764 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