For example:
11 1/1/2004 =CONCATENATE("insert into foo values (";A1;", '";text(B1;"mm/dd/yyyy");"');")
This worked...
Sorry for the noise, hope it helped someone :-)
Matt
Matthew T. O'Connor wrote:
Hello, I have a problem I haven't been able to figure out. I have a spread sheet I imported from a web page that is several thousand rows long. I am trying to build insert statements from the values in the spreadsheet, but when I try to include the date value it shows up in what I believe is OOo's internal date format.
For example
Col1 Col2 Col3
(number) (date) (concatenated Values)
11 1/1/2004 =CONCATENATE("insert into foo values (";A1;", '";B1;"');")
12 1/1/2005 =CONCATENATE("insert into foo values (";A2;", '";B2;"');")
What actually shows up in Col 3 is: insert into foo values (11, '37987'); insert into foo values (12, '38353');
Obviously, my database doesn't recognize '37987' as a valid date so the insert fails.
Now I did figure out that if I put a single quote in front of the date value ( '1/1/2004 ) the spread sheet then treats them as strings and the insert statements look ok. However with several thousand records and the fact that I will need to do this on a regular basis prevent me from editing all the date values by hand. So my questions are:
1) Is there some what to get the CONCATENATE function to print the date rather than the internal date value?
2) Is there an easy way to automatically update all the date values? I tried playing with macros but didn't get very far with that.
Thank you,
Matthew O'Connor
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
