Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Import CSV file vb.net

Posted on 2011-03-14
7
Medium Priority
?
1,507 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 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