INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

extra lines in csv export file created by carriage return

extra lines in csv export file created by carriage return

(OP)
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

RE: extra lines in csv export file created by carriage return

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: extra lines in csv export file created by carriage return

(OP)
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

RE: extra lines in csv export file created by carriage return

(OP)
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

RE: extra lines in csv export file created by carriage return

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: extra lines in csv export file created by carriage return

(OP)
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

RE: extra lines in csv export file created by carriage return

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: extra lines in csv export file created by carriage return

(OP)
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

RE: extra lines in csv export file created by carriage return

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: extra lines in csv export file created by carriage return

(OP)
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,

RE: extra lines in csv export file created by carriage return

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: extra lines in csv export file created by carriage return

(OP)
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.

RE: extra lines in csv export file created by carriage return

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: extra lines in csv export file created by carriage return

(OP)
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",""

RE: extra lines in csv export file created by carriage return

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-

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close