Is the a way in SSIS to add a comma to the end of each row of a csv file?

Question

Is the a way in SSIS to add a comma to the end of each row of a csv file?

DaveBriCam

SSCarpal Tunnel

Points: 4298
More actions
May 11, 2021 at 2:43 pm

#3881931

Is the a way in SSIS to add a comma to the end of each row of a csv file? C# Script? It's part of a package that loads csv files to SQL Server table... Thanks!

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Yes, a C# script could definitely do it.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 2

Something like this? I know this as written will not work:

StringWriter csv = new StringWriter();

csv.WriteLine(string.Format("{,},{LF}"));

foreach (var item in YourListData)
{
    csv.WriteLine(string.Format("{,},{LF}));
}

return File...

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Have a look at the first answer here: https://stackoverflow.com/questions/36116795/how-to-update-a-record-in-text-file

As long as your file is not too huge, that gets you pretty close.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 4

This line has to reference the file name as a literal: string[] lines = File.ReadAllLines("items.txt")....set up as a variable?:

    public void UpdateInFile(string modified,int id)
    {
       string[] lines = File.ReadAllLines("items.txt");
       for (int i = 0; i < lines.Length; i++)
       {
          string[] parts = lines.Split(',');
          if (Convert.ToInt32(parts[0]) == id)
          {
             lines = modified;
          }
       }
       File.WriteAllLines("items.txt", lines);
   }

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 5

Maybe I'm approaching this all wrong - the problem is my csv files will not load unless there's a comma at the end of each row. So maybe there's a way of manipulating my package, perhaps in the data flow part of the For Each Container, to force the package to ignore?

This reply was modified 3 years, 6 months ago by DaveBriCam.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

Why is the final comma needed? My CSV files don't need one.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 7

I'm not sure but the package fails if the comma is not there.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 8

Sounds like it is expecting (n+1) columns rather than the (n) that are there. Why not fix that, rather than modifying the input file?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 9

I checked the column count in SQL Server table and it matches the number of csv columns.

palandri Old Hand Points: 387 More actions · Answer 10

Are there any unmapped columns in your OLE DB or ABO destination? If so, that might get you pointed in the right direction.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 11

DaveBriCam wrote:

I checked the column count in SQL Server table and it matches the number of csv columns.

This is not the important thing. You need to look inside the package, using VS, and look at how the file connection has been set up.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 12

There probably are now as my original package corrupted and I had to make a copy. I'm trying to create a flat file connection manager and have for gotten how to do it so I don't have to pick a specific file as I have 14 csv files in my source folder that I'm looping through.

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 13

DaveBriCam

SSCarpal Tunnel

Points: 4298

May 11, 2021 at 5:50 pm

#3882004

Crisis avoided I restored a previous version.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 14

DaveBriCam wrote:

I checked the column count in SQL Server table and it matches the number of csv columns.

So - where is this failing and what is the failure message? I suspect it probably is failing when trying to read the file - most likely because the file specification is set to a specific number of columns and without that extra comma it isn't seeing the last field.

But - without any information on the actual error or where it is occurring, this is all just a guess. And as Phil pointed out - instead of trying to work around the issue it is much better (and often much easier) to fix the underlying problem.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs