• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2962
  • Last Modified:

Export data from Access to Excel in csv format

Hi Guys:

I have a access table that I am trying to export to csv, the problem is some of the fields have more than 18 characters in them and when the data transfers to csv it shows scientific numbers, How do I get this to work to take the values as is from the access table instead of switching to scintific number.I tried changing the field proprties to text or number but had the same problem.

THanks
0
proudpaki9
Asked:
proudpaki9
1 Solution
 
Rey Obrero (Capricorn1)Commented:
how are you exporting the file to csv?
did you create and export specification?


Creating an Import/Export Specification
http://support.microsoft.com/?kbid=208991

0
 
proudpaki9Author Commented:
This is what I am using in the button:

Option Compare Database

Private Sub Command0_Click()


DoCmd.TransferText acExportDelim, "", "table1", "C:\Table1_Export.csv", True
End Sub
0
 
Rey Obrero (Capricorn1)Commented:

---------------------------------------------vv export specification here
DoCmd.TransferText acExportDelim, "", "table1", "C:\Table1_Export.csv", True
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
proudpaki9Author Commented:
Sorry I dont understand.
0
 
Rey Obrero (Capricorn1)Commented:
go to the link i posted above and follow the instruction how to create an Export specification. Then use the name of export specification the you created in the the command line you are using

DoCmd.TransferText acExportDelim, "Nameof Export Specification you created", "table1", "C:\Table1_Export.csv", True
0
 
jerryb30Commented:
In order to get the full numbers without scientific notation, you may need to cast the numeric value as a text string.
So, instead of exporting your table, your will export the query
example of changing numeric to text
select cstr(yournumberfield) as newnumberfield from your table.

0
 
proudpaki9Author Commented:
I tried still getting the scientific numbers.
Thanks
0
 
Rey Obrero (Capricorn1)Commented:
what did you try?
0
 
proudpaki9Author Commented:
Both yours and jerryb30 suggestions.

FOr your I created template and then export to that template. Still got the same results.

Thanks
0
 
Rey Obrero (Capricorn1)Commented:
<FOr your I created template and then export to that template>

i did not suggest that you  create a template..

did you follow the instruction from the link i posted?
0
 
proudpaki9Author Commented:
I am sorry I meant to say I used the export method described in the article however the file was created as txt but I need to have .csv extionsion. When I changed the extension to .csv from txt same problem with the data scientific notation.

Thanks
0
 
Jeffrey CoachmanMIS LiasonCommented:
:proudpaki9,

Here is one fix I found, from here:
http://usenet.p2preactor.com/index-t-1374867.html


Ken Snell [MVP]06-26-2005, 07:58 PM
Export a query that is based on that table (don't export the table
directly). In place of the field that is to have 2 decimal places, use a
calculated field that formats the number the way you wish:
MyNum: Format([FieldName], "0.00")


HTH

Jeff Coachman
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

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now