Question export table to txt => Date becomes Date & Time (1 Viewer)

[ginkgo]

My config. :
Windows XP Home Edition
5.1.2600 Service Pack 3 build 2600
Access 2000
msjet40.dll V 4.00.9511.0

Struggling with this problem:
Converting a table to delimited text, the Dates are no longer in the DMY format, but get Time as extra. :
YME;140503-000;;G;W;12042402;;24/04/12 0:00:00;26/04/12 0:00:00

All I could do is remove the ":" delimiter for Date
YME,140503-000,,G,W,12042402,,24.4.2012 00000,26.4.2012 00000

Using "Save formatted", you get a pseudo table one could print on a dot matrix printer. Using a query as input, you can only export with the "Save formatted"

Any hint? I searched the threads, but could not find any answers.

Thank you!
Erwin

 

[Kiwiman]

Howzit

I had a similar problem when using the TransferText method - all the dates were coming across as dd/mm/yy hh:mm - but I only wanted dd/mm/yy - and there was no time factor in the date.


I got round this by changing the datatype in the import \ export specification I was using to export the datefiled, from DateTime (the releveant date field datatype) to Text and formatting it in the required format - in my case DMY.

This exported the values as text excluding the time portion 00:00 - 25/12/09. When i used the exported to import into SQL server and other access databases, excel etc, they still were recognised as dates.

 

[ginkgo]

Hello Pete,

This is good news ! I was afraid I'dd have to resort to other means to "get rid of Time" (who needs Time?).
I use the "Export Text Wizard" => "advanced" to build the Export Specification,
There one can specify Date Order, Date Delimiter, Time Delimiter, Four Digit Years and edit Field Names, but I do not find any possibility to change the Data Type.
I have also been wondering where and how these Export Specifications are stored.
Are you building the Export Specification in a similar way as I do?
I presume the term "Wizard" suggests more direct (and advanced) methods must be available.
Please could you show me where to find them?

Best regards,
Erwin

 

[Kiwiman]

Howzit

Sorry been rather busy. What I have always done is taken a dump of the data you want exported into excel (saved as a CSV file) first and "IMPORT" it into the database - in order to create an import specification (you can use this same import specification to export it out).

Steps - once you have the data in a CSV file:

1. In tables object area right click and select import data
2. this opens the import text wizard
3. Select Delimited option
4. Click NEXT
5. Select First Row contains field names
6. Click Advanced
7. Check the data types on each of the fields - ensuring you change the Date field datatype from Date/Time to Text
8. Save the specification by clicking the Save as button
9. then cancel out the process by clicking cancel twice - you don't really want to import the data
10. Then use the transfertext method to Export your data to csv and use the import specification you just created

Syntax of transfertext is:

expression.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)

So you would have something like this in vba

DoCmd.TransferText [B]acExportDelim[/B], "yourIMportSpec", "yourtable\query", "C:\yourfilelocation\yourfile.csv",TRUE

Alternatively you can build a macro to export it, which has the same arguments as above

 

[ginkgo]

Hello Pete,

This works! Your guiding is really to the point. It took me some time to realise this doesn't work when using the export wizard. It works great using VBA (I didn't try Macro, but that should indeed be no problem as the structure is similar).
The only restriction is the "DD/MM/YYYY" only output format. Of course , I need "DD.MM.YYYY", but that's easy to change afterwards.
The trick of using an import specification for export is brilliant, as the import wizard is much more elaborate.

Much obliged,
Erwin