Solved

Export csv file format - Access 2002

Posted on 2008-10-08
16
228 Views
Last Modified: 2013-11-27
I am trying to export an Access 2002 db table in a csv format

I want it to look like this when exported

"field1","field2","field3","field4"

instead I am getting this if i put a txt extension but I want a csv
"field1        ","field2         ","field3","field4       ","  

and if i put the csv extention i get each field in a separate excel cell across
0
Comment
Question by:Pdeters
[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
  • 8
  • 8
16 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22670328
you have to use

docmd.transfertext acexportdelim, "importspecificationName", "tableName", "c:\myText.csv", true
0
 

Author Comment

by:Pdeters
ID: 22670744
where do i put this
Riht now i have the export in a macro
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22670802
create a new macro, using TransferText and fill out all the necessary info
you will need a new export specification


Action
TransferText


Transfer Type           Export Delimited
Specification Name



0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:Pdeters
ID: 22671028
Ok - but where do I put this?
docmd.transfertext acexportdelim, "importspecificationName", "tableName", "c:\myText.csv", true
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22671070
what do you want a macro or vba solution?

this is vba
docmd.transfertext acexportdelim, "importspecificationName", "tableName", "c:\myText.csv", true
0
 

Author Comment

by:Pdeters
ID: 22671908
either
 just so it turns out corectly

Right now I have the macro
as

Action
TransferText
Transfer Type           Export Delimited
Specification Name

In the Specificiation i have it set up as
Delimited
field Delimiter ,
text qualifieer "

and i get
"field1        ","field2         ","field3","field4       ","  

this is what I want

"field1","field2","field3","field4"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22671935
your export spec is telling the export to have a fixed width.


can you attach your db?
0
 

Author Comment

by:Pdeters
ID: 22673247
I checked and and redid the specs - still doing -  i belive it is set to delimited
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22673295
run this update query

update tableName set  field1=trim([field1]), field2=trim([field2]),field3=trim([field3]),field4=trim([field4])


then try the export
0
 

Author Comment

by:Pdeters
ID: 22673453
forgot to attached here it is
Reports.mdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22673590
this is what i got after running the  macro


Export.csv.txt
0
 

Author Comment

by:Pdeters
ID: 22676985
I don't? could it be 2002 versus 2003 version of access?
Is it common to have csv.txt after then end to make it work?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22677290
for uploading file to this site >> .csv is not supported
i created the file export.csv directly i just added the .txt to the file name so i can attach the file here.
0
 

Author Comment

by:Pdeters
ID: 22677399
when i open as a csv i get an excel spreadsheet with each in a different cell. I need this
"field1","field2","field3","field4"

is this possbile.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 22677424
then do not use a .csv as output format,
use .txt
0
 

Author Comment

by:Pdeters
ID: 22679918
I will do that. Sometimes I make things way too difficult.

Thanks
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Empty Record being created 4 42
Access syntax 1 33
Open A Form without Loading All the Records of Its Recordsource 6 45
get and set file atrributes 5 10
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

756 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