Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160

    Exporting data to Ms Excel Comma Separated Values

    Hi Guys,



    I am working on a simple billing database.

    I was wondering is it possible to export data directly into Microsoft Excel Comma Separated Values. If yes, how can it be achieved.

    Any help is much Appreciated.
    Thanks and Regards
    Deepak Gupta

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    If you want CSV why would you export to Excel? CSV is a text file. Opening a CSV in Excel drops the commas.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    use the docmd.transfertext function to create a .csv. As June says, this can be opened by Excel and drops the comma's

  4. #4
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Hi June & Ajax,

    Thanks for your help and suggestions, but the issue I am facing is the formatting. When Exporting in .txt option with keep formatting option on, the format being exported is slightly different in format as desired. But when exporting to excel with keep format option the desired output is perfect (All i am required to do is open the file in excel and save as MS Excel comma separated value file.) The data exported is to be uploaded in different app which is very particular about the format in which data is imported.

    To Explain better, for example I have a field amount (which is defined as Currency in tables, but when making report for export I am defining it as standard format) Now when I export in .txt format data exported shows weired symbols in front of currency fields, which I am sure it is picking up from table properties, but when i export in excel it comes out perfect as per the report format. there are similar irritating bugs in date field also. Hence was wondering if any way to export directly into MS Excel Comma Separated Value file format.

    Thanks and Regards
    Deepak Gupta

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    as already said, there is effectively no such thing as an excel .csv. it is just a .csv file. Formatting is another issue - it is a property and .csv file does not have properties, all fields are text. If you want a specific format for a .csv file you need to do it yourself in the data you are exporting. Suggest you open an 'excel' .csv in notepad and see what the values look like.

    Format is just the way it looks and has nothing to do with the underlying value e.g. 1.2345 formatted to 2dp will be visible as 1.23, but the underlying value is still 1.2345. If you only want to export what you see (i.e. 1.23) then use the round function, or format function for dates.

    I've never looked into it, but it is possible that when you save an excel file as a .csv from excel, it applies some excel formatting properties to the underlying value when it saves e.g. strips off the 45 from 1.2345 from the above example.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Sounds like will have to be two-stage operation. Export to Excel then in Excel do SaveAs to CSV file. Can use application automation code to open the new file created by the export and accomplish the SaveAs.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    could you elaborate on application automation code. Could you please guide to me to a tutorial for the same or give me the code to do the same.

    Awaiting your help.
    Thanks and Regards
    Deepak Gupta


  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Or as the link orange provided shows, write lines to a text file. This would require building a query that has the data you want and open a recordset object, read each field of each record and construct the string to write out to text file.

    To save report to Excel use OutputTo method. Here is very simple example:
    Code:
    Sub ExpRpt()
    Dim xl As Excel.Application
    Dim wb As Excel.Workbook
    DoCmd.OpenReport "Compensation", acViewPreview
    DoCmd.OutputTo acOutputReport, , acFormatXLS, "C:\Users\June\Forums\test2.xls"
    DoCmd.Close
    Set xl = CreateObject("Excel.Application")
    Set wb = xl.Workbooks.Open("C:\Users\June\Forums\test2.xls")
    wb.SaveAs "C:\Users\June\Forums\test2.csv", xlCSV
    xl.Quit
    Set wb = Nothing
    Set xl = Nothing
    End Sub
    Similar code could also be used to export table or query objects instead of report. Can also use TransferSpreadsheet method. Review http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Dear June7 & Orange,

    Thanks for your suggestion.

    June, I was wondering how can i pass the date arguments in the query, which are input by user.

    Thanks and Regards
    Deepak Gupta

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    What query?

    Is the date filter criteria?

    Can use a dynamic parameterized query.

    Can pass filter criteria in WHERE CONDITION of OpenReport method.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Yes, Querry name is qryB2b, Date is the filter criteria. Trying right now, if unable to achieve will come back to you.

    Thanks and Regards
    Deepak Gupta

  13. #13
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Dear June,

    Tried running your code, but it give a vba error saying User-Defined type not defined. The error occurs on line where X1 is set as Excel.application

    Please help thanks.
    REgards
    Deepak Gupta

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Deepak,

    You seem focused on a solution involving Access, Excel and a csv file.
    Can you show us the business requirement so we can see and understand your "requirement" in context?
    You have several ongoing threads that readers are looking at individually. Perhaps a holistic view of the business requirement can identify options for a more coordinated solution.
    Trial and error is rarely a more efficient development strategy than analysis, modelling and design.

    Good luck with your project.

  15. #15
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Dear Orange,

    Thanks for your interest in my posts and helping me solve it. And a special thanks for offering to help me with my project.

    I have been using Excel for my business operation and was able to do work ok, but recently due to some changes in government policies work load got multiplied. Where same information from different sheets needs to be represented in different formats and it involved lot of copy cut paste. And I have always been highly inclined to work with Access, hence thought in place of extending my knowledge in excel programming, shifting to Access.

    I am right now starting with billing portion of the project. Eventually I want to add purchase, inventory, production, and payment tracking. I am hell bent on converting to Ms Excel csv file as my tax lawyer are able to directly access the file into their software for returns purpose, but it needs to be in exact format which I am able to get in query and export to xls file format, but need to convert it to MS excel csv file before forwarding it. I want to achieve the process in single step, so that if some one else also operates he is able to do it with a click of a button. Hence if someone can elaborate on June's code t would be really helpful. Also accounting department has it's own xls format which needs to be done at a much later stage.

    Hope I have been able to explain my requirement Incase you need i can send you the access file, for further guidance.
    Thanks and Regards
    Deepak Gupta

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-23-2017, 03:22 PM
  2. Replies: 5
    Last Post: 09-02-2017, 01:43 PM
  3. Replies: 4
    Last Post: 05-07-2015, 04:02 PM
  4. exporting query to text comma separated
    By Pasi in forum Queries
    Replies: 6
    Last Post: 12-27-2013, 06:42 PM
  5. Replies: 6
    Last Post: 06-26-2011, 12:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums