We help IT Professionals succeed at work.

Export table to a csv file without decimal places, dollar signs and apostrophes

peispud
peispud asked
on
77 Views
Last Modified: 2019-02-19
Hi.
I using Microsoft Access 365.  I need to export / create a clean csv file.
My table only has 3 fields.

[System ID]          Data type  =  Number         Field Size = Double             Decimal places = 0
[Order Qty]          Data Type = Number          Field Size = Integer             Decimal places  = 0
[Unit Cost] is        Data Type  = Currency        Format = General               decimal places = 2

This is my export command.
 DoCmd.TransferText acExportDelim, , str_tbl_ToExport, strTheDirectory, True, , 65001

Open in new window


I am trying to export a from a table to a CSV file.  Below is the result
 *** Please note that I inserted spaces in this  question to make it easier to read. ***
This is what I get.    

"System ID",                  "Unit Cost",  "Order Qty"
210000000347.00,         $4.20,                    1

See below for what I need.

System ID,              Order Qty,             Unit Cost
210000000347,           4.2,                      1

Any help would be greatly appreciated.
Comment
Watch Question

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
create a query that selects all of those records and uses explicit type conversion

SELECT [System ID], [Order Qty], cdbl([Unit Cost]) as UnitCost
FROM your table.

Then use this query in your transfertext method.
peispudTech

Author

Commented:
I am trying to understand your advice.
I have used the query wizard to on this table.   I am looking at this now.
But, I do not understand how "use explicit type conversion".

or is this a vba code solution that you suggest?
peispudTech

Author

Commented:
Thank you for your help.  I'm working on this but still stuck.

I believe that I've done that within the query wizard.  I created a new field in the query that does the explicit conversion. Then I exported just that field as a csv file.  It did not change anything .
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
If you have formatting defined on the table for those fields, that may get transferred when exporting to .csv.  Since I NEVER, EVER format numbers or dates at the table level, I have never had this problem.  It is not a problem that Access causes.  Either your query is adding the formatting or it is in the table itself.

The reason I never format at the table is because users never get to look directly at tables.  Only I do.  Users look at forms and reports where it does make sense to format these data types.  When you format at the table, you obfuscate the data and that only causes YOU problems with debugging.  For example, if you are having trouble selecting the correct records using a date range, the issue is likely that some rows include a time component and you can't see it because you formatted the field to show only the date.  Or you may be confused by decimals.  Internally, Access stores the actual value.  So if the result of a calculation is 245.788765, that is what gets stored but if you set the decimal places to 2, what you see when you look at the table is 245.79 and so you'll never understand why a column of numbers is a couple of cents off when you add it manually.
Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Use the old trick with Str and Val in an expression in a query, then export this query:

SELECT 
    CStr([System ID]), 
    Val(Str([Unit Cost])) AS UnitCost,
    CStr([Order Qty])
FROM 
    YourTable;

Open in new window

peispudTech

Author

Commented:
Hi
I went with  John Tsioumpris's suggestion.  The code below works perfectly, except that Excel interperts it as a text file.
When I examine the contents in notepad and compare to a valid csv file, there are no differences.
So, I am still missing something.


Dim fso As Object:      Set fso = CreateObject("Scripting.FileSystemObject")
     Dim Fileout As Object:      Set Fileout = fso.CreateTextFile(strTheDirectory, True, True)
     Fileout.Write "Order Qty,Unit Cost,System" & vbCrLf
     Do Until rsx.EOF
          Fileout.Write rsx![Order Qty] & Chr(44) & rsx![Unit cost] & Chr(44) & rsx![System ID] & vbCrLf
          rsx.MoveNext
     Loop
     Fileout.Close

Open in new window

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Excel sometimes tries to "guess" what the contents of a file is ...maybe this is the issue...
Maybe if you upload a small sample we can help better.
Also ...even Excel doesn't recognizes it correctly can you still import it by using the Text To Columns and using comma as separator ?
peispudTech

Author

Commented:
I am using Microsoft Access.  I actually just made the following changes

Old ---          Set Fileout = fso.CreateTextFile(strTheDirectory, True, True)
New ---        Set Fileout = fso.CreateTextFile(strTheDirectory, True, False)

Now, everything looks like it should in Excel.

So, it looks like I can create / have created  a valid .csv file for import into another web application.

It now looks and acts like a csv file.  Any other considerations?
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
So the problem was the Unicode/ASCII setting...interesting...
Anyway...as usual i think you just have to test it for some cases and if everything goes as it should the work is done.
peispudTech

Author

Commented:
Thanks everyone who helped in this. I see the same names often. I know that the talent is great!
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Did you ever check the table definition to determine if that was causing the problem?
peispudTech

Author

Commented:
Yes I did.
There were only 3 fields.  
I changed the formatting to general when possible.  It did not work for me.

I know that your advise was correct.  But I missed or misunderstood something.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
There should be NO formatting on the table.  "General" is an Excel format, not an Access format so I'm not sure you looked in the correct place.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.