Re: [libreoffice-users] Re: Apostrophe prepended to MM/DD/YYYY

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-unsubscribe/
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