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

save as CSV format
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
neblaz
General User
General User


Joined: 28 Feb 2006
Posts: 6

PostPosted: Tue Feb 28, 2006 4:15 pm    Post subject: save as CSV format Reply with quote

hi,

i have an Excel Sheet, all my cells are formated as text, some of them
are of structure pure text as ABCDEF and some of them are numbers
as 0.712123.

when saving as CSV format i choose as Field delimiter a comma and
as Text delimiter double quotes.

the resulting CSV text file shows me those fields in double quotes which
contained at least one letter, but those fields which contained pure
numbers are without double quotes.

that's not what i desired. i need all cells in double quotes, how to achieve
this?

i use the latest openoffice.org 2.0.1.

regards.
Back to top
View user's profile Send private message
yuki
OOo Advocate
OOo Advocate


Joined: 20 Oct 2005
Posts: 264

PostPosted: Wed Mar 01, 2006 2:33 am    Post subject: Reply with quote

What if you changed the cell format from number to text?
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Wed Mar 01, 2006 6:17 am    Post subject: Re: save as CSV format Reply with quote

neblaz wrote:
hi,

i have an Excel Sheet, all my cells are formated as text, some of them
are of structure pure text as ABCDEF and some of them are numbers
as 0.712123.

when saving as CSV format i choose as Field delimiter a comma and
as Text delimiter double quotes.


the resulting CSV text file shows me those fields in double quotes which
contained at least one letter, but those fields which contained pure
numbers are without double quotes.

that's not what i desired. i need all cells in double quotes, how to achieve
this?

i use the latest openoffice.org 2.0.1.

regards.[/quote]

I assume that you meant Calc? I tried this in Excel and cannot seem to get Excel to export with a text delimiter at all. Mind you I have not used Excel much in 3-4 years and I may just be forgetting how to do this

I may have found your problem but I am not quite sure what to do with it.
In Calc I formatted some cells, imput characters and numbers in the cells and it exported perfectly with the comma and double quote delimiters.

Then in another file I input some characters and some numbers in the cells, formatted it as text (Note that the numbers did not left align) and then exported. I then had your problem. The double quotes were missing for the numerals.

If I recall correctly there is something in the Help about how Calc treats numbers that are formated as text after they have been entered but I have 2.0.1 loaded and have not manually installed the missing help files so I cannot track it down. In any case I had not realised that it would affect CSV output.

I think we have a bug here!
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
neblaz
General User
General User


Joined: 28 Feb 2006
Posts: 6

PostPosted: Mon Mar 06, 2006 12:40 pm    Post subject: Reply with quote

hi,

to have no misunderstanding:
I used OpenOffice.org Calc 2.0.1 (my data set was an Excel Sheet, but export in CVS with Excel doesn't fit my needs), all cells were formated as Text, some of them had text inside and some pure numbers.

if it is a bug, that only cells with text do have quotation marks when exported into CVS, and cells with pure numbers don't have anything, then my question would be: how fast can this bug be fixed, as I need working export into CVS for large data set?

perhaps somebody knows about another possibility how to export an Excel Sheed into CVS with Text Delimiter quotation marks and Field Delimiter comma?

regards.
Back to top
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Mon Mar 06, 2006 2:34 pm    Post subject: Re: save as CSV format Reply with quote

neblaz wrote:
when saving as CSV format i choose as Field delimiter a comma and
as Text delimiter double quotes.
and
Quote:
if it is a bug, that only cells with text do have quotation marks when exported into CVS, and cells with pure numbers don't have anything
No - it's not a bug. You've told Calc you want double-quotes around text - numbers do not get the double-quotes. If you want quotation marks around each item, and commas as field delimiters (why? it seems redundant) then you have to convert the numbers to text before exporting to csv.
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
Back to top
View user's profile Send private message
neblaz
General User
General User


Joined: 28 Feb 2006
Posts: 6

PostPosted: Mon Mar 06, 2006 3:21 pm    Post subject: Reply with quote

how does the conversion of numbers to text work? i found only "Format cells..." in the context menu of a particular cell, and there i chose Text as Category. Do you ment that as the conversion?

some cells with text contain commas, that's why i need redundancy.
Back to top
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Mon Mar 06, 2006 3:36 pm    Post subject: Reply with quote

neblaz wrote:
how does the conversion of numbers to text work? i found only "Format cells..." in the context menu of a particular cell, and there i chose Text as Category. Do you ment that as the conversion?
I would use the TEXT function: =TEXT(A1;"00.0000") - replace "00.0000" with the format you want. Then maybe Copy & Paste Special to replace the formula with the string.

Quote:
some cells with text contain commas, that's why i need redundancy.
Yes - that is why you have text delimiters. But these are not cells with numbers in them.
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
Back to top
View user's profile Send private message
neblaz
General User
General User


Joined: 28 Feb 2006
Posts: 6

PostPosted: Tue Mar 07, 2006 1:29 pm    Post subject: Reply with quote

ok, but how to apply the TEXT function to a specific cell?
for example if a cell contains 1.01, what are the steps to convert it to
a number using the TEXT function?

if I enter =TEXT(B1;"0.00") after I marked the cell B1, then that what
I entered is in the cell insted of 1.01.

next question: if I know how to convert one cell, how can I do that
for hundreds of cells, perhaps in one step?
Back to top
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Tue Mar 07, 2006 1:48 pm    Post subject: Reply with quote

neblaz wrote:
ok, but how to apply the TEXT function to a specific cell?
for example if a cell contains 1.01, what are the steps to convert it to
a number using the TEXT function?

if I enter =TEXT(B1;"0.00") after I marked the cell B1, then that what
I entered is in the cell insted of 1.01.
If B1 contains the number you want to convert to text, put the formula =TEXT(B1;"0.00") in any other cell (C1 for example) and the text will display there.

Quote:
next question: if I know how to convert one cell, how can I do that
for hundreds of cells, perhaps in one step?
Select C1. You will see the border has a lttle black square at the bottom left corner. Grab this with your mouse (click and hold) and drag it down as far as you need. The formula will automatically fill as you drag the selection. You now have two columns: One with the number; and one with the number as text.
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
Back to top
View user's profile Send private message
neblaz
General User
General User


Joined: 28 Feb 2006
Posts: 6

PostPosted: Tue Mar 07, 2006 3:01 pm    Post subject: Reply with quote

well, it seemed to work for a moment, but now something in OpenOffice Calc has changed, so if i have my number 1.05 in B1 and enter =TEXT(B1;"0.00") in F1, then in F1 doesn't appear the string =TEXT(B1;"0.00") anymore like it did before, but rather 0.01, so the value of B1.

if I then export into CSV, only the value in F1 is in quotation marks, in B1 nothing has changed.
Back to top
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Tue Mar 07, 2006 4:40 pm    Post subject: Reply with quote

neblaz wrote:
if I then export into CSV, only the value in F1 is in quotation marks, in B1 nothing has changed.
That's as expected. Do the following on a copy of your spreadsheet:
  • Select Column F
  • Edit > Copy
  • Select Column B
  • Edit > Paste Special
  • Uncheck everything except Strings and Format
  • Click OK
  • Click Yes when the warining about overwriting existing data comes up
  • Select Column F
  • Press Backspace

You have now replaced your original column of numbers with a column of text that looks like numbers. If you open it again in Calc you will have to convert it back to numbers before you can do arithmetic operations on it.
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
Back to top
View user's profile Send private message
neblaz
General User
General User


Joined: 28 Feb 2006
Posts: 6

PostPosted: Tue Mar 07, 2006 5:10 pm    Post subject: Reply with quote

ok, fine, that seems to work. will try to apply this to the data set i will receive.
in the last step those additional conversion columns can be deleted to not
have them in the CSV file.
best regards.
Back to top
View user's profile Send private message
bogus8
Newbie
Newbie


Joined: 23 Jul 2006
Posts: 2

PostPosted: Sun Jul 23, 2006 12:12 pm    Post subject: Reply with quote

I know this is an old thread but this is exactly what my problem is so as to save time I shall tag on.

My only difference is I don't have a set format of input nor do I need a set format for output.

this is a spreadsheet populated by someone else and it is dates... some are xx-xx-xx, some are x-xx-xxxx, some are xx/xx/xxxx, some are just june06 and some are just 2006.

so I need a way to encapsulate those fields no matter if it's text or numeric and no matter what format the numbers are in.

Is this possible... I need to import this into a database and like the previous guy some of the addresses have comma's in them so I can't just go off of comma based.
Back to top
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Sun Jul 23, 2006 3:00 pm    Post subject: Reply with quote

bogus8 wrote:
this is a spreadsheet populated by someone else and it is dates... some are xx-xx-xx, some are x-xx-xxxx, some are xx/xx/xxxx, some are just june06 and some are just 2006.
Ugh!

Quote:
so I need a way to encapsulate those fields no matter if it's text or numeric and no matter what format the numbers are in.

Is this possible... I need to import this into a database and like the previous guy some of the addresses have comma's in them so I can't just go off of comma based.
Inital thought: try something like =IF(ISNUMBER(B1);TEXT(B1;"<whatever format you need>");B1) This will convert your numbers (dates) to a text output in a consistent format.
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
Back to top
View user's profile Send private message
bogus8
Newbie
Newbie


Joined: 23 Jul 2006
Posts: 2

PostPosted: Sun Jul 23, 2006 6:08 pm    Post subject: Reply with quote

Hmm... worked on some.

Still ones like "November 06" come out to be "39027" ... Oh and there are some places all OVER the sheet that have blanks... I need to have them turned into "" so mysql will recognize them as a placeholder and skip that colum when populating the table ... *sigh* what a pain.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
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