Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2007
    Posts
    54

    Unanswered: exporting data from access to csv

    I've created a bunch of tables with dates and other various values.
    I now need to export them to a csv format....BUT. I'd like to export the dates in a different format.
    eg) they are currently saved as mmddyyyy but I need them to look like mm/dd/yy. and there are some other formatting changes that I need to make.
    How can I go about doing this?
    Thx

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Create a query and use an expression (ex: DateExpression: format(MyField,"mm/dd/yyyy"). Then export the query.

    You can use expressions in queries to manipulate data to look like you want it to.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jul 2007
    Posts
    54

    queries

    cool. i'll give that a try.
    thanks.

  4. #4
    Join Date
    Jul 2007
    Posts
    54

    more meaningful column name

    Hi there. i gave it a try and it seems to be working fine on screen. (how do i give the new formatted field a more useful name than EXPRxxxx?)

    When I export it, the formatted date doesn't save. If I choose the "Save Format" option before I click on the export button, it creates a txt file which is not what I'm looking for. I even tried deselecting the "4 digit year" option that appears under the "advanced" button of the Export Text Wizard.

    Any suggestions?
    Last edited by chubbypama; 01-25-08 at 22:15.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    To change the name from ExprXXXX: just replace ExprXXXX: to MyName: (put in whatever name you'd like as MyName before the colon).

    For what you're trying to do, you should just be able to open the query, Click File -> Export and export to a *.csv file. You shouldn't get a "Save Format" unless you clicked the "Save Formatted" checkbox. Make sure you select Text File as the type. (not rich text format) and for a filename put something like MyFile.csv. In the wizard, select Delimated, and then comma separated (you shouldn't need to click the advanced button.) Again, leave the "Save Formatted" checkbox unchecked.

    Keep in mind that if you are opening the *.csv with Excel, you may have to change the formatting of that column in Excel as Excel may be interpreting it. Try opening it with notepad or change the format of that column in excel.

    You can also create an export specification. See the help on creating a specification. But you shouldn't need to do this for what you're doing unless you want to export the table instead of the query.

    A *.csv file (comma separated value) is the same thing as a text file (a *.csv is the same as a *.txt just refered to as csv - most systems interpret opening *.csv files with excel as the default program). Opening with notepad shows the file without any interpretation which excel likes to do (especially on date type field values). The text values in the fields are usually surrounded with a " and the fields are separated by ,
    Last edited by pkstormy; 01-25-08 at 23:02.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    exporting to CSV from Access does have several longstanding bugs / problems associated with it, especially int he formatting area. one of which involves dates. I forget the range and type, but I found in a non US environment exporting to CSV could be such a pain, that these days I 'just' write a CSV file myself and avoid using the inbuilt export functions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •