Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 8 of 8 total |
Importing a CSV file with Date Columns |
Wed, Feb 29 2012 2:39 AM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Tim,
I have a daily CSV file that I need to import into a table. The date column is formatted as YYYYMMDD. Is it possible for you to add the YYYMMDD format to the Import Table statement - to save me adding the dashes in Excel each day. Thank you . . . . Richard Harding |
Wed, Feb 29 2012 5:16 AM | Permanent Link |
IQA | Hi Richard,
I'm not sure if I've misunderstood your question, but can't you specify the DATE FORMAT clause? <DateFormat> and set the format to that same format as your Excel sheet? If the DATE FORMAT clause is not specified, then the default date format is the ANSI SQL standard date format 'YYYY-MM-DD'. Hope that helps, Phil. |
Wed, Feb 29 2012 2:19 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Richard,
<< Is it possible for you to add the YYYMMDD format to the Import Table statement - to save me adding the dashes in Excel each day. >> As Phil indicated, you can do this yourself via the DATE FORMAT clause: http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=IMPORT_TABLE -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Mar 1 2012 1:50 AM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Thanks Phil and Tim
My CSV file looks like this. AAC,20120201,1.4,1.415,1.39,1.4,695786 The following statement gives the error ElevateDB Error #904 Error importing the file C:\ASXData\20120201.csv into the table Stock at line 1 and column 5 (Improperly-formatted date value). IMPORT TABLE "Stock" FROM "20120201.csv" IN STORE "ASX_Stock" FORMAT DELIMITED ENCODING AUTO DELIMITER CHAR ',' QUOTE CHAR '"' DATE FORMAT 'yyyymmdd' TIME FORMAT 'hh:mm:ss n' AM LITERAL 'AM' PM LITERAL 'PM' DECIMAL CHAR '.' BOOLEAN TRUE LITERAL 'True' FALSE LITERAL 'False' MAX ROWS -1 If I add the dashes to the date in the CSV file and use DATE FORMAT 'yyyy-mm-dd', I can import OK. Richard Harding |
Thu, Mar 1 2012 1:54 AM | Permanent Link |
Richard Harding Wise Nutrition Coaching | I am using EDB 2.05b6.
Richard Harding |
Thu, Mar 1 2012 6:52 AM | Permanent Link |
Adam Brett Orixa Systems | Richard
You do need the dashes in the CSV file date-field as far as I know. i.e. "2001-01-01" works, "20010101" doesn't. This either needs to be sorted out during the export process, or via some sort of REPLACE routine written to run on the CSV file. There may be a "delimiter" choice in the IMPORT TABLE function ... but I don't think it can be left blank ... though that might be worth playing with, particularly if you can write something in Delphi rather than just using EDB SQL. |
Mon, Mar 5 2012 5:46 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Richard,
<< The following statement gives the error ElevateDB Error #904 Error importing the file C:\ASXData\20120201.csv into the table Stock at line 1 and column 5 (Improperly-formatted date value). >> This is now fixed for the next EDB build (2.08 B4). Thanks, -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Mar 6 2012 6:01 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | <<This is now fixed for the next EDB build (2.08 B4).>>
Wonderful . . . thank you Richard Harding |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |