Solved

Access Export to csv or tab Delimited.

Posted on 2015-02-11
9
69 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
 
LVL 49

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 49

Expert Comment

by:Gustav Brock
ID: 41232537
You are welcome!

/gustav
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now