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.
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
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