Question exporting dates to csv format

[Amylou007]

Hi

I currently have table that has a date field in the following format

yyyy-mm-dd

The problem is that when I export it to csv it becomes dd/mm/yyyy 00:00

I need the date to retain the format yyyy-mm-dd but the only way I can do this is to make it a text file prior to export. This is a problem as it then exports the field as a text field which is then unrecognised by the system I am trying to the data into as it expecting to find numbers and not text in this field.

The only way I can find to get around this is to export it as text and then use notepad to do a find and replace to remove the quotes around the field. this is time consuming when handling a large number of records

Has anyone got a alternative method that thay could suggest-

 

[namliam]

2 ways around this
1) using the format function...
Like you are doing, but use an export specification. In that specification there is the option of not having " around text fields.

2) ISO format
In the same export specification there is the option to change the order of your date fields. This is now set to DMY, change it to YMD, this though may still include the time part, where option 1 will guarantee not have that...

A third less desireably option could be to make your own code for making the CSV export if above 2 fail.

Good luck ! And welcome to AWF

 

[Amylou007]

Thanks for the suggestions.......

The problem with the first method is that with in the file there are text fields that are required to remain formatted as such, with the " If I specify to remove this formatting it applies it to all fields and not individual ones and thus I can't use this sollution

the second sollution does still include the time parameters so again I am still resulting to find and replace techniques within note pad

I have attached some example records of what I am trying to acheive.....

I can get the data to look correct in the table but the problems arise during export. Even the field highlighted in red is causing some issues in that I have to specify it as a Double Number field with 0 decimal places in order to get it to import but when I export it the number writes out with x2 decimal places.

23,"I",1,100062124393,"3605X00124555",2009-04-18,2009-04-18,2009-04-18,2009-04-22,"PL100739","3605TT"

23,"I",2,10012064628,"3605X001245144",2009-04-24,2009-04-24,2009-04-24,2009-04-22,"PL101334","3605TT"

23,"I",3,10012064087,"3605X001248575",2009-04-24,2009-04-24,2009-04-24,2009-04-22,"PL101345","3605TT"

Is there a way around it using Excell? As I can get excell to get the numbers and dates correct but it will not put the " around text fields....

 

[DCrake]

Can you post a copy of the table that you want to export?

David

 

[namliam]

One alternative that always works is to make your own procedure that will write the file to a drive...

I personaly dont see a need for "" around strings in the CSV, This is only required if you strings may contain the seperator (,). If that doesnt happen, dropping the "" will not be a problem...

Again if all else fails, fall back to creating your own procedure... Using things like Open, Write/Print, close to open the file, write the lines and Close the file.
In this situation you can determain exactly what it looks like.

 

[Kiwiman]

Howzit

I had a similar problem when doing a migration job for Great Plains - all the dates were coming across as dd/mm/yy hh:mm - but I only wanted dd/mm/yy.


I got round this by changing the datatype in the specification I was using to export the data, 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 00:00 - 25/12/09. When i used the export to import into SQL server and other access databases, excel etc, they came in as dates.

 

[Amylou007]

Thanks everyone for the advice I have resulted to concating the data into one field using a query in editing it under the SQL window as follows

SELECT CStr(Field1) + ",""" + CStr(Field2) + """," + CStr(Field3) + "," + CStr(Field4) + ",""" + CStr(Field5) + """," + CStr(Format(Field6,'yyyy-mm-dd')) + "," + CStr(Format(Field7,'yyyy-mm-dd')) + "," + CStr(Format(Field8,'yyyy-mm-dd')) + "," + CStr(Format(Nz(Field9,""),'yyyy-mm-dd')) + ",""" + CStr(Field10) + """,""" +CStr(Field11) + """" AS Data
FROM Test;

this formats it correctly when I export it

Unfortunately DCrake I cannot post the table as its copyright protected and the information I used in the examples were dummy records.

The only reason I retain the "" is because the software data loading tool that processes these records falls over if they are not included and this id the official format specification that is reuired by the data holders

 

[namliam]

No need to CStr each field I think?
Definatly no need to CStr(Format()), format returns a string!

Useing a select to build one column is also possible, LOL, why didnt I think of that 2 weeks ago?? I created code using Open/Write/Close to write my own csv file... lol....