Import date with custom format

[JJJoseph]

I'm also having the same problem as others with importing year-end CSV data. I've followed your suggestion about checking the "special numbers" option, and selecting "Y-M-D" date format, but the date format selected has no effect. I still get the US date format (that is, the date data is unchanged when imported into calc).

BTW: I'm not sure if I'm supposed to tag my question onto others of the same type. If it's not good manners, I can post a separate query.

[acknak]

[I've split this off to a new topic. It's always safe to start a new topic if you're not sure that your problem matches an older thread.]

Without having a sample of the data you're importing, any suggestion on how to handle it is going to be a guess. Copy/paste some of the data--or, better, attach a sample file or give us a link to where the data can be downloaded.

[Villeroy]

Open the text file in a text editor (which is a more appropriate type of application to edit plain text data, btw) and paste a few lines so we can see what you are talking about.

[JJJoseph]

paste a few lines so we can see what you are talking about.

Here's a sample from an eBay CSV report (Google, Yahoo, & PayPal all use the same CSV format):

Date Time Time GMT Transaction Status Gross Fee Net
3/31/2011 22:33:46 GMT-04:00 Payment Sent Completed GBP -14.44 0.00 -14.44
3/31/2011 09:49:22 GMT-04:00 Shopping Cart Payment Received Completed CAD 33.19 -1.26 31.93
3/24/2011 23:33:32 GMT-04:00 eBay Payment Sent Completed USD -30.01 0.00 -30.01
3/24/2011 23:33:32 GMT-04:00 Currency Conversion Completed USD 30.01 0.00 30.01
3/24/2011 23:33:32 GMT-04:00 Currency Conversion Completed CAD -30.10 0.00 -30.10
2/24/2011 12:21:29 GMT-05:00 eBay Payment Sent Completed USD -51.00 0.00 -51.00
2/24/2011 12:21:29 GMT-05:00 Currency Conversion Completed USD 51.00 0.00 51.00

OO is unable to convert the dates to standard ISO format (ymd) after importing from the CSV format.

[Villeroy]

Number formats do not matter. With default settings for English(US) and the "special numbers" option you get correct data imported into the spreadsheet and this is all what matters. Once you have the correct data in your sheet you can format all numerals to your liking.

[JJJoseph]

If you look at the sample data above you'll see that the date format is not correct. The data is correct, but it's formatted incorrectly. It's the USA date format, which is difficult to use. So I don't understand your statement that "Once you have the correct data in your sheet you can format all numerals to your liking".

No matter what I do, I can't "format all numerals" as you're suggesting. What am I missing?

[Villeroy]

It is all about a clear separation of data and format. CSV does not contain any information about formatting. If you get the correct values then this is all you can expect from this database exchange format in plain text.
Once you have imported the raw data into some other program you can use all the formatting options offered by the respective program.
If you need to edit text tables you should use a text editor. A good one for CSV and Windows is http://csved.sjfrancke.nl/
If you want to Calculate with imported values, the formatting does not influence your calculations.
If you have to work with the database row sets then you need a database program (which also does not know anything about formatting).
Combining Base and Calc you can import the raw text data into preformatted spreadsheets.

[acknak]

Here's a sample from an eBay CSV report (Google, Yahoo, & PayPal all use the same CSV format):

Hmm, something's odd: where are the commas (or any other separator character) in this "csv" data?

OO is unable to convert the dates to standard ISO format (ymd) after importing from the CSV format.

Well, since your sample data can't be used, here's some you can practice with:

Date,Time
3/31/2011,22:33:46
3/31/2011,09:49:22
3/24/2011,23:33:32
3/24/2011,23:33:32
3/24/2011,23:33:32
2/24/2011,12:21:29
2/24/2011,12:21:29

Copy those lines, then paste into Calc as unformatted text (Edit > Paste Special). You should see the text import dialog window.

Choose comma as the field separator and make sure the "Detect special numbers" option is ON, then OK.

Now, select the dates, Format > Cells > Numbers > Category: Date, Format: YYYY-MM-DD, and OK.

If you switch to View > Value Highlighting: ON, you'll see that all those imported values are shown in blue: they are numeric values in Calc. You can only set a number format for a numeric value; the format is ignored for text values.

[Villeroy]

Here's a sample from an eBay CSV report (Google, Yahoo, & PayPal all use the same CSV format):

Hmm, something's odd: where are the commas (or any other separator character) in this "csv" data?

The google/yahoo/paypal data use to be tab separated. The tabs get lost when pasted without code tags.

[JJJoseph]

OK, I see what you're after. I had pasted the data from Calc. Here's a slice from the same CSV via NotePad, showing the quotes & commas:

"12/30/2011","01:29:00","GMT-05:00","To U.S. Dollar","Currency Conversion","Completed","USD","-52.42","0.00","-52.42"
"12/29/2011","17:36:27","GMT-05:00","Canada Post","Payment Sent","Completed","CAD","-19.78","0.00","-19.78"
"9/30/2011","15:12:37","GMT-04:00","UKService","Web Accept Payment Sent","Completed","GBP","-17.61","0.00","-17.61"

Are you saying that there's a Calc numbers setting that will format the dates? I've tried the all Calc date formatting options. There's no "YYYY-MM-DD" option, but there's "1999-12-31", which I've tried to no effect.

[Villeroy]

Code: Select all

"12/30/2011","01:29:00","GMT-05:00","To U.S. Dollar","Currency Conversion","Completed","USD","-52.42","0.00","-52.42"
"12/29/2011","17:36:27","GMT-05:00","Canada Post","Payment Sent","Completed","CAD","-19.78","0.00","-19.78"
"9/30/2011","15:12:37","GMT-04:00","UKService","Web Accept Payment Sent","Completed","GBP","-17.61","0.00","-17.61"

I can import all the data correctly into OOo 2.4, 3.3 and LibreOffice 3.3. It is a matter of a few clicks, with a German office locale and OOo 2.4 it takes a few clicks more.

German locale and English(US) field options in OOo 2.4:

Code: Select all

40907	01:29:00	GMT-05:00	To U.S. Dollar	Currency Conversion	Completed	USD	-52,42	0	-52,42
40906	17:36:27	GMT-05:00	Canada Post	Payment Sent	Completed	CAD	-19,78	0	-19,78
40816	15:12:37	GMT-04:00	UKService	Web Accept Payment Sent	Completed	GBP	-17,61	0	-17,61

Same data with first column marked as MDY date:

Code: Select all

30.12.11	01:29:00	GMT-05:00	To U.S. Dollar	Currency Conversion	Completed	USD	-52,42	0	-52,42
29.12.11	17:36:27	GMT-05:00	Canada Post	Payment Sent	Completed	CAD	-19,78	0	-19,78
30.09.11	15:12:37	GMT-04:00	UKService	Web Accept Payment Sent	Completed	GBP	-17,61	0	-17,61

[acknak]

... Here's a slice from the same CSV via NotePad, showing the quotes & commas: ...

Ok, great.

So, the steps are the same, you just need to tweak the options:
1) Edit > Paste Special, as unformatted text. Look for the text import dialog window.
2) Text Import settings:

• Separated by: , (comma)
  Text delimiter: " (double quote)
  Quoted fields as text: NO
  Detect special numbers: YES
  Click OK.

3) Check the values with View > Value Highlighting: the dates, times and amounts should all be blue.
4) Select the dates column, then Format > Cells > Numbers > Category: Date, Format: "1999-12-31", and OK.

Here's what I get, just after pasting the data, and then after setting the format (click for larger image):

[JJJoseph]

I'm not sure if we're making progress on this end. I opened the CSV file with Notepad & copied the file into memory, pasted it into Calc using "Paste Special", then followed the rest of your instructions to get this result. All the number values are blue, but the calendar date is still black (and still resists formatting). I tried it several times with the same result each time. If I'm understanding your reply, it' necessary for the dates to be displayed in blue before we can do anything with them.

[kingfisher]

In the import dialogue you need to select that column in the preview at the bottom and change the column type from 'Standard' to 'Date (MDY)'.

[Villeroy]

In the import dialogue you need to select that column in the preview at the bottom and change the column type from 'Standard' to 'Date (MDY)'.

Specifying the column type for individual columns is one option.
In OOo3.3 you can simply choose English(US) as import locale for this specific import and check the special numbers option to interprete numerals that are beyond plain decimal figures (dates, time, currency, true/false, fractions, percent, scientific numerals). In older versions all special numbers are interpreted unless you mark them as column type 'Text'.
In all versions you can set the appropriate application locale to match the data to import before import. Once you have the correct data, you can switch back to your prefered locale.

