Pdeters
asked on
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
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
Rey Obrero (Capricorn1)
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.
ASKER
not all fields have a value - having a problem with the null fields exporting
ASKER
I receive an error
use the nz() function
nz([fldName],"") '< for text data
nz([fldname],0) ' < for number
to use the cstr()
cstr(nz([fldName],""))
nz([fldName],"") '< for text data
nz([fldname],0) ' < for number
to use the cstr()
cstr(nz([fldName],""))
ASKER
Still not getting the leading 00 on the text fields - it is dropping them
can you upload your db?
ASKER
I will need to take some stuff out
before you do, have you tried exporting using fixed width?
ASKER
I am using the button on the form
ASKER
Using Button on Main Form
db1.mdb
db1.mdb
ASKER
yes I am exporting with fixed width in the specs
ASKER
I need export in fixed widths
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I need to have it be able to open up in Excel in separate columns - it has to have the extention .csv
ASKER
when I do the acExportFixed it goes into one field/column in Excel
ASKER
Is this possible?
must it be a .csv file?
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
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
ASKER
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.
the leading 0 need to be there in Excel but the file extension needs to be csv.
a work around
use this in the query
select IIf(Left([FieldI],1)="0",C
from tableX
use this in the query
select IIf(Left([FieldI],1)="0",C
from tableX
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.