[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Export data from Access to Excel in csv format

Posted on 2007-10-19
12
Medium Priority
?
2,956 Views
Last Modified: 2008-01-09
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
Comment
Question by:proudpaki9
12 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20110238
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
 

Author Comment

by:proudpaki9
ID: 20110471
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20110495

---------------------------------------------vv export specification here
DoCmd.TransferText acExportDelim, "", "table1", "C:\Table1_Export.csv", True
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:proudpaki9
ID: 20110517
Sorry I dont understand.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20110622
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 20110732
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
 

Author Comment

by:proudpaki9
ID: 20111822
I tried still getting the scientific numbers.
Thanks
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20111846
what did you try?
0
 

Author Comment

by:proudpaki9
ID: 20111856
Both yours and jerryb30 suggestions.

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

Thanks
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20112281
<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
 

Author Comment

by:proudpaki9
ID: 20112341
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 20248731
: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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question