3

I have a flat file that with comma as a delimiter. In one address column I have . My issue is that one of the fields in my file contains "," (commas), so when the file is created it creates new columns because the field is "," comma delimited on the column. Is there anyway to get round this without having to get rid of commas in my file (which I've tried and works)?

Example:

store_name , phone , email , street , city , state , zip_code
Santa , (831) 476-4322 , santacruz@bikes.shop1 , 3700, Portola Drive1 , Santa Cruz1 , CA1 , 950601
Baldwin1 Bikes , (516) 379-8883 , baldwin@bikes.shop1 , 4200 Chestnut Lane1 , Baldwin1 , NY1 , 114321
Rowlett Bikes1 , (972) 530-5545 , rowlett@bikes.shop1 , 8000 Fairway Avenue1 , Rowlett1 , TX1 , 750881

Underline field is mainly creating the issue.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Nadeem Ahmed
  • 31
  • 2
  • Your example is not clear. You need to make sure you imported data have a "Text qualifier". – Adirmola Sep 03 '19 at 11:21
  • Your data has a varying number of columns. Your data needs to be well defined to be able to be read properly. If you look at your line under the headers, you have 8 columns, where as in the others you have 7. – Thom A Sep 03 '19 at 11:26
  • This is why i require the data to either be wrapped inside of double quotes or that they use a Pipe (|) or Tilde (~) delimiters – Mark Kram Sep 03 '19 at 11:56
  • What you have is some bad data... that first row has a comma in its street address. Having processed a lot of street addresses over the years, that's not common, except when it comes to secondary lines or bad data. Unfortunately bad data is all too common. The best way to handle it is as @Adirmola suggested and export the data with text qualifiers - IE, quoted text - if you can. If you can't... there's not a whole lot you can do. – TechGnome Sep 03 '19 at 12:38
  • Comma is the worst possible delimiter. If possible, get this file created with a different delimiter. pipe or tab are the usual picks. As is, there is no way to tell what isvalid comma and what isn't, unless you always assume that only that last field will have commas. – Nick.Mc Sep 03 '19 at 12:39
  • Why don't you use XML format as source? – Amira Bedhiafi Sep 03 '19 at 13:38
  • IMHO, after comma delimited files, XML is the next worse for tabular. Why use a needlessly verbose data format? – Nick.Mc Sep 05 '19 at 05:42
  • The commas within the fields will mislead my SSIS package to understand that file row has more columns than previously said! How to resolve this? Eg: Name,Amount,Address Me,50,000,My Home,India you,53,300,Your Home,Where here only 3 columns exist but SSIS assumes all commas used to separate fields;Actually not all.Amount Column and Address Column have extra commas. can you please upload a video on this? – Nadeem Ahmed Sep 06 '19 at 07:00
  • No one is going to produce a video for you. Please don't ask. Please instead read the answer below and try and understand. The only way to fix this is to pre process it. And that will only work if you know that commas only appear in the last field. Can you confirm if this is the case? – Nick.Mc Sep 09 '19 at 13:07
  • Thanks a lot, I just wanted to know if there is any built-in way to fix this but now my doubts are cleared. I resolved this issue by using C# code in the script task. – Nadeem Ahmed Sep 10 '19 at 14:28

1 Answers1

0

You have to handle the character . while exporting the data from your Datasource. YOu can escape or replace the character . with some character which not going to be used anywhere else. If you can't find such a character, then you can go for Pair Replacement (i.e. replace . with !!). You have to replace the , inside value fields only. Once you complete preprocessing the flat file, you can import the file as csv data. You only need to pre-process the flat file before importing it.

Kiran Maniya
  • 8,453
  • 9
  • 58
  • 81
  • The commas within the fields will mislead my SSIS package to understand that file row has more columns than previously said! How to resolve this? Eg: Name,Amount,Address Me,50,000,My Home,India you,53,300,Your Home,Where here only 3 columns exist but SSIS assumes all commas used to separate fields;Actually not all.Amount Column and Address Column have extra commas. can you please upload a video on this? – Nadeem Ahmed Sep 06 '19 at 07:02