Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

suppressing time when exporting dates to textfile (csv) 3

Status
Not open for further replies.
uscitizen

uscitizen

Technical User
Jan 17, 2003
672
US
i need to have dates expressed as YYYYMMDD in my export (csv) file. i created a test mdb and defined a date/time field with input mask 99/99/99 and format YYYYMMDD. when exporting the test values to the csv file, i find that my date (in this case 20040309 -- which is being displayed as such in the a2k table) is getting exported as 20040309 00:00. i don't want the time in the output file. what more do i need do? anybody know?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln
 
Sort by date Sort by votes
Hi

In stead of experting direct from teh table you could export from a query, in the query you have the date as a calculated column MyDate:Format(YourDateCol,"yyyymmdd")

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
us,

What you SEE in Date/Time field is a FORMAT. Just change the FORMAT. The underlying data is a number like 37456.0123.

:)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
ken, i don't want to use a query if i can export directly from the table. thanks i am trying to skip that.

skip, how'd you accomplish that ?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln
 
Upvote 0 Downvote
hi guys,

skip: ken has suggested we use MyDate:Format(YourDateCol,"yyyymmdd") from a query, can your "Year([ReqmtDate]) & Month([ReqmtDate]) & Day([ReqmtDate])" approach be used from within the table itself? if not that, then ken's suggestion would get my vote simply because there's less wear 'n tear on the keyboard.



“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln
 
Upvote 0 Downvote
skip, the plot thickens....

when i enter the string (using my variable's name, Last_TX_Date) in the Format part of the design for this field, it shreds it, e.g

y"ear([La"s"t_TX_"d"ate]) & "m\on\th"([La"s"t_TX_"d"ate]) & "d\ay"([La"s"t_TX_"d"ate])"

the above is what happens after i entered it.

did i not put it into the right part of the date field's design properties?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln
 
Upvote 0 Downvote
What you have in your TABLE, Last_TX_Date is a DATE and THAT field cannot be changed.

In your Table Query, the results of which you will EXPORT to a .csv file, you will have one column with the field defined as
[tt]
Year([Last_TX_Date]) & Month([Last_TX_Date]) & Day([Last_TX_Date])
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
hi, skip:

okay, that's kind of what i had a sneaking suspicion you were saying all along-- then would

Format(YourDateCol,"yyyymmdd")

in one column of the query you're referring to do the same job?

if so, then once again, my vote'd go with the one above in the interest of saving wear and tear on the keyboard.

presumably, in both scenarios there would have to be a new column created in the query. if not that, then i might vote for your vs. ken's.

in either case, i think both are useful and deserve a red one :)

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln
 
Upvote 0 Downvote
Ken's would work just fine!

The difference is that the output in Ken's case IS a DATE.

Mine is NOT a Date -- it's a string.

So it all depends how the .csv will import the DATE.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
yeah, i started out with reference to exporting a date field from my mdb test database to a text (csv) file. but it's interesting that one can convert text property values into time -- computer alchemy!

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln
 
Upvote 0 Downvote
what is they say....america and britain are two countries separated by a common language? perhaps this has gotten a little knotted, or lost in translation? while i would enter a date into my mdb, it would not matter a hoot if it were exported as straight text in the csv file. my downstream people want a csv file in which any field which is a date field has the property that it appears as YYYYMMDD in the text file.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln
 
Upvote 0 Downvote
i wonder if i can re-open this.....

i looked at the resulting csv file created by the export and noticed that when using the following in my query:

Subm_Date1: Format([Subm_Date],"yyyymmdd")

if the value entered in the table is 01/01/01 (January 1st, 2001) the resulting value is written to the csv text file as

"20010101"

with the quotations.

when i use instead

Subm_Date1: Year([Subm_Date]) & Month([Subm_date]) & Day([Subm_Date])

the resulting value written to the csv text file is

"200111"

also surrounded by quotations.

i am generating a file for another group and their interest is in seeing a date written as

20010101

notice that there are no quotations surrounding the date in their spec'ns.

of course there are many many other values in the table that this date belongs to and the separation is with commas, but my downstream folks' requirements seem to preclude quotations around the YYYYMMDD information in the text file they've requested.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln
 
Upvote 0 Downvote
Hi

The reason for the difference when outputting 01/01/2001 (or indeed and date where the day number or month number are less than 10) is obvious if you look at teh actual statement you used (ie Month() and Day()), in help. This is why I suggested Format(), as well as the reduced number of key strokes, we Brits are an idle lot!

Well we did say that the output would be a string, and the consequence of this is that in the csv file strings are bounded by ".

If the recipients of your csv file cannot live with that, then another possibility is to set the format in the tabledefinition to yyyymmdd this may work, I have not tried it, if still no joy, then I think you will have to dispense with using DoCmd.TransferText and do it the hard way, ie write a text file by opening a text file and writing to it, this may be accomplished using OPEN (see help), or by using the new filesystem object.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
g'day,

i used each of the members of the pair of suggestions you guys proferred and the results were indistinguishable; as to looking at the properties of the statements i described in my posting nearest above, i didn't understand what i should be looking for (did you think you brits held a patent on idleness?). at any rate, since the mdb i'm creating is 'simply' a vehicle as it were to output a csv text based data file which will be uploaded to another server, i reasoned that there's no fundamental reason to assign the 'datetime' property to the fields taking date information since -- by that i mean, i converted each datetime field to longinteger number and will probably instruct the data enterer to enter the date as YYYYMMDD in it -- on exporting, the entered information it's appearance is exactly what my downstreamers want to see. problemo licked, i think.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln
 
Upvote 0 Downvote
Hi

Maybe, maybe not.

What is to stop your users from entering invalid dates ? eg 20041335, this will no doubbt upset those down stream of you.

If you use a datetime datatime, it will only accept a valid date.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MrStohler
  • Locked
  • Question
Beginner Q: How to separate data from Excel into related tables and maintain relationship? 1
Replies
5
Views
442
SkipVought
S
ameedoo3000
  • Locked
  • Question
Send a message to an email
Replies
2
Views
2K
ameedoo3000
ameedoo3000
TrekBiker
  • Locked
  • Question
Prevent new data being used when using Archive backend
Replies
0
Views
2K
TrekBiker
TrekBiker
monkeysee
  • Locked
  • Question
Field to count specific records
Replies
3
Views
375
dhookom
dhookom
puforee
  • Locked
  • Question
Error when importing Excel with blank dates
Replies
4
Views
278
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top