At 18:01 06/10/2016 -0400, Doug McNutt wrote:
Brian had some questions about how I read, with curl and perl5, the csv
files which seemed to be applying an apostrophe to dates formatted as
MM/DD/YYYY.
I'm not sure why you think you need to preprocess these documents before
using them. It would be instructive to use them exactly as you get them -
from your bank - and see what happens. If there are problems, you can see
what those problem are and decide how to circumvent them - which could
involve preprocessing but may instead and more easily be modification of
techniques or further processing in the spreadsheet itself.
Managed to open *.csv into new window using the suggested procedures. Copy
and paste into BANK worksheet ...
Whoa! Hold your horses. The question is about how to input the CSV file
data. Before you do anything else with it, let's survey the situation: if
the data has been imported correctly, any problems must be being introduced
by you later; if not, the way forward is to deal with the problem now.
When you import that date data, does it come in as dates or as text?
o Dates will be right-aligned by default. Their cells will have been
automatically formatted as Date. If you click View | Value Highlighting,
dates will show in blue text.
o Text will be left-aligned by default. Its cells will remain formatted as
Number | General. If you click View | Value Highlighting, dates will show
in black text.
You can examine formatting by selecting a single cell and going to Format
| Cells | Numbers. For the test to work, it is important that you have
*not* formatted any cells or cell ranges in advance, so import the material
into a fresh sheet, not somewhere you have already been using. Best of all,
right-click the CSV file and use Open With... (or whatever similar facility
your operating system provides) to open it in LibreOffice.
I selected the csv data and performed a copy followed by a paste into my
worksheet but moved over to the right starting at column K.
Some samples of the data from the comma separated file:
K L M N O
08/17/2016 08/19/2016 GH BASS & CO #4385 JEFFERSONVILLOH
[...]
All this will work, but how to do whatever you need depends on getting the
data into LibreOffice correctly in the first place.
_But_ Copy and paste-special adds a ' at the start of the MM/DD/YYYY date.
That happens if you paste text into cells previously formatted as number,
date, and so on. Note also that Paste Special... (as that ellipsis
forewarns) is not a single process but gives you a range of choices of what
is pasted and what is not. So you have not clarified what you did here by
referring only to "paste special".
Changing the format for columns A and B to date doesn't help.
Changing the format of cells never changes the data already in them. Since
you now have text in these cells, you cannot magically convert that to
dates (numbers) by changing the format of the cells. (But you can do so
easily using the VALUE() function.)
I trust this helps.
Brian Barker
--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-uns
ubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be
deleted
Privacy Policy |
Impressum (Legal Info) |
Copyright information: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License.
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (
MPLv2).
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our
trademark policy.