0

I'm having some issues dealing with commas on a export from a datatable to a CSV flat file using SSIS and SSMS. Some values on the table have commas 'e.g. Enterprise, S.A.' and the export wizard separated that value on another column, 'Enterprise' and 'S.A.'is there any solution to ignore that comma or change the type of delimiter to keep the data together?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
LuisCas
  • 45
  • 8

1 Answers1

1

You can use SSIS to send the data to a flat file destination and use a delimiter other than a comma to distinguish columns. When creating a Flat File Connection Manager, choose the Delimited format and in the Advanced pane change the ColumnDelimiter property for each column from a comma to another delimiter. The Fixed Width and Ragged Right formats are options as well.

userfl89
  • 4,610
  • 1
  • 9
  • 17
  • when i change the delimiter to another one, the nexts columns keeps in one column leaving nexts empty – LuisCas Oct 19 '18 at 15:11
  • I'm not sure if I totally understand your comment, but have you changed the delimiter for all of the columns? – userfl89 Oct 19 '18 at 15:20
  • yes, i selected semicolon ; to all the columns, and they agroup in one single cell per colum, leaving the others empty. The program does not find the limiter ; so it does no delimit and separate. t – LuisCas Oct 19 '18 at 15:23
  • Did you create the flat connection manager based on the source output within the data flow task or independent of this? – userfl89 Oct 19 '18 at 15:29
  • i dont know if i undertand this. but i create a OLE DB Source that have a query that extract the data from a table, then i send that to a Flat File destination that saves the results into CSV – LuisCas Oct 19 '18 at 15:41
  • Yes, create the OLE DB source, then in the data flow task add a flat destination, connect the OLE DB source to the flat file destination, and in the flat file connection manager field press new and create the output file based off the columns from the OLE DB source. – userfl89 Oct 19 '18 at 15:57
  • Oh, man. I needed to create a new one, thats why doen not works before. I create new csv and new connectors and it works! thanks dude! Now my fields are correctly separated :D – LuisCas Oct 19 '18 at 16:16