?
Solved

Access Export to csv or tab Delimited.

Posted on 2015-02-11
9
Medium Priority
?
128 Views
Last Modified: 2016-02-11
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

Repairs

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.
0
Comment
Question by:jb702
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40604980
Line breaks are not interpreted by CSV/Tab delimited files.

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

tblIssues
iID
iDate
iNotes
iReportedBy
...etc

tblSympoms
sID
iID
sText
...etc

tblRepairs
rID
iID
rText
...etc

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.

JeffCoachman
0
 

Author Comment

by:jb702
ID: 40605010
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.
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40605154
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
        Next
    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.
    ws.UsedRange.Columns.AutoFit

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

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

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")
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40605158
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:

SELECT
    tblNotes.FieldName,
    tblNotes.FieldValue,
    tblNotes.Note,
    Replace([Note],Chr(13) & Chr(10), Chr(10)) AS Note10
FROM
    tblNotes;

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.

/gustav
qdyNote.csv
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40605159
... 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.
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40605171
EDIT: sorry, leave the parenthesis out when running the Sub -- so

CopyToExcel "MyTable", "C:\myexcelfile.xlsx"
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40605205
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
Ron
0
 

Author Closing Comment

by:jb702
ID: 41228304
Thank you
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 41232537
You are welcome!

/gustav
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Screencast - Getting to Know the Pipeline

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question