How to read particular rows from a csv file

Question

0.00/5 (No votes)

See more:

My sample.csv file looks like this:

ID Name Loc Sal
1 abc usa 2342
2 xyz usa 4567
3 qwe Europe 7864
4 empty row
5 asd Europe 4567

Now I want to write all this data except the rows contains loc as "usa" to new.csv file. In my vb.net winform application I coded for writing all the rows to a new file but haven't got clue to filter the rows. I am not sure wether I can use same logic for this problem or not? Any suggestions please?

VB

Dim ioFile As New System.IO.StreamReader("C:\sample.csv")
 Dim ioLine As String
 Dim ioLines As String
 ioLine = ioFile.ReadLine
 ioLines = "ID,Name,Number,Amount"
 ioLines &= vbCrLf & ioLine
 While Not ioLine = ""
    ioLine = ioFile.ReadLine
    ioLines = ioLines & vbCrLf & ioLine
 End While
 Dim ioWriter As New System.IO.StreamWriter("C:\new.csv")
 ioWriter.WriteLine(ioLines)
 ioFile.Close()
 ioWriter.Close()

Posted 19-Jun-11 23:32pm

Member 8001800

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Simon_Whale · Accepted Answer · 2011-06-20T00:03:00

Solution 1

You need to test each line for the USA in the location, I personally would use the split function to create an array so that you know you are testing the location value and not finding the value at any location in the line that you have read using ioLine = ioFile.ReadLine

VB

while not ioLine = ""
  ioLine = ioFile.ReadLine
  dim Values as string() = ioLine.split(" ")
  if not values(2) = "usa" then
    iolines += system.environment.newline & ioline
  end if
end while

Documentation on String.split: String.split function[^]

Posted 20-Jun-11 0:03am

Simon_Whale

Comments

Member 8001800 20-Jun-11 6:35am

if i use this I am getting an error message states "Null Reference Exception was unhandled" points at dim Values as string() = ioLine.split(" ")

Simon_Whale 20-Jun-11 6:37am

When it errors does ioLine have any values?

Member 8001800 20-Jun-11 6:43am

it doesn't hold any values

Simon_Whale 20-Jun-11 6:45am

you need to create a test to make sure the line isn't empty before doing a split

Member 8001800 20-Jun-11 7:05am

Thank you, its working fine. Additionally, i need to delete empty rows as well. For this I modified above code slightly at, if not values(2) = "usa" or values(2)=" " then But couldn't able to delete empty rows. Any ideas?

Simon_Whale 20-Jun-11 7:09am

change it too

if not values(2) = "usa" orelse not values(2) = " " then

Member 8001800 20-Jun-11 7:22am

Its not working if i put both conditions in a single statement. I done in the following way

If Not Values(5) = "" Then
If Not Values(5) = "KVARH" Then
ioLines += System.Environment.NewLine & ioLine
EndIf
EndIf

Then it is working well

Simon_Whale 20-Jun-11 7:24am

Glad you got it works :)

Member 8001800 20-Jun-11 7:32am

Is there any way that I can read particular columns only. For example, I need ID,Name,Loc columns. I don't want sal column.

Simon_Whale 20-Jun-11 7:36am

Yes, with the split function it has made values into an array of string. So you would build up your string by contatinating the columns that you want.

i.e.

dim Output as string = values(0) & " " & values(1) & " " & values(2)

Member 8001800 20-Jun-11 11:30am

Bit more tricky and extension for the previous task. If I have few rows on top of the headers and i want to exclude them being written to.
ex:
------- title of the sheet------
===================================
Welcome
<empty row="">
ID Name Loc Sal
1 abc usa 2342
2 xyz usa 4567
3 qwe Europe 7864
4 empty row
5 asd Europe 4567

How to write from the headers only by excluding additional rows on top of the headers?

Simon_Whale 20-Jun-11 11:39am

you will need to perform a logical test after the ioLine = ioFile.readline such as

dim SetWriteToFile as boolean = false

if ioLine.substring(0,2) = "ID" then SetWriteToFile = true

if SetWriteToFile = true then
'do the other checkes here / write to file here
end if

Member 8001800 20-Jun-11 12:50pm

If I do this way, I could not read the next rows after Headers as they don't have string "ID".

Member 8001800 20-Jun-11 13:02pm

Here is the whole code:

Dim ioFile As New System.IO.StreamReader("C:\sample.csv")
Dim ioWriter As New System.IO.StreamWriter("C:\new.csv")
Dim ioLine As String
Dim ioLines As String
Dim writeToFile As String = False
ioLine = ioFile.ReadLine
ioLines = "ID,Name,Number,Amount"
ioLines &= vbCrLf & ioLine
While Not ioLine = ""
ioLine = ioFile.ReadLine
If ioLine.substring(0, 2) = "ID" Then
Dim values As String()
values = ioLine.Split(",")
Dim outPut As String
outPut = values(0) & "," & values(1) & "," & values(2)
If values(2) = "usa" Then
If values(2) = "" Then
ioLines = ioLines & System.Environment.NewLine & outPut
End If
End If

End If
End While

ioWriter.WriteLine(ioLines)
ioFile.Close()
ioWriter.Close()

Member 8001800 20-Jun-11 13:50pm

it is solved now. But i would like to know how to use escape character.
For example
ID,Name,Loc,Sal
1,abc,usa,2342
2,xyz,,usa,4567
3,qwe,Europe,7864
4 empty row
5,asd,,Europe,4567

If you observe 2nd and 5th rows, name column values contain "," as part of a name. So If i want to split this file using above code what are the possible changes have to made?