OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Saving CSV file in excel format

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
michelleooffice2
Newbie
Newbie


Joined: 22 Feb 2012
Posts: 1

PostPosted: Thu Feb 23, 2012 2:41 am    Post subject: Saving CSV file in excel format Reply with quote

Hi everyone! I have ready many threads on this, but can't seem to find a simple solution (that I understand).

I am using an online courier website that uses an csv file to upload addresses to an address box. I need to be able to export addresses from my customer database to calc, then save in csv format from calc.

However the website only supports the excel format - the difference being that in excel any strings containing a comma get wrapped in quotes, whilst the remaining strings are left without quotes. Whereas in calc, I have the option to either have all the text strings wrapped in quotes, or none wrapped in quotes.

I.e;
Excel:
Mr. Ranom,"Unit2, 20 Random Street",RandomTown,RandomCountry

Calc:
"Mr. Random","Unit2, 20 Random Street","RandomTown","RandomCounty"
OR
Mr. Ranom,Unit2, 20 Random Street,RandomTown,RandomCountry

The problem obviously being in the second example of calc, the info is not submitted to the website correctly because the comma means all the info is shiften over by one column.

I don't know how to do macros, or how to write any programs etc. But I really need a solution to this. I need it to be simple enough that my staff can get the address from our database to the courier website without to much mucking around.

I really do appreciate any help you can offer!
Michelle
Back to top
View user's profile Send private message
davidh182
OOo Advocate
OOo Advocate


Joined: 01 Apr 2004
Posts: 413

PostPosted: Sat Feb 25, 2012 9:52 am    Post subject: Reply with quote

You could create a second page which simply copies sheet 1 but puts quotes in if the cell contains a comma, then save that page.

Code:

=IF(Sheet1.A1="","",IF(ISERROR(FIND(",",Sheet1.A1)),Sheet1.A1,""&Sheet1.A1&""))


[copy to required range in sheet 2]

Would be possible to do it all by macro if it's a frequent operation...
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group