• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 134
  • Last Modified:

Access Export to csv or tab Delimited.

How can i get access to export data so that when opened in excel the data of a field looks like the data that was in an access memo record.

For Example, the below text is what is saved in a record in access. The export has several columns this is just one field. I need to be able to export out the data so when opened in excel as a txt or csv file the data below stays in one field and one record. This is because this data is being imported in QuickBooks and needs to stay in one record.

Found angle stop leaking  won't turn off
Found tank to bowl leaking
Found closet ring rusted out.  Causing wax ring to fell. Leaking
Found major discoloration on ceiling at entry way.
Found ball valve in garage will not turn off water to house


Installed new angle stop
Installed new supply line
Replaced tank to bowl gasket

This job is not complete.   This job is not complete
we are submitting a proposal for remediation and buildback.
  • 3
  • 2
  • 2
  • +2
1 Solution
Jeffrey CoachmanMIS LiasonCommented:
Line breaks are not interpreted by CSV/Tab delimited files.

To me this looks like it should be in separate tables/records anyway.




with your data stored like this, you can concatenate these records together then insert them into your Quicken data.

Lets see if another expert can help with a solution that keeps your data in its current format.

jb702Author Commented:
i don't think your statement "Line breaks are not interpreted by CSV/Tab delimited files." is true.

If you take some data and you put in a cell in excel and where you want a line break, you press alt+enter. The save the data as a csv or tab file, the import file will import into quick books.  Hmmm. i just tried that and it works with a .csv file but not a tab file.
Robert ShermanOwnerCommented:
You could try this, which came from Dirk Goldgar original source at
MS Access 2013 truncates longtext fields to 255 characters when exporting to excel?

Sub CopyToExcel(TableOrQuery As String, WorkbookPath As String, Optional WorksheetName As String)

    Dim rs As DAO.Recordset

    ' Note: the following declarations use early binding, so they require
    ' a reference to be set to the Microsoft Excel? <version> Object Library.
    ' Late binding could be used instead.?
    Dim appExcel    As Excel.Application
    Dim wb          As Excel.Workbook
    Dim ws          As Excel.Worksheet
    Dim iCols       As Integer

    Set rs = CurrentDb.OpenRecordset(TableOrQuery)
    Set appExcel = New Excel.Application

    Set wb = appExcel.Workbooks.Add()
    Set ws = wb.Sheets(1)

    ' Create a worksheet header row from the recordset's field names.
    With rs
        For iCols = 0 To .Fields.Count - 1
            ws.Cells(1, iCols + 1).Value = .Fields(iCols).Name
    End With
    ws.Range(ws.Cells(1, 1), ws.Cells(1, rs.Fields.Count)).Font.Bold = True
    ' Starting on the next line of the worksheet, copy each row from the
    ' recordset to a new row in the worksheet.  Excel provides a simple
    ' method call to do this.
    ws.Range("A2").CopyFromRecordset rs
    ' Size columns to fit the data.

    ' Name the worksheet.
    If Len(WorksheetName) > 0 Then
        ws.Name = WorksheetName
    End If

    ' Save the workbook.
    wb.SaveAs WorkbookPath
    ' Terminate the Excel application.

End Sub

Open in new window

To use, paste the above code into a VB code module.  You can then run it from the immediate window by typing in

CopyToExcel("Insert name of table or query here" , "insert path and filename to output to")

for example:

CopyToExcel("MyTable", "C:\myexcelfile.xlsx")
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Gustav BrockCIOCommented:
This is what Access does.
The attached file is exported from a query. Field Note is the original field, in Note10 the usual CrLf is replaced with Lf only:

    Replace([Note],Chr(13) & Chr(10), Chr(10)) AS Note10

When you open the file in Excel, it is correctly formatted for both note fields:
ExcelCSV.PNGSo your issue seems related to Quickbooks which, at least, cannot handle the first format (field Note).
Thus you could try to import the other format - field Note10.

Robert ShermanOwnerCommented:
... and from there if you need a csv file for Quickbooks, you can save to csv from Excel.  I don't know if Quickbooks will import Excel xls/xlsx files directlly, but use that if it's an option instead.
Robert ShermanOwnerCommented:
EDIT: sorry, leave the parenthesis out when running the Sub -- so

CopyToExcel "MyTable", "C:\myexcelfile.xlsx"
When you use the Export Icon in the Ribbon bar, check the box to preserve formatting and layout.  This will keep your linefeeds in Excel.  You can then save this export and rerun it again via code or by clicking on the Saved Exports Icon on the Ribbon.
saved exports
jb702Author Commented:
Thank you
Gustav BrockCIOCommented:
You are welcome!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now