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
PdetersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.