[Solved] Import of CSV file not working correctly

[protectivedad]

I noticed this has been commented on but it has been marked as solved and it isn't. When importing a CSV file which contains the lines:
"08/30/2010","08/30/2010","CITI CARDS ONLINE PAYMENT ","-2,328.44"
The program incorrectly processes the line.

Please refer to RFC4180 or the wikipedia entry for CSV format which will tell you that fields can always be surrounded with a " whether needed or not.

This clearly implies that the " are not part of the field but surround the field and should not be included.

I would also suggest that any "field" specific operations for example Column Type not be overridden by general for example Text Delimiter fields.

When properly processed as per RFC4180 the information is:
08/30/2010
08/30/2010
CITI CARDS ONLINE PAYMENT
-2,328.44

The column specific information should then be applied:
Date (MDY)
Date (MDY)
Standard
Standard
as chosen by the operator on the Text Import dialog.

This is very frustrating because right now your Text Import dialog doesn't allow for the importing of standard CSV files.

[Villeroy]

You forgot to mention what goes wrong. I can import
"08/30/2010","08/30/2010","CITI CARDS ONLINE PAYMENT ","-2,328.44"
any way I want. I want 2 US-dates, a text and a US number, so I declare the columns accordingly and get
30.08.10
30.08.10
CITI CARDS ONLINE PAYMENT
-2328,44
with German locale settings.

[protectivedad]

I created a test.csv file with the one line:
"08/30/2010","08/30/2010","CITI CARDS ONLINE PAYMENT ","-2,328.44"

I used the following options on the Text Import screen:
Character Set: Unicode (UTF-8)
Separated by: Comma
Text Deliminator: "
Column Types: Date (MDY), Date (MDY), Standard, Standard

It then imports the fields makes them text. For example the dates and number will have a ' prepended to it to force them to be text. This is with English (US) locale settings.

Last month when I imported my bank file Calc would import it and the dates would be recognized as dates and the numbers as numbers. This month with the same file Calc forces all fields to be text.

[acknak]

OOo considers any field within double-quotes to be text, period.

It's been that way as long as I can remember; are you sure it's not the bank that changed the quoting on their end?

Fortunately, this problem is finally getting some attention. The text import dialog in the next version of OOo (OOo 3.3, due in a few weeks) includes an option allowing the user to specify whether quoted fields are imported as text or not.

BTW, the fact that there is a written specification, or RFC, does not make the format a standard. While I think you could make a reasonable argument that Calc is wrong in always treating quoted fields as text (otherwise, how can you import numbers that include commas?), practically speaking, every application that supports csv, and every csv file, follows slightly different rules.

[Villeroy]

OOo considers any field within double-quotes to be text, period.

I wish it would. My OOo3.2.1 ignores the double quotes. Not ignoring them may be a major improvement in the Linux versions. Unfortunately there is no resource where we can look up the subtile differences between OOo derivates.

The column specific information should then be applied:
Date (MDY)
Date (MDY)
Standard
Standard
as chosen by the operator on the Text Import dialog.

This "auto formatting" will certainly never happen. When you load csv data into your spreadsheet model, the get imported as raw data into a brand new sheet with overall number format "General" (one format fits all). Numeric evaluation follows the overall application locale as set in Tools>Options...Lang>Locale Setting) and you can override these evaluation rules to some extent. Once the data are in the cell grid, formatting is completely up to you.
When you save the file back to text, the defaults are that the file is more or less written as displayed.
AFAIK, next version will apply the explicit import settings when saving the same file back to disk (YMD, DMY, MDY, Text).

OOo comes with a database component which offers at least 2 different ways of database import into the whole office suite. In the end, csv is a simple meta-format or mere convention for database exchange.
Unfortunately, all these methods are far from perfect. Once you've configured how to read your files properly you can import (subsets of) tables into pre-formatted spreadsheet ranges, pivot tables, Writer fields, Writer tables, Writer text or table grids in Draw and Impress.

[protectivedad]

It's been that way as long as I can remember; are you sure it's not the bank that changed the quoting on their end?

Absolutely, positive. I even have the old file from last month which imported properly last month and fails this month. I've been doing the exact same sequence of events every month for the last few years. I just updated to FC13 and the latest OO this month and now it fails.

Putting aside the CSV file format issue, when following a sequence of events usually field specific instructions take precedence over general instructions. I think this is "expected" behaviour and good practice.

This "auto formatting" will certainly never happen.

It's not auto formatting its telling Calc what the data represents. It has nothing to do with how the data is displayed after importing, it is telling the import routine what the field data contains. i.e. Date (MDY) or Date (DMY), etc.

When you load csv data into your spreadsheet model, the get imported as raw data into a brand new sheet with overall number format "General" (one format fits all)

That is where I think you are wrong when you load the csv data you get the raw data plus it is adding a single quote to the beginning of the raw data. It is not interpreting the raw data as you tell it to or as the local settings tells it to it is forcing it to be text. So, numbers are text, dates are text, everything is text.

acknak gets it. It is just for him it has always happened but for me in just started happening after my last upgrade.

[Villeroy]

That is where I think you are wrong when you load the csv data you get the raw data plus it is adding a single quote to the beginning of the raw data

No, not my copy of OOo. It is the vanilla one from download.openoffice.org
It does not respect the text delimiters and with any non-US locale it imports 1/2/1999 as 1st of February 99 with US locale or MDY import option I get January, 2nd.

[Alex1]

