Solved

Import CSV file vb.net

Posted on 2011-03-14
7
1,480 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cant save 3D 4 19
VB.Net - MemoryMappedFiles - Confirm receipt 2 19
vb.net winforms sizing/resolution? 4 31
vb.net and creating a class 5 16
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
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…

776 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