Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1660
  • Last Modified:

Export Access table to csv file

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
Pdeters
Asked:
Pdeters
  • 12
  • 7
  • 2
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
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
 
PdetersAuthor Commented:
not all fields have a value - having a problem with the null fields exporting
0
 
PdetersAuthor Commented:
I receive an error
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Rey Obrero (Capricorn1)Commented:
use the nz() function

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

to use the cstr()

        cstr(nz([fldName],""))
0
 
PdetersAuthor Commented:
Still not getting the leading 00 on the text fields - it is dropping them
0
 
Rey Obrero (Capricorn1)Commented:
can you upload your db?
0
 
PdetersAuthor Commented:
I will need to take some stuff out
0
 
Rey Obrero (Capricorn1)Commented:
before you do, have you tried exporting using fixed width?
0
 
PdetersAuthor Commented:
I am using the button on the form
0
 
PdetersAuthor Commented:
Using Button on Main Form
db1.mdb
0
 
PdetersAuthor Commented:
yes I am exporting with fixed width in the specs
0
 
PdetersAuthor Commented:
I need export in fixed widths
0
 
Rey Obrero (Capricorn1)Commented:
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
 
PdetersAuthor Commented:
I need to have it be able to open up in Excel in separate columns - it has to have the extention .csv
0
 
PdetersAuthor Commented:
when I do the acExportFixed it goes into one field/column in Excel
0
 
PdetersAuthor Commented:
Is this possible?
0
 
Rey Obrero (Capricorn1)Commented:
must it be a .csv file?
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
PdetersAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
a work around

use this in the query

select IIf(Left([FieldI],1)="0",Chr(39) & [FieldI],[FieldI])
from tableX
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 12
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now