| View previous topic :: View next topic |
| Author |
Message |
neblaz
General User
Joined: 28 Feb 2006
Posts: 6
|
 Posted: Tue Feb 28, 2006 4:15 pm Post subject: save as CSV format |
 |
|
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 |
|
 |
yuki
OOo Advocate
Joined: 20 Oct 2005
Posts: 264
|
| Posted: Wed Mar 01, 2006 2:33 am Post subject: |
|
|
| What if you changed the cell format from number to text? |
|
| Back to top |
|
|
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
Location: Kingston ON Canada
|
| Posted: Wed Mar 01, 2006 6:17 am Post subject: Re: save as CSV format |
|
|
| 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 |
|
|
neblaz
General User
Joined: 28 Feb 2006
Posts: 6
|
| Posted: Mon Mar 06, 2006 12:40 pm Post subject: |
|
|
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 |
|
|
Dale
Super User
Joined: 21 Feb 2005
Posts: 1440
Location: Australia
|
| Posted: Mon Mar 06, 2006 2:34 pm Post subject: Re: save as CSV format |
|
|
| 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 |
|
|
neblaz
General User
Joined: 28 Feb 2006
Posts: 6
|
| Posted: Mon Mar 06, 2006 3:21 pm Post subject: |
|
|
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 |
|
|
Dale
Super User
Joined: 21 Feb 2005
Posts: 1440
Location: Australia
|
| Posted: Mon Mar 06, 2006 3:36 pm Post subject: |
|
|
| 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 |
|
|
neblaz
General User
Joined: 28 Feb 2006
Posts: 6
|
| Posted: Tue Mar 07, 2006 1:29 pm Post subject: |
|
|
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 |
|
|
Dale
Super User
Joined: 21 Feb 2005
Posts: 1440
Location: Australia
|
| Posted: Tue Mar 07, 2006 1:48 pm Post subject: |
|
|
| 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 |
|
|
neblaz
General User
Joined: 28 Feb 2006
Posts: 6
|
| Posted: Tue Mar 07, 2006 3:01 pm Post subject: |
|
|
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 |
|
|
Dale
Super User
Joined: 21 Feb 2005
Posts: 1440
Location: Australia
|
| Posted: Tue Mar 07, 2006 4:40 pm Post subject: |
|
|
| 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 |
|
|
neblaz
General User
Joined: 28 Feb 2006
Posts: 6
|
| Posted: Tue Mar 07, 2006 5:10 pm Post subject: |
|
|
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 |
|
|
bogus8
Newbie
Joined: 23 Jul 2006
Posts: 2
|
| Posted: Sun Jul 23, 2006 12:12 pm Post subject: |
|
|
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 |
|
|
Dale
Super User
Joined: 21 Feb 2005
Posts: 1440
Location: Australia
|
| Posted: Sun Jul 23, 2006 3:00 pm Post subject: |
|
|
| 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 |
|
|
bogus8
Newbie
Joined: 23 Jul 2006
Posts: 2
|
| Posted: Sun Jul 23, 2006 6:08 pm Post subject: |
|
|
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 |
|
|
|
