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:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted

Reply via email to