[Solved] Opening CSV in Calc

[involved]

Hi all,Just joined the forums.The reason why is I just installed Open office on to my new windows 7.I had microsoft works 7 installed before OO.When I did my online banking I used to export my monthly accounts into the Works 7 spreadsheet and it worked well I could have the dates in cell A and all other info in cell B,C,D and so on and my balance would show up in cell H,all was okay.
Now with OO I went to export in the same way and went to open up the download csv file and yes it did open with OO spreadsheet, but it was a complete mess it was nothing like works 7 method.
Where am I going wrong Have I got to set the spreadsheet to accept the download.Any help here please and any tips for using the OO.
thanks.dai.

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases your chances of a reply. (TheGurkha, Moderator)

[Villeroy]

csv is plain text with a certain structure which is not standardized by any means. Open the text file in some text editor, copy a few lines of text and paste it here so we can see the structure of those data.

[involved]

Not sure how to copy & paste into this forum?

[Villeroy]

Copy some lines from your text editor and paste into the text box where you write your message.

[TheGurkha]

Not sure how to copy & paste into this forum?

Same technique as copying & pasting every where else. or you can attach an example file:

[Forum] How to attach a document here.

[jtunell1]

I am a new user and hope I am handling this correctly.

I have a very similar problem to that described in the original question. I am also downloading a csv file and then trying to open the file in CALC. My problem is that each row in the downloaded file is loaded into the CALC sheet as one column.
Also, each row is very, very , very long. I tried to copy 10 lines into this posting. The lines were so long you couldn't really tell what it looked.

So I copied the first 13 columns of some rows and pasted it below.
The spreadsheet in CALC had the first 2 rows as blank.
The 3rd row is a date line.
The 4th line contains the column headers from the csv file. Some of headers fit in the area I copied. The rest of the header are spread out over the 4the row.
The rows from CALC are followed by rows copied from WRITER

Hope you have a fix for my problem. Maye like some option I need to select.
Jack


Copied rows from CALC

Blank row
Blank row, followed by following lines
Quote data as of 09/09/2011 3:22 PM ET.
Watch Closely,"Symbol","Note","Last Trade","Tick","Change
false,"ALXN","","$57.86","down","-$1.83","100.000","$50.430","$5,786.00","+$743.00","+14.73%","09/09/2011 03:22PM"
false,"MO","","$26.36","down","-$0.65","185.000","$27.350","$4,876.60","-$183.15","-3.62%","09/09/2011 03:22PM"
false,"ABV","","$32.30","down","-$1.10","160.000","$31.620","$5,168.00","+$108.80","+2.15%","09/09/2011 03:22PM"
false,"ABC","","$38.33","down","-$1.41","120.000","$42.440","$4,599.60","-$493.20","-9.68%","09/09/2011 03:22PM"
false,"AAPL","","$375.34","down","-$8.80","15.000","$346.570","$5,630.10","+$431.55","+8.30%","09/09/2011 03:22PM"
false,"AZO","","$313.43","down","-$2.67","18.000","$287.890","$5,641.74","+$459.72","+8.87%","09/09/2011 03:22PM"

Lines copied from WRITER

Quote data as of 09/09/2011 3:22 PM ET.
Watch Closely,"Symbol","Note","Last Trade","Tick","Change
false,"ALXN","","$57.86","down","-$1.83","100.000","$50.430","$5,786.00","+$743.00","+14.73%","09/09/2011 03:22PM"
false,"MO","","$26.36","down","-$0.65","185.000","$27.350","$4,876.60","-$183.15","-3.62%","09/09/2011 03:22PM"
false,"ABV","","$32.30","down","-$1.10","160.000","$31.620","$5,168.00","+$108.80","+2.15%","09/09/2011 03:22PM"
false,"ABC","","$38.33","down","-$1.41","120.000","$42.440","$4,599.60","-$493.20","-9.68%","09/09/2011 03:22PM"
false,"AAPL","","$375.34","down","-$8.80","15.000","$346.570","$5,630.10","+$431.55","+8.30%","09/09/2011 03:22PM"
false,"AZO","","$313.43","down","-$2.67","18.000","$287.890","$5,641.74","+$459.72","+8.87%","09/09/2011 03:22PM"

[acknak]

Hmm, Calc seems to fail with data fields like this: "$5,786.00"

By my understanding, commas inside quoted fields should be ignored, but they aren't--for me at least.

Maybe someone else knows how to get around that.

Edit: PS:

If I edit the csv data: change the field separators to tab and get rid of the quotes, then it imports fine into Calc.

[mriosv]

Works well in OpenOffice dev 3.4.0 and LIbreOffice 3.4.3.
and convert in a number, if is select in the column head the Englis US format.

[acknak]

Ok, wait, I see now. Thanks, mriosv.

I was selecting the lines, starting with the column header line: Watch Closely,"Symbol","Note","Last Trade","Tick","Change

That line includes an un-balanced double quote, so when I set the text delimiter, all the data goes into one row. Without the text delimiter, the fields separate properly, except for the currency fields.

So you have to check that the column headings have the correct quotes, and fix it (if necessary) before you import it, or remove that line altogether.

[Villeroy]

The following screenshot shows the correct import options after the non-matching quote in the first line has been fixed.

Text import of comma separated US figures in double-quotes.

[jtunell1]

Like to thank ACKNAK. Appears that there was some kind of problem with the first lines in that file. I have now downloaded other data and imported per the directions. No problems.
I guess I should mark this post as solved. However, I had added my question to another posting. Now figure this is a NO NO. Anyway, as far as my problem is concerned, it has been solved.
Thanks again.
Jack T