I found pasting one line behaves different from pasting more than one line: the text import dialog does not appear, and only the first item is inserted, the rest is ignored. Could that be the problem? Adding an empty line makes the dialog reappear, and it does not overwrite existing data.

[Villeroy]

This nuisance has been introduced in 2.3 or so. Some users wanted to paste a single line as one unformatted text value.

[acknak]

OOo considers any field within double-quotes to be text, period.

I wish it would. My OOo3.2.1 ignores the double quotes. Not ignoring them may be a major improvement in the Linux versions.

Well flip my bits. It's time for me to find another hobby.

You're exactly right; I remembered it 180° bass-ackwards.

And it is different in the OOo from Fedora, which is the one I happened to check. I just figured they were all the same. Sorry for the bum steer. Yeesh.

I went back and tested several OO.org versions (the Linux version built by OO.org): 3.0, 3.1.1, 3.2 and 3.2.1, with comma as the delimiter and using Insert > Sheet > From file. All behave as Villeroy says: the double quotes are ignored. The only change(s) come in the developer snapshot leading to OOo 3.3.

... it is adding a single quote to the beginning of the raw data.

You do realize that the single quote is not part of the cell value, right? It's only a marker that the cell value is text; it's not actually stored in the cell.

Are you guys thinking it's maybe a locale setting rather than the actual csv import? That would make sense to me.

[Villeroy]

The quote inhibits evaluation. In most cases "12/20/1999" is no numeric expression at all. "12/20/1999" won't get the apostrophe unless you import this as a text in US context. In US context it is needed to inhibit the evaluation as 20th of December.
The vanilla versions of OOo never add the apostroph without any user intervention. If it is there then you typed it, or you imported (loaded/pasted through the text import) some numeric expression and marked it as text explicitly.
Oh, and you can use it in the search/replace dialog as well.
Fedora adds it in case of quoted csv text which makes sense.

Find
.+
replace with
&
with option regular expression will re-enter the numeric strings and remove the leading quotes.

[protectivedad]

... it is adding a single quote to the beginning of the raw data.

You do realize that the single quote is not part of the cell value, right? It's only a marker that the cell value is text; it's not actually stored in the cell.

Are you guys thinking it's maybe a locale setting rather than the actual csv import? That would make sense to me.

If I take the following lines:

Transaction Date,Posting Date,Description,Amount
"07/26/2010","07/26/2010","CITI CARDS ONLINE PAYMENT ","-1,745.59"
"07/26/2010","07/26/2010","THIS IS MY TEXT","-27.25"

And paste it into the spreadsheet with the following options:
Seperated by [x] Comma;
Text delimiter ".

The column types are explicitly Date (MDY), Date (MDY), Standard, Standard.

After this if I go to edit the information in the cells that contain dates or numbers the ' is in the cell. The following is a copy and paste from the cell:

'07/26/2010

The quote inhibits evaluation. In most cases "12/20/1999" is no numeric expression at all. "12/20/1999" won't get the apostrophe unless you import this as a text in US context.

If it is there then you typed it, or you imported (loaded/pasted through the text import) some numeric expression and marked it as text explicitly.

I did not type it and as I keep saying I imported it EXACTLY as I have outlined over and over again. If there are " that is denoting a field the import routine will not process the information based on the column type. I can post screen shots and the ODS file if you'd like, but I'm sure it is easily reproducible.

[protectivedad]

with option regular expression will re-enter the numeric strings and remove the leading quotes.

But it doesn't make sense. In the CSV format it is not a "Text delimiter" it is a field delimiter and it is up to OpenOffice to properly process the fields as the user has told it to with the column type setting Date (MDY). And as mentioned under your logic there is NO way to import numbers that use a , as a thousands separator. For example 1,234.45 can NEVER be imported because without the " the comma is seen as a field seperator and with the " it is seen as text only. This is ludicrous.

[RPG]

Hello

It cost me 15 minutes when I tried to import your two lines of code to valid spreadsheet numbers.

There you use FC I assume you use the go-oo version. Then it is also possible to import data as date for you.

First you get a number and then change this to a date.
use :US Enlish.

It can depend from locale style

Romke

[Villeroy]

Do yourself a favour and install the original from download.openoffice.org
I can paste , split, load your text lines without any problems getting the following result with an non-US English locale:

Code: Select all

Transaction Date	Posting Date	Description	Amount
26/07/10	26/07/10	CITI CARDS ONLINE PAYMENT 	-1745.59
26/07/10	26/07/10	THIS IS MY TEXT	-27.25

However:
I consider the behaviour of your office build as consistent. Except Excel no other application sets text delimiters "just for fun" (since decades Excel can not export floating point numbers with all significant digits unless they are declared as text).

CSV is a database format. However, I can not import your csv data correctly into Base for 2 reasons:
1) The text delimiters enforce text import indeed.
2) Without the text delimiters the last column contains numbers with camma as thousands separators beeing equal to the field delimiters.
If I recall correctly, MS Access could not import these data for the same reasons.

[Villeroy]

Alternatively, you can import everything as text (like your Fedora build does) and use the ususal conversion routine:
menu:FInd>Replace...[Ctrl+F]
[More Options]
[X] Regular expression
Search: .+ [a dot and a plus]
Replace: &
[Replace All]

This re-enters all formula contents converting text to numbers in the respective locale context (you can set the cell's number format locale or the global application locale).
In case of number format "Text" (code @) you would convert numbers to text.

[protectivedad]

Thanks, both your suggestions work. I have decided to uninstall the FC13 version and install the downloaded version from OpenOffice as suggested. I now no longer have a problem.