Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Export table to a csv file without decimal places, dollar signs and apostrophes
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.
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.
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
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
SELECT [System ID], [Order Qty], cdbl([Unit Cost]) as UnitCost
FROM your table.
Then use this query in your transfertext method.
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?
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?
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 .
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 .
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.
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.
Just use one of the many file based techiques to just create a .csv file and populate it line by line
Here is an article that uses the fso object, creates the ".csv" and then populates it...you just need to iterate your records , concatenate the fields you want with commas to a single string and use the WriteLine to write it.
As for strange characters just use Replace to replace them with the equivelant Chr
Here is an article that uses the fso object, creates the ".csv" and then populates it...you just need to iterate your records , concatenate the fields you want with commas to a single string and use the WriteLine to write it.
As for strange characters just use Replace to replace them with the equivelant Chr
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;
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.
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
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 ?
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 ?
I am using Microsoft Access. I actually just made the following changes
Old --- Set Fileout = fso.CreateTextFile(strTheD
New --- Set Fileout = fso.CreateTextFile(strTheD
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?
Old --- Set Fileout = fso.CreateTextFile(strTheD
New --- Set Fileout = fso.CreateTextFile(strTheD
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?
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial