Export Access table to csv file

Pdeters
Pdeters used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

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.

Author

Commented:
not all fields have a value - having a problem with the null fields exporting

Author

Commented:
I receive an error
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

Commented:
use the nz() function

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

to use the cstr()

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

Author

Commented:
Still not getting the leading 00 on the text fields - it is dropping them
Top Expert 2016

Commented:
can you upload your db?

Author

Commented:
I will need to take some stuff out
Top Expert 2016

Commented:
before you do, have you tried exporting using fixed width?

Author

Commented:
I am using the button on the form

Author

Commented:
Using Button on Main Form
db1.mdb

Author

Commented:
yes I am exporting with fixed width in the specs

Author

Commented:
I need export in fixed widths
Top Expert 2016
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.

Author

Commented:
I need to have it be able to open up in Excel in separate columns - it has to have the extention .csv

Author

Commented:
when I do the acExportFixed it goes into one field/column in Excel

Author

Commented:
Is this possible?
Top Expert 2016

Commented:
must it be a .csv file?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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

Author

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.
Top Expert 2016

Commented:
a work around

use this in the query

select IIf(Left([FieldI],1)="0",Chr(39) & [FieldI],[FieldI])
from tableX
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial