Solved

Export Access table to csv file

Posted on 2009-07-08
21
1,419 Views
Last Modified: 2013-11-27
I am exporting an Access table to a csv file.
The problem I am having is that one of the fields which is defined as text has numbers in like
009900
it is exporting like 9900
I need it to be 00900

this is what I am using to exp0rt

 DoCmd.TransferText acExportDelim, "spec", "ExportTable", stOutputName & ".csv", False
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
  • 12
  • 7
  • 2
21 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24803908
create a query against ExportTable (select all fields) and specify that field as text with  cstr([nameoffield])  save the query and use it for export instead of the table.
0
 

Author Comment

by:Pdeters
ID: 24804323
not all fields have a value - having a problem with the null fields exporting
0
 

Author Comment

by:Pdeters
ID: 24804326
I receive an error
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24804365
use the nz() function

nz([fldName],"")  '< for text data
nz([fldname],0)   ' < for number

to use the cstr()

        cstr(nz([fldName],""))
0
 

Author Comment

by:Pdeters
ID: 24804604
Still not getting the leading 00 on the text fields - it is dropping them
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24804618
can you upload your db?
0
 

Author Comment

by:Pdeters
ID: 24804654
I will need to take some stuff out
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24804734
before you do, have you tried exporting using fixed width?
0
 

Author Comment

by:Pdeters
ID: 24804945
I am using the button on the form
0
 

Author Comment

by:Pdeters
ID: 24804952
Using Button on Main Form
db1.mdb
0
 

Author Comment

by:Pdeters
ID: 24805358
yes I am exporting with fixed width in the specs
0
 

Author Comment

by:Pdeters
ID: 24805366
I need export in fixed widths
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 300 total points
ID: 24805466
use this line then

       DoCmd.TransferText acExportFixed, "NewExport", "QryExport", stOutputName & ".csv", False


now, open your table in design view and fixed the Field size property accordingly.
create a new Export Spec and use the new spec in your export.
0
 

Author Comment

by:Pdeters
ID: 24805716
I need to have it be able to open up in Excel in separate columns - it has to have the extention .csv
0
 

Author Comment

by:Pdeters
ID: 24805979
when I do the acExportFixed it goes into one field/column in Excel
0
 

Author Comment

by:Pdeters
ID: 24806062
Is this possible?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24806249
must it be a .csv file?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24806676
Pdeters,

First things first.
The Output file: c:\ExportFile.csv *DOES* have the leading zeros.

If you open the file with *NotePad*, you will clearly see the leading zeros.

If you open the CSV file with *Excel*, Excel will convert these values to numbers.

So the ultimate question is what application will these CSV files end up going to?
If Excel, then why not export to the Excel format?

If they are going to some other program, you have to test the file in this application to see if they will be *USED* in this program correctly as Text (with the Leading zeros)

You cannot tell anything form "Opening/viewing" the file, because any program to view the file may have it's own filters. (like Excel)

You have to actually test the file in its target application and veiw the output to be sure.

JeffCoachman
untitled.JPG
0
 

Author Comment

by:Pdeters
ID: 24831029
Yes - If I open with note pad the leading 0 are there but I gues I am not being clear.
the leading 0 need to be there in Excel but the file extension needs to be csv.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24831243
a work around

use this in the query

select IIf(Left([FieldI],1)="0",Chr(39) & [FieldI],[FieldI])
from tableX
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 200 total points
ID: 24831975
Pdeters,

Thanks, it always helps to clarify things.

Also, always state what version of Office you are using as there are Major differences between 2007 and previous versions.

If you are using Office 2003 you can simply Export the Table in the Excel format, then simply change the extension to ".csv" afterwards.
When you open the file with Excel 2003 you will see the Leading Zeros.

This works absolutely beautifully for me in Office 2003:

Private Sub Command68_Click()

Dim stOutputName As String
       
    stOutputName = "c:\ExportFile"
   
    DoCmd.TransferSpreadsheet acExport, , "Table1", stOutputName & ".xls"
    'Change the extension
    Name stOutputName & ".xls" As stOutputName & ".csv"

    MsgBox "Exported", vbOKOnly, "File Exported"

End Sub


JeffCoachman
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

752 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