Save As csv Carraige Return issue

I have a large xlsx file with the last column containing text with carraige 
returns (CR).  When I save this xlsx as a csv file these interim CR are 
preserved so each record of data isn't continuous on a single line.

Example data:
f1, f2, f3, "data for field 4"
f1, f2, f3, "more data <carraige return>
for field 4"
f1, f2, f3, f4

I discovered this by opening the csv file in word with paragraph markings 
turned on and I can see these paragraph marks.

Is this a 'feature' of Excel?  How can I save a CSV without these pesky 
returns?  I am using this csv in an import routine on another program and it 
fails without manual corrections.

Your help is appreciated...kelly
0
Utf
5/5/2010 3:35:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
3094 Views

Similar Articles

[PageSpeed] 2

Why not just remove the carriage returns before saving as a CSV file? I 
adapted the following code from a reply Dave Peterson made to a similar 
question today in the Excel Programming group:

Sub Remove_CR_LF()
'select any cell in the column to be processed before running macro
Dim myRng As Range
Set myRng = ActiveCell.EntireColumn
myRng.Replace What:=vbCr, Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
myRng.Replace What:=vbLf, Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Set myRng = Nothing
End Sub

Depending on your data, you may want to replace the vbCR or vbLF with a 
space instead of an empty string.

Hope this helps,

Hutch

"WaterBug" wrote:

> I have a large xlsx file with the last column containing text with carraige 
> returns (CR).  When I save this xlsx as a csv file these interim CR are 
> preserved so each record of data isn't continuous on a single line.
> 
> Example data:
> f1, f2, f3, "data for field 4"
> f1, f2, f3, "more data <carraige return>
> for field 4"
> f1, f2, f3, f4
> 
> I discovered this by opening the csv file in word with paragraph markings 
> turned on and I can see these paragraph marks.
> 
> Is this a 'feature' of Excel?  How can I save a CSV without these pesky 
> returns?  I am using this csv in an import routine on another program and it 
> fails without manual corrections.
> 
> Your help is appreciated...kelly
0
Utf
5/5/2010 7:20:01 PM
None of this is done with code.  These questions pertain to a totally manual 
process.  Thanks for your help.  Any idea why/how to not 'preserve' these cr 
doing a manual save as?

"Tom Hutchins" wrote:

> Why not just remove the carriage returns before saving as a CSV file? I 
> adapted the following code from a reply Dave Peterson made to a similar 
> question today in the Excel Programming group:
> 
> Sub Remove_CR_LF()
> 'select any cell in the column to be processed before running macro
> Dim myRng As Range
> Set myRng = ActiveCell.EntireColumn
> myRng.Replace What:=vbCr, Replacement:="", _
> LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
> myRng.Replace What:=vbLf, Replacement:="", _
> LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
> Set myRng = Nothing
> End Sub
> 
> Depending on your data, you may want to replace the vbCR or vbLF with a 
> space instead of an empty string.
> 
> Hope this helps,
> 
> Hutch
> 
> "WaterBug" wrote:
> 
> > I have a large xlsx file with the last column containing text with carraige 
> > returns (CR).  When I save this xlsx as a csv file these interim CR are 
> > preserved so each record of data isn't continuous on a single line.
> > 
> > Example data:
> > f1, f2, f3, "data for field 4"
> > f1, f2, f3, "more data <carraige return>
> > for field 4"
> > f1, f2, f3, f4
> > 
> > I discovered this by opening the csv file in word with paragraph markings 
> > turned on and I can see these paragraph marks.
> > 
> > Is this a 'feature' of Excel?  How can I save a CSV without these pesky 
> > returns?  I am using this csv in an import routine on another program and it 
> > fails without manual corrections.
> > 
> > Your help is appreciated...kelly
0
Utf
5/6/2010 1:47:01 PM
Okay, here is a non-VBA way to remove any carriage returns or line feeds. 
Assuming the last column (the one with the problem text) is D, enter this 
formula in E1:
=SUBSTITUTE(SUBSTITUTE(D1,CHAR(13)," "),CHAR(10)," ")
Copy this formula down through all rows of data. Recalc the worksheet. Copy 
& paste column E in place as values. Delete column D. Column E becomes the 
new column D; it has the same text, but without any carriage returns or line 
feeds. Save As CSV file.

Hope this helps,

Hutch

"WaterBug" wrote:

> None of this is done with code.  These questions pertain to a totally manual 
> process.  Thanks for your help.  Any idea why/how to not 'preserve' these cr 
> doing a manual save as?
> 
> "Tom Hutchins" wrote:
> 
> > Why not just remove the carriage returns before saving as a CSV file? I 
> > adapted the following code from a reply Dave Peterson made to a similar 
> > question today in the Excel Programming group:
> > 
> > Sub Remove_CR_LF()
> > 'select any cell in the column to be processed before running macro
> > Dim myRng As Range
> > Set myRng = ActiveCell.EntireColumn
> > myRng.Replace What:=vbCr, Replacement:="", _
> > LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
> > myRng.Replace What:=vbLf, Replacement:="", _
> > LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
> > Set myRng = Nothing
> > End Sub
> > 
> > Depending on your data, you may want to replace the vbCR or vbLF with a 
> > space instead of an empty string.
> > 
> > Hope this helps,
> > 
> > Hutch
> > 
> > "WaterBug" wrote:
> > 
> > > I have a large xlsx file with the last column containing text with carraige 
> > > returns (CR).  When I save this xlsx as a csv file these interim CR are 
> > > preserved so each record of data isn't continuous on a single line.
> > > 
> > > Example data:
> > > f1, f2, f3, "data for field 4"
> > > f1, f2, f3, "more data <carraige return>
> > > for field 4"
> > > f1, f2, f3, f4
> > > 
> > > I discovered this by opening the csv file in word with paragraph markings 
> > > turned on and I can see these paragraph marks.
> > > 
> > > Is this a 'feature' of Excel?  How can I save a CSV without these pesky 
> > > returns?  I am using this csv in an import routine on another program and it 
> > > fails without manual corrections.
> > > 
> > > Your help is appreciated...kelly
0
Utf
5/6/2010 3:09:01 PM
Reply:

Similar Artilces:

Row Not Found at the Subscriber
http://sanssql.blogspot.com/2010/05/row-not-found-at-subscriber-replication.html Regards, Sandesh Segu http://sanssql.blogspot.com/ ...

Reminder issues for Outlook 2010
When sending and receiving appointments the reciever is getting a default reminder for everything. We can shut off the defaults, uncheck the reminder before sending and still it defaults to 15 min reminder. With the number of appointments that we share, this has become a real pain. Please help with any advise and or suggestions. What version of Exchange server? There is a known issue with Exchange 2007. http://support.microsoft.com/default.aspx?scid=kb;en-us;973761 -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions ...

eConnect - MSMQ
I am able to send the xml data to MSMQ (server\private$\econnect_incoming75) que. The que is read by MBS eConnect service and then is deleted from the Que messages. The que apears in journal messages. The data never gets inserted into SOP tables. (SOP10100). The eConnect event viewer does not shows any errors. I am running MSMQ and eConnect on Windows 200 server. The eConnect and MSMQ runs on local system account. For MSMQ, If i log on This account, the MSMQ eConnect que disappeared. Anybody has any idea what must be the problem? Hi What account are you using for "This Acc...

saving slides as png/jpeg @ 1280x800
I have created a presentation with screen size 1280x800. When I save individual files as png or jpeg, they are saved as 960x600, which is a different size. Is there a way to export individual slides at 1280x800? On 30 Apr, 17:01, Alessandra <acima...@gmail.com> wrote: > I have created a presentation with screen size 1280x800. When I save > individual files as png or jpeg, they are saved as 960x600, which is a > different size. =A0Is there a way to export individual slides at > 1280x800? I forgot to say I am using PowerPoint 2007. I use an add-in to do this (ove...

100% cpu utilization during save as
I'm wondering if anyone here can help me with this. When I'm typing a filename in the Save As dialog box in Excel 2003, excel.exe shoots up to 100% CPU utilization and it takes a second or two for the letters I've typed to show up. This only happens when I'm trying to save to a network location. The same issue comes up in Word 2003 and Powerpoint 2003, but not in any other windows applications. Any ideas? Thanks --Cam If this is only to a network location, it suggests a problem on the network side - or permissions. But are you using mapped drives for this? If so, try ...

Word always asks to save changes to the template
For my work we have made templates for different letters we have to send. Our application (VB6) calls those templates when we want to send a letter. Using the code we follow a few steps : - open word - open a new document using our template - save the document (.doc) on a server - sometimes print the document - sometimes immediately close the document (when we don't want users to modify the document afterwards) When they closed the document the users always get 2 questions : - save changes to template for the letter - save changes to normal.dot I already found a solution to avoid the q...

Publisher font issue
I have an issue with Publisher 2000 and the calendar font size. The defaulted font size is 18. In each calendar date box, when I enter information is a smaller font (8 font), and save; close the program and then reopen; the 8 font text defaults to 18 font. I need the smaller font for information that needs to go into the calendar. I have tried a few tings through the help feature but nothing I do seems to work. Can someone help me with this or tell me how to fix this problem? Thanks Try this, put the calendar on the background (master page) Ctrl + m , use text boxes for your i...

Saving Word E-Mail as a Revision/Draft
Hi, I would like to be able to use the E-Mail feature in Word 2003 to save an email as a draft/revision for future use. I do not want to save the word doc as an attachment. Is this possible? Thanks, Josh Why don;t you just create the word doc, save it, and then email it when you're ready. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "JoshB" <JoshB@discussions.microsoft.com> wrote in message news:9710F08D-64E2-45B7-8115-BE8F0B116369@microsoft.com... > Hi, > I would like to be...

how to use a saved master page
I have created a new master page and saved it as a .pub file. The question is when opening a new document, how do I apply the saved master file? Open the master file, copy, paste to your new publication. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Colin" <Colin @discussions.microsoft.com> wrote in message news:A3C59647-7D56-46B9-AA56-C707538ECDED@microsoft.com... >I have created a new master page and saved it as a .pub file. The question > is when opening a new document, how do I apply the saved mas...

Thrift Savings Plan #2
Does anyone know an accurate way of tracking the military retirement plan, TSP? I have just added an account called TSP and modeled it like a 401(k). Then for each of the funds you have, just add a new investment. You won't have a stock symbol or anything. Some days, I manually update the fund prices, and others I don't. It will calculate your returns whenever you make new transactions. I basically just make those investment transactions whenever I get my quarterly statement. I don't know of a better way of tracking the TSP. "Anthony" wrote: > Does anyone ...

Exchange Migration Issue
Hello All, I have multiple copies of email in my user email accounts after migration. I used Exchange Migration Tool to Migrate email accounts from Exchange 2000 to Exchange 2003. Since we did not switch over to the new server right away , there were 2 days of emails still pending to be imported so I used the Migration tool again to migrate emails filtered based on date, but it seems that exchange migration has migrated all emails again, and now users have 2 to 3 copies of each email. Is there a way to remove the duplicate copies ? if so how can I go about doing that? Any pointer...

Setting email so server will not save emails on server
I have two computers. ( Desktop & laptop) I seldom use the laptop but when I do use it and get my emails it brings up every email that I have already read and deleted from desktop. Is there a setting where I can set so the server will not keep the deleted emails? On both machines: Tools | Accounts | Mail | Properties | Advanced - Check: Leave a copy of messages on the server. On one machine only, (The desktop would be logical for you), check to Delete messages after X days to satisfy your allotted space you get from your server, and Remove from server when d...

Entourage communication issue
My entourage quit communicating last week. Worked fine wednesday, thursday not. I can open entourage, but it won't send or retrieve mail. times out and gives a message that it's a server issue. My wife's works fine. tried installing the latest upgrade from microsoft, still no change. Both our macs have the same email configuration. tried the fixes given in the help section - snding myself an email, restarting entourage, etc, nothing. I'm not a computer genius, and need help. By the way, if i go into windstream via the internet, every thing's normal Thanks,Tj On 7/24...

store mounting issues
(this is not related to my other similar post today) My client's sbs machine was affected by sudden shutdown in a storm. Now when the server restarts we have to manually mount the stores. I will check to see if the database is dirty and it probably is, do I need to restore from backup? This seems toi be a time consuming operation, is the best way to proceed? I would start by looking in the Event Log. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "pepper" <pepper@discussions.microsoft.com> wrote in message news:28C8A34D-E...

Selection.AutoFilter Issue
I have the same code for 5 different reports. The only difference in code is the criteria filtered and removed. All reports filter and remove the desired data just fine except for one. The code is below and it is the same each reports with the exception of the worksheets it is deleting and Criteria1 for each sheet. NOTE: The BCP E&M code below filters correctly but not the others...and they are the same code! Any help would be greatly appreciated. Dim myLastRow As Long Dim myLastColumn As Long Dim myLastCell As String Dim myRange As String Dim Newbook As String Di...

how to save settings for printing several envelopes
I want to print addresses on several envelopes which are the same size but the addresses are different (for Christmas cards). Is there a way to do this without going to options each time and reset the size and font that I want? Create an envelope template. You can start from scratch with a new document, selecting the desired paper size (or specifying a custom size) or use Add to Document to add your envelope to the current document. Then see http://sbarnhill.mvps.org/WordFAQs/DetachEnvelope.htm for instructions on separating the envelope from the document. Once you have just the ...

Why Excel 2007 not responding after saving
I have reinstalled Excel 2007 (Office Home & Student) but it is worse. Now having problems even opening files. If I click on a cell it pauses for ages and then says it is not responding. Try looking for help here: http://support.microsoft.com/?scid=ph;en-gb;8753#tab3 "Xandersmum" wrote: > I have reinstalled Excel 2007 (Office Home & Student) but it is worse. Now > having problems even opening files. If I click on a cell it pauses for ages > and then says it is not responding. ...

Font Issue 12-01-09
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3342466225_2506650 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: 8bit I upgraded my iMac to Snow Leopard and tried as best I could to follow the directions re deleting old and duplicate fonts for Office 2004 and 2008. That seemed to correct the gibberish I was getting in Entourage, but I am left with constant �error� messages asking me if Entourage (2008) can use a font on one of my several external...

Excel 2007 slow saving with defined names
Hello, I am using a worksheet with a lot of defined names. Saving this sheet in Excel 2007 takes about 5 minutes! In previous Excel-versions the same worksheet saves within 10 seconds. Does anyone have an idea to improve this performance? Probably Windows Vista has something to do with this problem, because: - Windows XP and Excel 2007: fast saving - Windows Vista and Excel 2003: fast saving - Windows Vista and Excel 2007: slooooow saving Any ideas? Greeting RI ...

.csv file #2
Once I saved an Excel file to .csv file, how can I protect .csv file with a password? You won't be able to protect the .csv file with a password in Excel. The .csv file will only contain values - all your formulae and any formatting (colours, fonts, lines, column widths etc) will be lost when you save it. In Explorer you can change the attributes of the .csv file to make it read-only, but I'm not sure how useful this would be, as a User will load it into Excel and save it as an .xls file (or as another ..csv file). Hope this helps. Pete ..csv files are plain text. Maybe you coul...

Add Vendor Doc. No. to Return from Project Entry screen
We can enter the Vendor Doc. No. in the Returns from Inventory Entry but there is no available field in the Return from Project Entry screen. We'd also like to have the Apply To option when entering a return so the invoice and the credit could link on the PM side. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and th...

outlook meeting issue
Hi I am using outlook 2000. I invite people for a meeting and they accept then I get accetance reply form them. all of a sudden, I am getting any reply even though people accept and send me a reply. It used to work fine. If any one has this problem and know the way to work around. I appreciate for you help. Thanks ...

how do i remove routing information from saved excel spreadsheet
I have created a spreadsheet and sent it to people. now everytime i open the spreadsheet the names are still in the send to and cc fields. i want to remove the routing from the spreadsheet so that it opens clena. Click on File|Send to Then toggle the "mail recipient" option. (It may not looked depressed, but it is!) debbym wrote: > > I have created a spreadsheet and sent it to people. now everytime i open the > spreadsheet the names are still in the send to and cc fields. i want to > remove the routing from the spreadsheet so that it opens clena. -- Dave Pete...

Where are my templates saved?
Can I share them with other users? Did you look under: C:\Program Files\Microsoft Office\Templates\1033\ONENOTE\[VERSION]\Stationery -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.html Author: The Lawyer's Guide to Microsoft Outlook 2007: http://tinyurl.com/ol4law-amazon "Phil L." <PhilL@discussions.microsoft.com> wrote in message news:18F26F9A-139E-45AE-AF7B-4F758DFD4BA2@microsoft.com... > Can I share them with other users? ...

CRM Mobile CA issues
Greetings, I am attempting to load CRM Mobile to a SBS 2003 server. The certificate issuance is causing my problems, (likely a user one). The way I understand it I need to create a Enterprise Level Root Authority using the server .local name and then issue a cert for the CRM site DNS name. When I run CRMMobIPCfg.exe I get several service failures. I can force them up in Service manager but the integration service terminates unexpectedly. Can anyone offer any direction; information on the net is sparse. ------=_NextPart_0001_2DECD42B Content-Type: text/plain Content-Transfer-Enc...