473,320 Members | 1,951 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Export Delimited files removes DateTime formatting?

6
I'm hoping that this is going to be an easy one to solve, but I've been trawling google and have had no luck at all!
I'm using Access 2003, and am trying to export the contents of a table into a pipe delimited text file using a macro with a pre-set export specification.
The file exports fine, but one of my columns is a date column. The formatting in the table is dd-mmm-yy, but when I export it becomes dd/mm/yy 00:00:00.
I'm trying to firstly remove the time from the file, and also to get the date format back to dd-mmm-yy. Can anyone give me some pointers?
Jan 19 '09 #1
4 3843
orangeCat
83 64KB
I think that when you export delimited files they become text files. So, Date formatting is no longer applicable.

I also think you should explicitly format your access Date fields
eg BD: Format(BirthDate,"DD-MMM-YY")
and then do the export with pipe delimiters.

If you don't do the Format, the date will be a DD/MM/YY date with time (see the output file)

I tried this with the following query, SQL is

Expand|Select|Wrap|Line Numbers
  1. SELECT ancestor.name, Format([birthdate],"DD-MMM-YY") AS bd, ancestor.deathdate, DateDiff("d",[birthdate],[deathdate]) AS ageAtDeath
  2. FROM ancestor
  3. ORDER BY DateDiff("d",[birthdate],[deathdate]) DESC;
  4.  
The input in the underlying table was:

AgeAtDeathForAccess
bob,10/31/1922,3/13/1976,19492
sam,11/1/1922,3/12/1976,19490
jim,3/22/1944,9/17/1964,7484
]ken,9/9/1999,2/7/2006,2343

The output was :

"bob"|"31-Oct-22"|3/13/76 0:00:00|19492
"sam"|"01-Nov-22"|3/12/76 0:00:00|19490
"jim"|"22-Mar-44"|9/17/64 0:00:00|7484
"ken"|"09-Sep-99"|2/7/06 0:00:00|2343
Jan 20 '09 #2
NeoPa
32,556 Expert Mod 16PB
As orangeCat says (pretty close anyway) you need to create a query based on the table and export the query (using Format([Field],'dd-mmmm-yy')).

This is because exporting does not format the data. A Date/Time field is simply a real number which, when displayed, is treated as a Date/Time value and formatted as such.

Welcome to Bytes!
Jan 20 '09 #3
klaul
6
Thank you very much - orangecat, your solution worked pretty much as you described. Thanks!
Jan 20 '09 #4
orangeCat
83 64KB
@klaul
Happy to be helpful.
Jan 20 '09 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Melanie | last post by:
Hi For my company I work for I have to read and process files from Excel, Access or MS SQL. This way is no poblem. I use the JDBC/ODBC bridge to make a delimited file (*.csv) and work with that....
14
by: atse | last post by:
Hi experts, I retrieve data from the database and display on ASP, then I export these data to a file, like Excel (the best) or text file. Is it possible? I think it is possible, but how can I do...
1
by: frankvfox | last post by:
I need a code sample using VB ASP.Net 2003 which exports a tab delimited text file resident on the server into an Excel spreadsheet which will be saved through the browser to the client's local...
3
by: nologin | last post by:
Is it possible to export data exposed by DataView to Excel file for example ? Seba
1
by: Stella | last post by:
Help!!! Am extremely new to VBA. Want to set up code behind a button that will prompt the user for a table name(source of data) and file name(end result of export) and then export the data into a...
7
by: mattc66 via AccessMonster.com | last post by:
I need to create an EDI 850 Record Set. The EDI 850 Record set I am creating consists of 2-5 seperate tables all linked by the CustomerID and CustomerPO. Tables - tblOrder - >Link CustomerID...
3
by: t8ntboy | last post by:
I am migrating data from one database to another. The process requires that I significantly modify the table and data structure. Therefore, I am exporting the data to a csv, making the adjustments...
3
by: =?Utf-8?B?YzY3NjIyOA==?= | last post by:
Hi all, I have a question for you. I have a .csv file which has many lines of data. Each line has many data fields which are delimited by ",". Now I need to extract part of data from this...
5
by: situ | last post by:
Hi, I have Db2 ver 9.5 for LUW , running on two different system, one is linux and other on windows and have the table with same defination on both the system. i exported blob column of the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.