It is guaranteed that no format setting nor locale option will ever modify your data. This must not happen even if many users would expect this kind of data corruption for mere convenience.
Specifying the column type in the text import dialog has nothing to do with formatting. It's all about how the incoming stream of characters is supposed to be interpreted. All spreadsheets do interprete input in order to work as calculators. There is nothing wrong with it and formatting is completely irrelevant for the functionality of a spreadsheet.
In my above test run against your data the incoming 8 characters "12/30/11" are taken as an US numeral or DMY date which gives the day number 40907 or 2011-12-30 respectively. 40907 can be displayed as "2011-12-30" or "$40,907.00" or "40'907,00€". All formulas, including date/time functions, will always calculate with the same number 40907. WEEKDAY(40907) returns 6 because that day was a Friday and it must not make any difference if you format 40907 as date or not.
In all spreadsheet applications the number 40907 with all its different but equivalent formattings "2011-12-30" or "$40,907.00" or "40'907,00€" is NOT the same value as the text value "40907" or "2011-12-30" or "$40,907.00" or "40'907,00€". These are all different strings of characters belonging to a completely different category of values.

[mgroenescheij]

Hi,

If you omit the "special numbers" than the import cant decide if 10/11/12 is 11 October 2012, 10 November 2012 or 12 November 2010.
The "special numbers" option is to tell OpenOffice that the source is M/D/Y, D/M/Y or Y/M/D the only purpose is to calculate a number.
After the import you use the Cell Format to display the number stored in OpenOffice to display it the way you prefer.

Martin

[Villeroy]

If you omit the "special numbers" than the import cant decide if 10/11/12 is 11 October 2012, 10 November 2012 or 12 November 2010.

No, this is wrong. The locale option determines if 10/11 is taken as 10th of November or October 11th. By default it behaves exactly like keyboard input which also depends on the locale.
SInce version 3.3 you can specify the locale in the import dialog for the particular import. Additionally you can mark single columns as US English or DMY or MDY or YMD.

The "special number" has been introduced because many people do not want their sport results "12-4" or "13:12" to be interpreted as date/times. With the new option you don't have to mark these fields as text.
It affects all numerals that consist of more than digits, thousand separator(s) and a decimal separator (point or comma).
Integer numbers and 1,234.99 (or 1.234,99 respectively) are always interpreted unless you mark the columns as text.

The third option that has been introduced in 3.3 is the "quoted text" option, so you can decide if all quoted fields should be treated as text.

All new options in 3.3 (locale, special numbers, quoted text) do not add any functionality that was not already there in older versions. They save a few clicks when the incoming text does not match the locale settings or when you want certain columns always treated as text.

This is an alternative way to load csv into preformatted spreadsheets:
Load the text file into a text editor, copy the content, paste into a prepared sheet template where the number format locale, the number format and all the other formatting attributes are to your liking. Specify the delimiters and hit OK.

Personally I prefer Base for csv data but Base is less flexible with special numbers.

[JJJoseph]

I finally got it all to work after piecing everyone's advice together. You're right, Calc can do all the right moves, but it takes significant experience to know what steps are needed, such as "Paste Special" or "Detect Special Numbers". Here's the final sequence that works for me:

1) Export year-end data from eBay as a CSV file, save as a local CSV file.
2) Open the CSV file with NotePad (not Calc, nor any other text editor. Only NotePad works correctly)
3) Open a new Calc spreadsheet file
4) Paste the NotePad info into the new Calc spreadsheet as follows:
Go to: "Edit > Paste Special", and paste as "unformatted text" (from the popup menu). Look for the text import dialog window.
5) Text Import settings:
Character Set: UTF-8
Language: Default - English (*your locale*) [sets calendar format]
From: Row 1
Separated by: , (comma)
Text delimiter: " (double quote)
Quoted fields as text: [ ] (no)
Detect special numbers:[x] (yes) [allows user to select dates vs numbers vs text]

Click OK.

This only works when pasting into Calc from MicroSoft Notepad. If I use any other text editor, the "Paste Special" doesn't give the right results. Also, calendar dates convert properly, but clock time ("12:32:18") stays as text. This might be important to some users.

It's horribly complicated compared to Excel, but it works. Thanks to everyone for the help. I could never have figured this out unassisted!

[crusader]

To assist other users in finding your solution quickly, using the green check mark, please mark your first post as resolved.

[acknak]

You can avoid several steps if you simply open the csv file that you've downloaded. When you do File > Open, just make sure that to select "Text (CSV)" as the file type, then select the file name.

Alternatively, I prefer to start a new spreadsheet and then do Insert > Sheet from file. That will work with any text file and take you right into the text import dialog, and it avoids potential difficulties with File > Open.

Also, this is easily the most discussed issue with Calc, as you can well imagine. It comes up here regularly. If you look through almost any of the past discussions, you'll find that there are alternative approaches for handing text import into Calc that provide very easy access once you have it set up; it's perfect if you often need to import the same sort of text data.

I'm not sure what's happening with your times; I had no problem importing your sample data (note blue values in the screen shot).

[Villeroy]

I finally got it all to work after piecing everyone's advice together. You're right, Calc can do all the right moves, but it takes significant experience to know what steps are needed, such as "Paste Special" or "Detect Special Numbers". Here's the final sequence that works for me:

1) Export year-end data from eBay as a CSV file, save as a local CSV file.
2) Open the CSV file with NotePad (not Calc, nor any other text editor. Only NotePad works correctly)

Any plain text capable tool works for me. I can even use Writer, Draw or Impress for this.

3) Open a new Calc spreadsheet file
4) Paste the NotePad info into the new Calc spreadsheet as follows:
Go to: "Edit > Paste Special", and paste as "unformatted text" (from the popup menu). Look for the text import dialog window.

If there is plain text in the clipboard you can also use Ctrl+V.

5) Text Import settings:
Character Set: UTF-8
Language: Default - English (*your locale*) [sets calendar format]

Wrong!
Is this really so difficult to understand? Choose the locale that matches the text you are going to import. Your currently active locale is preselected by default.
If the text has comma decimals you must not choose English. If there are point decimals and 12/31/1999 dates you have to choose US English. If columns are mixed, choose the non-English one and mark individual columns as English.

From: Row 1
Separated by: , (comma)
Text delimiter: " (double quote)
Quoted fields as text: [ ] (no)
Detect special numbers:[x] (yes) [allows user to select dates vs numbers vs text]

Click OK.

This only works when pasting into Calc from MicroSoft Notepad. If I use any other text editor, the "Paste Special" doesn't give the right results. Also, calendar dates convert properly, but clock time ("12:32:18") stays as text. This might be important to some users.

It's horribly complicated compared to Excel, but it works. Thanks to everyone for the help. I could never have figured this out unassisted!

Database admins hate Excel because is does not follow common rules (and because spreadsheet users do not know what they are doing as this topic demonstrates). But MS addicts never give a shit on what their habit means to others who have to work with professional tools.

Apart from all this, the split-to-columns feature, the paste-special-text feature and the direct text file import including Insert>LinkFromFile all use the exact same import dialog to split text into columns and interprete the incoming column data. All these tools work in the same manner importing valid and correct spreadsheet data from more or less consistent plain text sources. The column type and locale setting that is assumed by default behaves exactly like manual input from the keyboard into an ordinary cell with the general number format.

Opening the file directly dumps raw data into a blank unformatted sheet without any assumptions about formatting. Pasting plain text from clipboard allows pasting raw data into preformatted cells if you think that formatting matters somehow. I have tested a hundred of different csv flavours with that import dialog since OOo 1 and it always did the right thing with a few clicks or a few clicks more in order to get correct data from mixed column styles. Of course I do not stumble around cluelessly, ignoring the application I'm using.

In addition, this office suite comes with a database component which can read collections of hundreds similar text files into Calc and into Writer with one single configuration where you specify the decimal delimiter, the column delimiter and the text delimiter. Since nobody uses this import method, it is somewhat underdeveloped but if it works for you, you will never use anything else to get text tables into this office suite. Yes, this will import raw data into preformatted cells linked to dynamically resizing formulas, charts, conditional formattings or formatted Writer fields respectively.

In addition to the addition, this office suite comes with a complete HSQLDB database engine which allows for full featured input forms to edit plain text files in a database environment.