Solved

Access Export to csv or tab Delimited.

Posted on 2015-02-11
9
94 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 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 50

Expert Comment

by:Gustav Brock
ID: 41232537
You are welcome!

/gustav
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

735 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