![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
How do I get rid of the single quote format character so I can work with the
dates from imported cvs or xls files?
Am 01.09.2012 14:04, JAMES MAJESKI wrote:
How do I get rid of the single quote format character so I can work with the
dates from imported cvs or xls files?--
View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907.html
Sent from the Users mailing list archive at Nabble.com.
[X] Detect special numbers
What you have is not dates as such, but date values expressed as text. How to solve your problem is difficult to say exactly, since date formats and behaviour are locale-dependent. And a successful technique depends on exactly what format the text dates are in. But the first thing is not to try to remove the quote character - since it is not really there! It is simply an indicator that the value has been stored as text even though it looks like a date (and no doubt means a date to you).
Here are two ideas:
o When you open a .csv file in LibreOffice, you should see a Text Import dialogue box. If you click the column headers, you can then select a column type from the drop-down list. Selecting an appropriate date format at this stage may help. As has already been suggested, the "Detect special numbers" option should also solve your problem at this stage.
o If you have text values representing dates in your spreadsheet, try a formula such as =VALUE(Xn) or =DATEVALUE(Xn) in another column. Fill this formula down the column. Once you have a formula that works, you can copy and paste the results back over the original text values if you wish, and then even delete the new columns. But you will have to freeze the results of your formulae. To do this, use Edit | Paste Special... (or right-click | Paste Special... or Ctrl+Shift+V) instead of ordinary Paste; in the Paste Special dialogue, ensure "Paste all" is not ticked and then that Formulas is not ticked (but "Date & time" is) as well.
I trust this helps.
Brian Barker
James,
On the box with all the import options, at the bottom where you see the
table click on the column with the date data in it. All the columns are
marked standard, change the date one to the desired date format in the
column type selector.
Thank you all for your assistance. You have pointed to features of which I
was not aware, but found easily. The work-around solution will not be
necessary. As for not deleting the single quote that is not there, that
work-around works, but is very labor intensive. I will be doing some
experimenting to see the best way to implement your suggestions to import
number and date and time data, but the preliminary test suggests that it
works on dates in the mm/dd/yyyy mixed-endian format. Now to test it on the
dd mmm yyyy big-endian and yyyy-mm-dd small endian formats which are the
three main formats. Of all the formats, I have been using the small endian
format since the sixties as it sorts properly even in text format. I was
surprised when I found out that the International Standards Organization
chose that format and called it ISO8601. Again, thank you all. 2012-09-01
51:59:28 Z-07
Please keep us informed ...
it might be nice to have the program be able to inject
dd-mon-year and/or dd-mo-year for genealogical research projects 
Thank you all for your assistance. You have pointed to features of which I
Am 02.09.2012 21:33, anne-ology wrote:
Please keep us informed ...
it might be nice to have the program be able to inject
dd-mon-year and/or dd-mo-year for genealogical research projects
It can do this easily for thousands of different csv formats.
The previous suggestions should work for input from a file into a new
spreadsheet, but if you have an existing spreadsheet, you may have date and
time in either date and time or text format. Anyone working with date and
time, date, time, or number formats may have this problem.
It will not matter if your date and time entries are static, but sorting the
entries by date is usually why a spreadsheet is used. I suggest using the
ISO8601 format [Fri 1999-12-31 23:59:59 (NN YYYY-MM-DD HH:MM:SS)] as that
will sort properly as text if the entries are all from the same time zone.
My problem was because I tried to solve the problem within the spreadsheet
and there is no known way to do that. My solution is to take the entire
column of data out of the spreadsheet, enter it into a simple word processor
(gedit for example) to remove all formatting and copy it back into the
spreadsheet. I make sure the column is correctly formatted before pasting
the data. Unfortunately, a direct copy and paste special does not work as
there is no unformatted option, just check boxes to include or exclude data
formats. Copy a text encoded date and paste special will paste the data only
if text is checked.
I hope my description of what works for me is clear enough for you to use
the same (or similar) procedure.
Of course Calc can import and export all flavours of numbers, dates,
currencies, scientific numbers, plain text, numeric text, booleans and
fractions in 180 different languages. Just do it.
btw: There is no formatting in a text editor (gedit). So you can not remove
any formatting.
My knowledge of the processes involved is limited to my personal experience,
but I see that when I copy from a spreadsheet and paste special, the dialog
box limits the format that is pasted, but does not allow the removal of all
formatting. When I paste to gedit then copy from gedit and paste special,
the 'unformatted text' option is presented (as this was the only option
presented, I presumed that gedit removed the formatting). This text is
converted into the proper format when it is pasted into a properly formatted
cell. I have not tried to do a simple paste to see if the formatting is
still present, as you suggest. In my case, being able to strip the
formatting from the entry is the primary goal as that allows the data to be
formatted according to its function (date and time, date, time, number). All
previous attempts failed because the formatting remained attached to the
entry. Manually removing the formatting mark from each entry, one entry at a
time, was prohibitively time consuming.
Hi 
People get confused about the difference between a text-editor and a word-processor.
It's especially confusing because a number of advanced text-editors are able to cleverly interpret the textual information they are given and convert the text into colour-coded output. However if you change the preferences of the text-editor then it changes the way it colour codes the file. Also individual characters or words cannot be formatted in any way, such as made bold or centred. If you change the preferences for an advanced text-editor then it changes the colours of the entire document, not just a portion of it.
Basic text-editors such as Notepad can still read a file that appeared to have colour-coding and will show you that there is no colour-coding at all really. It's just that the advanced text-editors look for things such as coding brackets and known commands that you would normally look-up in a look-up table or from some other notes. Since the list of commands is usually finite and seldom allows typos it helps coders by drawing attention to things where the colour-coding appears to have gone wonky.
A great example is to look at an html page, say any page on the internet, and then go to your web-browser's equivalent of
View - Source
which then shows the code and text which is really what your web-browser receives and then translates that text into (hopefully) the page that the web-designer wanted you to see.
Hmm, ok so Notepad is a bad example because it is extremely basic and is made by MS so the usual problems apply = it can't read things that are not done "the Windows way" such as those using a different end-of-line character and so on. Also it needs to be told a file is text, such as by using the .txt file-ending as it can't work it out for itself.
Word-processors are good at processing words, applying formatting to individual portions such as making headings bold or in a different font etc
Actually before this list told me i could paste unformatted text i would often use a text-editor to strip away formatting in order to paste as unformatted text.
Regards from
Tom
So the topic is about importing ISO dates, right?
Calc, Writer and Base always interprete 2012-09-05 23:45:59 correctly
regardless of locale conetex when you type it into some table cell unless
the cell is explicitly prepared to accept text only.
When using the text import dialog, all you need to check is
[X] Detect special numbers
as indicated in my first post.
All these questions around text import suffer from 2 issues:
1) People import plain text into an application they do not understand. They
assume that a spreadsheet is the proper application to import, edit and save
database exchange data in plain text. No, spreadsheets are not the right
tool unless you really want to import these data into your spreadsheet
calculation models. But even then I would import text files through the Base
component into the spreadsheet simply because csv is about database data and
you can define your special import parameters once for all times.
2) Csv is NOT a file format. It is a mere convention how to represent
database rows with constant field count in rows of plain text.
Unfortunately, everybody uses different date strings, different decimals,
different text quoting, different line feeds and different character
encodings. Of course, MS Excel has its own rules which is why database
admins usually hate Excel.
Calc can handle most of flavours of csv properly with very few effort but in
case of problems it is completely impossible to give any advice without
actually seeing a couple of data rows copied from a plain text editor.
The distinction between text editor, hyper text markup language, word
processor, desktop publishing, etc is getting blurred as the program's
authors keep adding features. You say there are differences, but are not
very clear as to what they are.
A text editor is the simplest in that a text editor does not format, color,
or enhance the text. It is just the simplest type of program to enter text
and will not support the insertion of graphics, sound, or video. Any format
modifications will effect the entire document. A text editor jumps the
boundary to word processor when it can mark format changes within a
document.
A word processor allows the modification of a character, word, paragraph, or
document for display or printing. Usually the display and the printed output
are similar. Text, graphics, charts, video, and sound may be inserted in an
advanced word processor document. The hyper text markup language is a subset
designed for publishing on the internet and read by a web browser.
Desktop publishing programs allow the maximum flexibility in positioning
elements in a document meant to be printed. To allow for the best fit, the
desktop publishing program allows the moving of characters, words, images,
etc. The distinctions are again blurred with the advent of hypermedia
publishing and enhancements of word processor programs.
As enhancements and improvements occur, the distinctions will become more
difficult to discern. Text editors may remain as text only for display or
printing. Desktop publishing may remain as anything printable. Everything
else will only be limited by the intended end use; display, print, webpage,
multi-media, etc.
No, the problem was importing date and time, date, time, and number and
having it formatted so the formatting may be modified. I learned three
things from the previous replies that I find very useful in resolving the
format problem. Each seems to trick the spreadsheet into accepting input as
if it were from a keyboard.
I was unaware of the new feature in the 'input data' dialog box, 'Detect
special numbers' and the ability to format the text before it is pasted into
the document. Paste special would usually come up with a dialog box that
would limit the formats to be pasted into the document, but when copied from
a text editor, it would show the 'unformatted' option as the only paste
option. In the latter case, I get the correct formatting if I remember to
format the column before pasting the date. This last option is available
when dealing with single columns. I sort that dates in the original
spreadsheet by time zone. Format the main spreadsheet with the template for
the time zone (calculates the relative offsets for all the other time
zones). Copy the date and paste it into the appropriate time zone column.
Copy and paste the rest of the data. Repeat for each time zone. This works
because both spreadsheets have the same formatting.
I receive dates in three different formats; big endian [31 Dec 2012], mixed
endian [Dec 31, 2012], and small endian [2012-12-31] (also known as
ISO8601). I prefer the ISO8601 format for date and time as I have been using
it before there was an ISO8601. On the rare occasion I get dates with two
digit years, I always presume 19nn because I presume the Y2k publicity would
have caused people to think of the year as a four digit number. I also
presume that anyone that would still use a two digit year for 20nn does not
think. I prefer not to deal with non-thinkers.
I receive times in two different formats; twelve hour clock [11PM OR
11:00PM] and twenty-four hour clock [23:00]. If the AM or PM is missing, I
presume the twenty-four hour clock format.
Time zones are usually not present, so I presume the time zone of the sender
or location of the data. When present, most time zones are defined as
offsets to UTC [Z±hh]. Those that are not are presumed to be the time zone
in effect on the posted date. For example, Eastern Daylight Time, EDT is
Z-04 and Eastern Standard Time, EST is Z-05, but Eastern Time, ET changes
depending upon the transition date and time. For example:
Sun 2012-11-04 02:00 Z-04 [EDT] becomes Sun 2012-11-04 01:00 Z-05 [EST]
Sun 2013-03-10 02:00 Z-05 [EST] becomes Sun 2013-03-10 03:00 Z-04 [EDT]
I wish there was a way to incorporate the time zone in the date and time
format, but since there is not, the work around is to include an extra
column with the time zone for each entry (sortable if in UTC offset format)
or separate columns labeled for the time zone of the entry (sortable in any
of the time zone columns). I have a column for each time zone I use and use
a template of formula to convert the entry column to correctly fill the
other columns. For example, Z±00 (UTC) entry in column A to Z-05 (EST/CDT)
in column F [=An-(5/24)] (where n is the row number). Column G Z-06
(CST/MDT) [=An-(6/24)]. Etc.
I have never used a database, so I do not know if what I am doing in the
spreadsheet is possible in a database. I can format the spreadsheet display
to split the screen so as to show the preferred time zone and the headers
and the data. I may compare the line items for specific dates visually or by
using a function in an unused column. If appropriate, I may generate a graph
of a range of data.
Please avoid the term "formatting". No, the locale setting does not format
anything. This option specifies the assumed language to be used for the
*interpretation of text strings*.
For instance, a "German(Germany)" locale for this particular text import
tells the program that ...
123.456 needs to be interpreted as integer number 123456 because the point
is used as thousands separator.
123,456 needs to be interpreted as a decimal fraction because the comma is
used as decimal separator.
12.10.2012 needs to be interpreted as date because this is a common way we
write dates in Germany.
Same with 12. Oktober 12 ("Oktober" with k) and dozends of other date
strings which have to be interpreted correctly as one particular day number.
Incoming text "WAHR" and "FALSCH" evaluate to boolean TRUE and FALSE.
The incoming string of characters includes some numeric values as they had
been formatted by some unknown other software, but for Calc it is a string
of characters coming from some file. Since Calc is a calculator program it
tries to evaluate the character snippets as numbers. Since there are rather
fuzzy cultural differences in interpreting characters as numeric values, we
have to tell the software the cultural context to be used for the evaluation
of character strings.
When you choose this import locale to import any data that are supposed to
be German the application opens a blank new default spreadsheet and fills
the cells with the respective values.
If your blank new default spreadsheet happens to be English:
Input 123.456 yields integer 123456
Input 123,456 yields fraction 123.456
Input 12.10.2012 or any other German date expression for that particular day
yields 10/12/12 if your new spreadsheet happens to be US English and
12/10/12 in case of most other flavours of English (1212-10-12 in Canadian)
Formatting is the way how these values are displayed in your sheet cells.
Formatting is completely unimportant. Getting the right numbers into the
cells (or text if it's not a number at all) is the one and only thing that
matters in a calcuation program. How these values are displayed (formatted)
is completely unimportant. You can change the formatting of correct values
with a few clicks withtout changing a single value. But it is impossinle to
get anything right when the imported/pasted/entered values are wrong.
German 123.456 is the exact same value as English 123,456 or plain 123456.
German "31. Dezember 12" is the exact same value as US "December 12 2012" or
41274. Yes, that day is just another format of number 41274. Enter the date
in A1, the number in A2 and compare =A1=A2 which will yield TRUE (or the
exact same value WAHR in German which is the number 1 actually). All dates
are numbers and all numbers can be shown as date (unless they are text).
Formatting is the correct word as I wish to be able to format the date and
time, date, time, number, and currency into the required format.
Your assertion that the correct number must be correctly entered into each
cell is absolutely correct. My problem was that the number was not being
entered correctly because of the format in which the number was being
presented to the spreadsheet. There is a wide variety of formats. For
example, a specified date.
In my personal experience, '10.11.2012' has been demonstrated to be two
different dates (Fri 2012-10-11 OR Mon 2012-11-10). Everyone is accustomed
to using their own format and, in some cases, their own calendar:
{12 Bak'tun 19 K'atun 19 Tun 15 Winal 17 K'in 13 Kaban 0 Keh G2} is Sat
2012-11-10.
09,27,4710 is Sat 2012-11-10.
25 Che 5773 is Fri 2012-11-09 from local sunset to midnight and Sat
2012-11-10 from midnight to local sunset. Longitude is required to resolve
local sunset.
2012-315 is 2012-11-10.
2012-45 includes 2012-11-10.
This is why I prefer using ISO8601 as the International Standards
Organization's format is least likely to be misinterpreted. For other
formats, I prefer to use the name of the month abbreviated to three
characters, especially if there is any chance of the month being confused
with the day (day <13).
Unless punch cards are still being used, there is no excuse for a two digit
year.
Meine Übersetzungen sind nicht sehr gut, so dass ich selten nutzen Deutsch.
Zu zahlreichen Anlässen habe ich gebeten worden zu benutzen nur Englisch.
The data are dumped into a blank new sheet. In a blank new sheet there is
only the "General" number format which applies one distinct number format
per number format category.
You must not try to edit text tables in a spreadsheet and expect numbers to
be resaved in the same number format as they have been imported.
Spreadsheets are not able to do this. As a matter of fact, spreadsheets are
inappropriate to edit and save csv data. Of course you can use imported text
data in calculation models, but then the formatting of the input would not
be relevant by any means.
CSV is an exchange format for databases in plain text. Each line represents
a database record. Each record has the same amount of database fields. Each
database field holds one field type (dates below dates, integers below
integers, text below text, etc.).
There are several ways to deal with csv files using the Base component. Once
you have anything in the Base component, you can also link it to
preformatted spreadsheets. Then there are programmatic ways to import text
into preformatted templates. Both, databases and macro programming, is
something for professional users only.
For Windows users there is an excellent text editor:
http://csved.sjfrancke.nl/
This tiny text editor for tabular data can do more for you than any
spreadsheet.
I know nothing about databases. Is there some advantage to using a database
that would make it worth my while to learn how to use a database?
James,
Databases allow one to store information in related logical groups (or
tables) with the ability to search the entire logical group to find
specific information. They are very good when there is a large amount of
data to be stored and accessed.
For example an inventory and order tracking system would allow one to
enter the data about products and orders then search (query) the data
for all orders for a specific item, etc.
Use the extension CT2N.
It Converts Text to Numbers.
Simples.
Tink.