473,320 Members | 1,831 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
Jun 6 '19 #1
7 2300
zmbd
5,501 Expert Mod 4TB
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.
Jun 7 '19 #2
NeoPa
32,556 Expert Mod 16PB
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 (,).
Jun 7 '19 #3
zmbd
5,501 Expert Mod 4TB
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"
Jun 7 '19 #4
ZZEE
3
It looks like all fields in access are data type "Text".
Jun 7 '19 #5
ZZEE
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!
Jun 7 '19 #6
zmbd
5,501 Expert Mod 4TB
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
Jun 8 '19 #7
NeoPa
32,556 Expert Mod 16PB
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')
Jun 9 '19 #8

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

Similar topics

0
by: Adam | last post by:
Hi all, Does anyone know of a php script that I can use to put on a page to allow the user to click a button to export all tables in csv format ? I'd really appreciate any help you could give....
2
by: Mello | last post by:
Mello I am not sure if this is eaven possible but I need a script to quickly export selected tables from a SQL database into Access. The script should preferably run from an ASP page on demand...
5
by: Jandre | last post by:
To anyone that can help I have 2 MySQL databases that contain large amounts of tables. I need to be able to compare the data in the tables with older/newer versions of the tables. I figured the...
4
by: Paolo | last post by:
Friends, I need help with some code to export different tables to a single spreadsheet in Excel. My excel file is named REPORT and the spreadsheet is named CLIENTS. I do have the code to export...
2
by: megann | last post by:
Hi, My name is Megan and I am 21 and just learning to use Access. I was hoping that someone would be able to help me. My problem is that I have created a database with multiple tables (over 10)...
4
by: dreaken667 | last post by:
I have a MySQL database containing 16 tables of data. Each table has a different number of columns and there are few common field names accross tables. I do have one master table with which I connect...
2
by: danorourke99 | last post by:
Hi, In my Access database I have a table (dbo_000_DataCubeProcessing) which contains a list of tables that I need to export to Excel on a regular basis along with a checkbox for each. Once I...
6
by: clloyd | last post by:
I have a database with over 20 tables. I need to delete one to many record(s) in all tables by using a loop code. Some tables with have one record, multiple records or no records for the criteria...
6
by: srikanya | last post by:
Hi, I have a database with 10 tables and I want to export all table data into excel file.But there should be only one excel file with different sheets in it, each containing different tables data.
5
by: fjg49849 | last post by:
Hi all, Took a CSV file which was then imported into a temporary table. Separated the initial table into 3 tables with width defined for each field. Now need to export data into one TXT file. ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.