Hi :) Can find&replace get rid of the ' marks to make the values revert to 'numbers/dates' rather than being forced into being text? Regards from Tom :)
On 9 October 2016 at 18:00, Brian Barker <[email protected]> wrote: > 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: [email protected] > 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 > > -- To unsubscribe e-mail to: [email protected] 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
