extra lines in csv export file created by carriage return

[Cpreston]

Hi

I have created an export from our sql database and it imports into a csv file.

I have come across an issue when the customer special instruction field as more than one line, the export into CSV is creating an extra line which then throws out all the other fields.
Is there a way to keep it in all one rather than it create a new line. I have looked in to the sql field and it is a carriage return. I assume it thinks the return is a comma.

Any ideas how to stop this happening please.

Thanks

 

[fredericofonseca]

not necessarily an issue.

Before you go cleaning data how is your extract file defined. e.g. did you define it as having a cr+lf row separator or just as a cr? if the last one then you will need to either define the file as being fixed length, clean the data or change your row delimiter to be cr+lf.

if your row delimiter is cr+lf then you need to confirm that whatever software will be reading the file can deal with a single row delimiter (as opposed to treat both CR or CR+LF as valid row delimiters). If it can then you don't have a problem.

If none of the above is a possible solution then data cleaning may be your only option - however depending on what is the final destination of the file other options may be available. But without knowing which is the destination I will not advance any of the possible options.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

HI

Thanks for the reply. Here is the information I think you require

Extra Field settings have a Flat File destination with ColumnDelimeter of {CR]{LF}, column Type of Delimited and Data Type of string [DT_STR]
They are exporting to a CSV file in Excel 2010

Thanks

 

[Cpreston]

Hi

I have tried to change the columndelimeter to comma, but I get a message saying

Property value is not valise and in the details The row delimiter cannot be the same as the column delimiter

All the other columns are set to columndelimter of comma {,} so do know what to do, any ideas please.

Thanks

 

[fredericofonseca]

Excel will be able to read that carriage return as a content of the field instead of a row delimiter as long as the field containing it is defined as being text qualified.

This means that the contents of the field should be delimited by a character that defines it as text - default value is a double quote.

On your SSIS package on the general tab you can define the qualifier to use - by default is set to <none> so if you put a double quote on it it will be used.

Then in each column on the advanced tab you need to ensure that that particular field is set to "TextQualified" = true. this is the default but may have been changed by you.
You may also wish to change all the other ones not to be true.

The above will allow the file to be loaded onto Excel correctly.

This leaves you with the business use of that data. Once it is loaded onto Excel it will not look like it has that Carriage Return on the contents and depending on what the users are then using this data for it may cause issues.

So your next step is asking the users what they wish to do on these situations as you may need to do a bit of manipulation and/or data cleaning on your SSIS package or even at the source (although from what you say that field is for this is not feasible).

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

Sorry so in General I have

