By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,969 Members | 1,536 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,969 IT Pros & Developers. It's quick & easy.

Help with VBA script to Export Multiple Tables in Access to CSV & Remove Carriage Ret

P: 3
Hello,

I have an access database with 50 tables. I am looking to export all tables into a folder on my computer. The issue I'm having is the data in access has carriage returns or line breaks on some lines. When I open the exported CSVs as a text file, some lines are broken up.

What code am I missing to clean the data during the exporting process? And where does it go in my script?

I currently have this VBA code written which exports all tables successfully:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Sub ExportDatabaseObjects()
  5. On Error GoTo Err_ExportDatabaseObjects
  6.  
  7.     Dim db As Database
  8.     Dim td As TableDef
  9.     Dim d As Document
  10.     Dim c As Container
  11.     Dim i As Integer
  12.     Dim sExportLocation As String
  13.  
  14.     Set db = CurrentDb()
  15.  
  16.     sExportLocation = "C:\File Path\"
  17.  
  18.     For Each td In db.TableDefs 'Tables
  19.         If Left(td.Name, 4) <> "MSys" Then
  20.             DoCmd.TransferText acExportDelim, , td.Name, sExportLocation & "Table_" & td.Name & ".csv", True
  21.         End If
  22.         Next td
  23. Set db = Nothing
  24.     Set c = Nothing
  25.  
  26.     MsgBox "All database objects have been exported as a csv file to " & sExportLocation, vbInformation
  27.  
  28. Exit_ExportDatabaseObjects:
  29.     Exit Sub
  30.  
  31. Err_ExportDatabaseObjects:
  32.     MsgBox Err.Number & " - " & Err.Description
  33.     Resume Exit_ExportDatabaseObjects
  34.  
  35. End Sub
1 Week Ago #1
Share this Question
Share on Google+
7 Replies


zmbd
Expert Mod 5K+
P: 5,380
Are some of the field datatypes "long text" formerly known as "Memo" fields?

You'll find that these fields have some unusual issues.

The other thing that can happen is if there are non-ascii characters in the field data.

If either of these are true, then you may have to step thru each table and port each record's information field by field.
1 Week Ago #2

NeoPa
Expert Mod 15k+
P: 31,347
Consider using queries where all fields are represented by equations that use the Replace() function to replace anything that is not desired with other strings. That way you can ensure that the only time new lines occur is between records. In a CSV file you may also need to do something similar for commas (,).
1 Week Ago #3

zmbd
Expert Mod 5K+
P: 5,380
Neopa's post prompted me to take another look
ZZEE (...) the data in access has carriage returns or line breaks on some lines.(...)
Neopa's suggestion to use the queries with the replace() function may be the best way to transfer your data out without the breaks in the records.
Even if we step thru the fields and pull the field texts out the CR/LF would still be in the text; thus, requiring the use of the replace() or REGEX to strip them out of the string.

Still need to know about the field datatype - are there any "Long Text"
1 Week Ago #4

P: 3
It looks like all fields in access are data type "Text".
1 Week Ago #5

P: 3
If I use the Replace function, would that be written in my VBA script? Or would that be written in macro form? If it is written in my VBA script, where would I put it?

Thanks!
1 Week Ago #6

zmbd
Expert Mod 5K+
P: 5,380
The data type will either be:
"Short Text"
or
"Long Text"
Not just "Text"

For the simplest replacements the Replace() can be used directly in the SQL
For example if we had a table named [Table1] with a text field named [ExampleText] and we wanted to replace every "a" with "xxx" and have that shown in a field named [CrrctedTxt]
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.exmpletext
  2.   , Replace([Table1].[exmpletext],"A","xxx") AS CrrctedTxt
  3. FROM Table1;
More complex replacements will take a bit more work; however, same concept
1 Week Ago #7

NeoPa
Expert Mod 15k+
P: 31,347
ZZEE:
If I use the Replace function, would that be written in my VBA script? Or would that be written in macro form?
No. Neither. That would be directly in the query itself (Check post #3 again).
Query design can be viewed in either of two ways :
  1. The SQL view is best for sharing your query with others but is not the default view when it's opened for design.
  2. The QBE (Query By Example) grid is the default and shows the record sources at the top and fields below from left to right.
In the SQL you would have a line such as :
Expand|Select|Wrap|Line Numbers
  1. Replace([YourTextField],Chr(13) & Chr((10),'YourReplacementString') AS [UpdatedTextField]
In the grid view though, it would be :
Expand|Select|Wrap|Line Numbers
  1. UpdatedTextField: Replace([YourTextField],Chr(13) & Chr((10),'YourReplacementString')
1 Week Ago #8

Post your reply

Sign in to post your reply or Sign up for a free account.