Edit a CSV file with SSIS

R

rongrace

Member
Joined
Jul 2, 2012
Messages
23
Programming Experience
Beginner
I receive a number of csv files from a third party on a regular basis. The records all end with a comma which causes a problem when I try to load in to a sql table as sql expects another column. Is there a quick and easy way of removing the character from each record of each file. I assume it will be some sort of sql task within a foreach loop. The files I receive will never have the same names and there could be 1 or there could be 20, hence my need to loop through all files. Any help would be greatly appreciated
 
Sort by date Sort by votes
Hi,

I have never used SSIS but if you need to modify your file structure first to work with SSIS then you can use a StreamReader to read your file line by line, using ReadLine, then use SubString of the String class to remove the last character in the line that has been read, being your comma, and then use a StreamWriter to re-write your modified file.

Hope that helps.

Cheers.

Ian
 
Upvote 0 Downvote
Hi Ian,
Thanks for the response.

As you may have guessed I'm quiet new to all this so I may ask some obvious questions.

I had a search around for streamreader examples, but it got a bit confusing. I attempted a bit of code but not sure if it will work. What I've tried is to read in the complete file using a variable (User::FilenName) that will hold a list of possible filenames and this will sit within a for each loop, then get the length of each record and if the right most character is a comma then get a substring of this line. I will then need to write this back to the same file. I take it there's a stream writer command too

Public Sub Main()
'
' Store the line in this String.
Dim TextLine As New IO.StreamReader("User::Filename")

' Write the line we read from "::FileName"
Dim Line As String, comma As String
comma = ","
Do Until EOF()
If Right(Line, 1) = comma Then
Line = Left(Line, Len(Line) - 1)
End If
Loop

End Sub
 
Upvote 0 Downvote
Hi,

It seems that you are trying to go about this using old VB6 technology, and in addition to that, you never actually read the file so you are on a bit of a "Road to Nowhere" here.

Have a go with the following example. In this case, I read a file, test for the last character being a comma, and then re-write the file as needed. i.e:-

VB.NET: 
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
  Const CommaChar As Char = ","c
 
  Using myReader As New StreamReader("d:\temp\MyTextFile.txt")
    Using myWriter As New StreamWriter("d:\temp\MyConvertedTextFile.txt")
      Do While Not myReader.EndOfStream
        Dim myRecord As String = myReader.ReadLine
        If myRecord.EndsWith(CommaChar) Then
          myWriter.WriteLine(myRecord.Substring(0, myRecord.Length - 1))
        Else
          myWriter.WriteLine(myRecord)
        End If
      Loop
    End Using
  End Using
  MsgBox("Done!")
End Sub

Hope that helps.

Cheers,

Ian

PS. Please remember to use code tags when posting code for readability.
 
Upvote 0 Downvote
Ian,

it's looking good. The package errored but I'm pretty sure that's to do with a variable I set up incorrectly. Many Thanks for your help
 
Upvote 0 Downvote
rongrace said:
I receive a number of csv files from a third party on a regular basis. The records all end with a comma which causes a problem when I try to load in to a sql table as sql expects another column. Is there a quick and easy way of removing the character from each record of each file. I assume it will be some sort of sql task within a foreach loop. The files I receive will never have the same names and there could be 1 or there could be 20, hence my need to loop through all files. Any help would be greatly appreciated
Click to expand...
I dont work with SSIS much myself, but if you add a Flat File Source object to the diagram, you can specify which columns to include for the next step (which should be a Data Conversion object) which takes the source columns and inserts the records into the DB.
 
Upvote 0 Downvote
I've tried using the code with my own variables, but keep getting the error 'The path is not of a legal form'. When I step through the code in debug it errors on the myReader as new StreamReader line.

The variable in the code contain the following
LocalPath = C:\Users\gracero\Desktop\local\

My script code is as follows

VB.NET: 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Public [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Main()[/SIZE][SIZE=2]        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Const[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] CommaChar [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Char[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] = ","c[/SIZE]
[SIZE=2]       
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Using[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] myReader [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] StreamReader([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"User::LocalPath"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"User::FileName"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]           
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Using[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] myWriter [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] StreamWriter([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"User::CorrectedPath"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"User::FileName"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]                
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Do[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Not[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] myReader.EndOfStream[/SIZE]
[SIZE=2]                    
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] myRecord [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] = myReader.ReadLine[/SIZE]
[SIZE=2]                  
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] myRecord.EndsWith(CommaChar) [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]                        myWriter.WriteLine(myRecord.Substring(0, myRecord.Length - 1))                  [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Else[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]                        myWriter.WriteLine(myRecord)
                    
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]                
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Loop[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]            
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Using[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Using[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2]     
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000][/COLOR][/SIZE][/COLOR][/SIZE] 
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000][/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]        Dts.TaskResult = Dts.Results.Success

    
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub
[Code/]

If I hard code the path and filename in to the code as variables the task completes without any problem

[Code]
        Const CommaChar As Char = ","c
        Dim filepath As String
        filepath = "C:\users\gracero\desktop\local\"
        Dim filenames As String
        filenames = "Processed Documents Report_2012_11_16_14_18_12.CSV"
        Dim corrpath As String
        corrpath = "C:\users\gracero\desktop\local\corrected\"
        Using myReader As New StreamReader(filepath & filenames)
            Using myWriter As New StreamWriter(corrpath & filenames)
                Do While Not myReader.EndOfStream
                    Dim myRecord As String = myReader.ReadLine
                    If myRecord.EndsWith(CommaChar) Then
                        myWriter.WriteLine(myRecord.Substring(0, myRecord.Length - 1))
                    Else
                        myWriter.WriteLine(myRecord)
                    End If
                Loop
            End Using
        End Using
[Code/]
[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE][/COLOR][/SIZE]
 
Upvote 0 Downvote
Hi,

This must be some syntax that I am not familiar with in .NET:-

VB.NET: 
"User::LocalPath" & "User::FileName"

The way I would get the current users Desktop folder would be:-

VB.NET: 
Dim filePath As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
MsgBox(filePath)

You then just need to add any sub folders you want to access and the file to be used.

Hope that helps.

Cheers,

Ian
 
Upvote 0 Downvote
If you're not familiar with 'User::LocalPath' how would you cater for a variable. I don't want to hard code a path into the script as this would then require the localpath folder that I'm accessing to always be in the same location i.e. on my desktop, but on thelive machine it may be somewhere different, hence the reason I want to use variables.

Hope this makes sense
 
Upvote 0 Downvote
If you're not familiar with 'User::LocalPath' how would you cater for a variable. I don't want to hard code a path into the script as this would then require the localpath folder that I'm accessing to always be in the same location i.e. on my desktop, but on thelive machine it may be somewhere different, hence the reason I want to use variables.

Hope this makes sense
Click to expand...

No, not really.

Are you saying that you want to access an Environment Variable that has been defined on a particular machine? if so then you can use GetEnvironmentVariable. i.e.:-

VB.NET: 
Dim strComputerName As String = Environment.GetEnvironmentVariable("COMPUTERNAME")
MsgBox(strComputerName)

Cheers,

Ian
 
Upvote 0 Downvote
I have a number of package variables
Screendump.jpg
 
Upvote 0 Downvote
I couldn't access that web page because of the company internet access policy, but I managed to eventually work out what was required, I had to declare the variables and convert them

VB.NET: 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Const[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] CommaChar [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Char[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] = ","c
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] filename [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]CStr[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2](Dts.Variables([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"FileName"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]).Value)[/SIZE]
[SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] corrpath [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]CStr[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2](Dts.Variables([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"CorrectedPath"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]).Value)[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] localpath [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]CStr[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2](Dts.Variables([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"LocalPath"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]).Value)[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Using[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] myReader [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] StreamReader(localpath & filename)[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Using[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] myWriter [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] StreamWriter(corrpath & filename)[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Do[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Not[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] myReader.EndOfStream[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] myRecord [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] = myReader.ReadLine[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] myRecord.EndsWith(CommaChar) [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2]                        myWriter.WriteLine(myRecord.Substring(0, myRecord.Length - 1))
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Else[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2]                        myWriter.WriteLine(myRecord)
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Loop[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Using
[Code/][/COLOR][/SIZE][/COLOR][/SIZE]
 
Upvote 0 Downvote

Similar threads

T
  • Question
Reading json from file
Replies
1
Views
327
jdelano
J
F
  • Question
All vb2010.net files ara corrupted
Replies
7
Views
1K
jdelano
J
Z
  • Question
Resolved read bytes from middle of file?
Replies
12
Views
2K
cjard
cjard
Z
  • Question
OpenFileDialog1.Filter For A Specific File, Not File Type(s)
Replies
5
Views
1K
JohnH
JohnH
D
  • Question
Question Remote Window mouse control and screen recording to image file
Replies
4
Views
2K
jdelano
J

Share this page

Latest posts

Back
Top