Format : Delimeted
Text Qualifier: " " (not sure what you meant by double quotes
Header Row Delimter: {cr}{L:F}

in Advance all fields are set to textqualified = True

I have tried the above and all it does is put "" after all fields entry's. Sorry fairly new to this so having problems understanding some of it.

Thanks

 

[fredericofonseca]

single quote = '
double quote = "
so on the text qualifier box you stick one "

sample output will look as follows (where <cr> is the carriage return you mentioned
"customer sale<cr>second line",123456
"customer sale",33445566


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

No doesn't seem to work. I should have 7 rows, where I am getting 9. example below is what is happening (ignore Row 1 and Row 2 this is just to explain)


Row 1. ''GR1096'' ''Grant & Stone Limited (NBG)'' ''London Road'' ''Wheatley'' ''Oxford'' ''OX33 1LH'' ''Midlands'' ''GR1096'' ''3.57220000'' ''1929.0000'' ''2016-03-09 14:07:00'' ''26/200032'' ''TO BE DELIVERED MONDAY 14TH MARCH PLEASE
Row 2. MUST HAVE 24HRS NOTICE''

The last part ''TO BE DELIVERED MONDAY 14TH MARCH PLEASE
Row 2. MUST HAVE 24HRS NOTICE''

Should be all together as below. This as you can see is going into another row.

TO BE DELIVERED MONDAY 14TH MARCH PLEASE MUST HAVE 24HRS NOTICE''

The file also needs to be a CSV and in this case I do not see any commas.

I probably am misunderstanding it all. Thanks

 

[fredericofonseca]

if it is not a csv that is because you changed the delimiters manually e.g. messed up.

and yes when you open with notepad for example you will see the extra lines where you have a Carriage return - but as I said on previous post if the program you use does not allow for a single type of delimiter per file you get that issue. Excel does allow for it and that is what the solution I gave you is for.

so delete your file connection.

Go to your data flow, edit the destination and choose a new file connection
add it again and specify you wish it to be delimited - choose comma as the delimiter.
then on the text qualifier box you stick one "
Define first row contains header if you need

That should be it as creating the file connection from the data flow will set the column names and sizes correctly - but change types ans sizes if required.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

I think it is doing what it should, it just is putting a carriage return at the end of each row. I have exported to a txt file and I now get this.
Each field is separated by a comma. the only 2 things seem strange are

1. The TO Be delivered etc... txt still goes down to another line
2. If the last field is empty then we still have a , after the date

What do you think?

NI9,John Nicholls (Trading) Ltd,Units 11-12, Somerville Court,Banbury Business Park, Adderbury,Banbury,OX16 4TB,Midlands,NI9,.94500000,510.0000,2016-03-08 16:05:00,02/272934,
NI9,John Nicholls (Trading) Ltd,Units 11-12, Somerville Court,Banbury Business Park, Adderbury,Banbury,OX16 4TB,Midlands,NI9,28.69479000,17994.0000,2016-03-09 10:44:00,02/273073,
GR1096,Grant & Stone Limited (NBG),London Road,Wheatley,Oxford,OX33 1LH,Midlands,GR1096,3.57220000,1929.0000,2016-03-09 14:07:00,26/200032,TO BE DELIVERED MONDAY 14TH MARCH PLEASE
MUST HAVE 24HRS NOTICE
PI2001,Norman Piette Ltd (FOR),CHANNEL SEAWAYS LTD,NEW QUAY,POOLE,BH15 4AJ,South and South East,PI2001,6.49305405,6614.0000,2016-03-10 11:01:00,33126,
PI2001,Norman Piette Ltd (FOR),CHANNEL SEAWAYS LTD,NEW QUAY,POOLE,BH15 4AJ,South and South East,PI2001,1.27940000,691.0000,2016-03-10 11:10:00,33075,TO BE DELIVERED MONDAY 14TH MARCH PLEASE
MUST HAVE 24HRS NOTICE
NI9,John Nicholls (Trading) Ltd,Units 11-12, Somerville Court,Banbury Business Park, Adderbury,Banbury,OX16 4TB,Midlands,NI9,.22800000,123.0000,2016-03-10 14:12:00,02/273295,

 

[fredericofonseca]

you are missing the textdelimiter

regarding the last field being empty that is correct. you get a "," for each field - 1 so you could have a line like
NI9,John Nicholls (Trading) Ltd,,,,,,,,NI9,.94500000,510.0000,,,


On a piece of advise always use tab as the delimiter. if you don't then always make sure you get the textdelimiter set otherwise if you have a Comma as a separator and one of your text fields contains a Comma you will have problems.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi
Yes sorry I did not set the txtdelimeter. I now have this result, is this how you would expect to see it?

''NI9'',''John Nicholls (Trading) Ltd'',''Units 11-12, Somerville Court'',''Banbury Business Park, Adderbury'',''Banbury'',''OX16 4TB'',''Midlands'',''NI9'',''.94500000'',''510.0000'',''2016-03-08 16:05:00'',''02/272934'',''''
''NI9'',''John Nicholls (Trading) Ltd'',''Units 11-12, Somerville Court'',''Banbury Business Park, Adderbury'',''Banbury'',''OX16 4TB'',''Midlands'',''NI9'',''28.69479000'',''17994.0000'',''2016-03-09 10:44:00'',''02/273073'',''''
''GR1096'',''Grant & Stone Limited (NBG)'',''London Road'',''Wheatley'',''Oxford'',''OX33 1LH'',''Midlands'',''GR1096'',''3.57220000'',''1929.0000'',''2016-03-09 14:07:00'',''26/200032'',''TO BE DELIVERED MONDAY 14TH MARCH PLEASE
MUST HAVE 24HRS NOTICE''
''PI2001'',''Norman Piette Ltd (FOR)'',''CHANNEL SEAWAYS LTD'',''NEW QUAY'',''POOLE'',''BH15 4AJ'',''South and South East'',''PI2001'',''6.49305405'',''6614.0000'',''2016-03-10 11:01:00'',''33126'',''''
''PI2001'',''Norman Piette Ltd (FOR)'',''CHANNEL SEAWAYS LTD'',''NEW QUAY'',''POOLE'',''BH15 4AJ'',''South and South East'',''PI2001'',''1.27940000'',''691.0000'',''2016-03-10 11:10:00'',''33075'',''TO BE DELIVERED MONDAY 14TH MARCH PLEASE
MUST HAVE 24HRS NOTICE''
''NI9'',''John Nicholls (Trading) Ltd'',''Units 11-12, Somerville Court'',''Banbury Business Park, Adderbury'',''Banbury'',''OX16 4TB'',''Midlands'',''NI9'',''.22800000'',''123.0000'',''2016-03-10 14:12:00'',''02/273295'',''''

Good point regarding the comma, in our case some address fields may have. This file will eventually import into another software so the company running that software will be testing the exported file as such.

 

[fredericofonseca]

not quite.

you entered a single quote ' twice as your delimiter instead of a double quote "
not the same '' <> "

apart from that it is what I would expect to see.

And... I asked what was the software that was going to read the file. This means the final destination of the file, not a intermediary one for internal verification.

Speak with the other company and see if they will accept the textidentifier and also if whatever software they will be using to process the file will deal with the carriage return on the middle of the text.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

Oh yes silly me I change the txtdelimeter to " and I get this now, which looks better. I will talk with the company about the comma situation. I am also going on a SSIS course in 2 weeks time so hopefully my skills will improve. Thanks for your patience and help.

"NI9","John Nicholls (Trading) Ltd","Units 11-12, Somerville Court","Banbury Business Park, Adderbury","Banbury","OX16 4TB","Midlands","NI9",".94500000","510.0000","2016-03-08 16:05:00","02/272934",""
"NI9","John Nicholls (Trading) Ltd","Units 11-12, Somerville Court","Banbury Business Park, Adderbury","Banbury","OX16 4TB","Midlands","NI9","28.69479000","17994.0000","2016-03-09 10:44:00","02/273073",""
"GR1096","Grant & Stone Limited (NBG)","London Road","Wheatley","Oxford","OX33 1LH","Midlands","GR1096","3.57220000","1929.0000","2016-03-09 14:07:00","26/200032","TO BE DELIVERED MONDAY 14TH MARCH PLEASE
MUST HAVE 24HRS NOTICE"
"PI2001","Norman Piette Ltd (FOR)","CHANNEL SEAWAYS LTD","NEW QUAY","POOLE","BH15 4AJ","South and South East","PI2001","6.49305405","6614.0000","2016-03-10 11:01:00","33126",""
"PI2001","Norman Piette Ltd (FOR)","CHANNEL SEAWAYS LTD","NEW QUAY","POOLE","BH15 4AJ","South and South East","PI2001","1.27940000","691.0000","2016-03-10 11:10:00","33075","TO BE DELIVERED MONDAY 14TH MARCH PLEASE
MUST HAVE 24HRS NOTICE"
"NI9","John Nicholls (Trading) Ltd","Units 11-12, Somerville Court","Banbury Business Park, Adderbury","Banbury","OX16 4TB","Midlands","NI9",".22800000","123.0000","2016-03-10 14:12:00","02/273295",

 

[jjefferson]

If you are able to modify your dataset query and it is acceptable to have a space where this CrLf is in your data, you might also try something like:

REPLACE(yourColumnName, CHAR(13)+CHAR(10), ' ')

This will put all your text into one "line" by replacing a CrLf pair with a single space. You will have to determine if this is acceptable to the receiving party.

-Jim-