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: - Option Compare Database
-
Option Explicit
-
-
Public Sub ExportDatabaseObjects()
-
On Error GoTo Err_ExportDatabaseObjects
-
-
Dim db As Database
-
Dim td As TableDef
-
Dim d As Document
-
Dim c As Container
-
Dim i As Integer
-
Dim sExportLocation As String
-
-
Set db = CurrentDb()
-
-
sExportLocation = "C:\File Path\"
-
-
For Each td In db.TableDefs 'Tables
-
If Left(td.Name, 4) <> "MSys" Then
-
DoCmd.TransferText acExportDelim, , td.Name, sExportLocation & "Table_" & td.Name & ".csv", True
-
End If
-
Next td
-
Set db = Nothing
-
Set c = Nothing
-
-
MsgBox "All database objects have been exported as a csv file to " & sExportLocation, vbInformation
-
-
Exit_ExportDatabaseObjects:
-
Exit Sub
-
-
Err_ExportDatabaseObjects:
-
MsgBox Err.Number & " - " & Err.Description
-
Resume Exit_ExportDatabaseObjects
-
-
End Sub
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.
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 (,).
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"
It looks like all fields in access are data type "Text".
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!
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] - SELECT Table1.exmpletext
-
, Replace([Table1].[exmpletext],"A","xxx") AS CrrctedTxt
-
FROM Table1;
More complex replacements will take a bit more work; however, same concept
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 : - The SQL view is best for sharing your query with others but is not the default view when it's opened for design.
- 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 : - Replace([YourTextField],Chr(13) & Chr((10),'YourReplacementString') AS [UpdatedTextField]
In the grid view though, it would be : - UpdatedTextField: Replace([YourTextField],Chr(13) & Chr((10),'YourReplacementString')
Sign in to post your reply or Sign up for a free account.
Similar topics
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....
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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...
|
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...
|
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.
|
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.
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |