Exporting to CSV with Query does not provide the output I expect (1 Viewer)

[Lateral]

Hi Guys

I have spent many hours today trying to sort this issue out but I'm throwing my hands up for some help.

I have a table called "Parts" and a query called "qExportPartsToCSV".

I have attached a sample database with these in it.

The Parts table contains records relating to various cars parts, their Unit Price and other bits of information.

I want to export selected records from the parts table to a CSV file that will be used to update a website with the new Unit Price.

The following is the correct format of the CSV file that I need:

Web_Product_Id,UnitPriceNew,GST
1382,2617.00,GST10
1711,143.00,GST10
2018,2087.00,GST10

The following is incorrect as it keep exporting the UnitPriceNew value with dollar signs and is what I keep getting:

Web_Product_Id,UnitPriceNew,GST
1382,$2617.00,GST10
1711,$143.00,GST10
2018,$2087.00,GST10


I've tried setting up a dedicated specifications and just can't get it to work properly.


I have a button on a form that triggers the following:

DoCmd.TransferText acExportDelim, "QExportPartsToCSVExportSpecification", "qExportPartsToCSV", "c:\RPR Access\web shop\partsexport.csv", True


So, in summary, all I want to do is to export the UnitPriceNew without any dollar ($) signs.

Help!

Cheers
Greg

 

[Micron]

Your table field is Currency type. You've formatted it to have a dollar sign.
I'm confused a bit - what else would you expect? Perhaps you're trying to do something in the form to eliminate that, but there's no form in your db.

EDIT - You could try formatting the query field to Standard.

 

[strive4peace]

hi Greg ,

So, in summary, all I want to do is to export the UnitPriceNew without any dollar ($) signs.

as Micron said, you would need to remove dollar signs if you don't want them ... unfortunately, however, currency is an accurate data type with decimal places whereas Double Precision, what you could convert to instead, isn't. Sadly, setting the format property for the column doesn't affect writing to external files. Perhaps wrapping with the ROUND funtion might get what you want?

 

[arnelgp]

bring your Query in design view.
click on that Column (unitPriceNew).
on it's Property Sheet -> Format, choose Fixed.

 

[strive4peace]

VBA Help for Round:

Round function

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/round-function

settings on the Property Sheet don't impact exported data -- equation needs to be in the expression for the field

 

[Micron]

If one is going to round, then why worry about the lack of exactness with floating point numbers? You're saying csv output will not comply with an applied format? Interesting because arnelgp is also suggesting format the query field.

 

[Lateral]

Hi Micron

I was hoping to not have to change the data format of the field from Currency to Number.

This entire process is doing my head in a bit.

I have tried multiple times changing the field type to Number, Double fixed and I get differing results.

Sometimes I will still get the UnitPriceNew with the dollar signs and sometimes I am mussing the cents.

I think it is somehow related to the saving of the Specification

In order to create or modify the Specification, I first right click the query (qExportPartsToCSV) and then go through the process untill the very end when I then save the Specification to "QExportPartsToCSVExportSpecification ".

I think it depends upon when I actually "save" the Specification during the Export process.

Anyway, I seem to have it working now but I have one last question.

If I want to keep the [UnitPriceNew] field as Currency and want to export it without the dollar sign via a query, is there a way to do it?

Thanks for you help with this.

Cheers
Greg

 

[Lateral]

VBA Help for Round:

Round function

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/round-function

settings on the Property Sheet don't impact exported data -- equation needs to be in the expression for the field

Hi

Can you please explain this for me?

Cheers
Greg

 

[strive4peace]

You're saying csv output will not comply with an applied format?

Micron, yes. The format PROPERTY affects how data looks, but not what it IS. What is exported will be what it is ... and single and double precision data types are known for creating random digits with no significance

Currency data type is nice since it has decimal places with accuracy ... but it also displays the currency symbol, which is not always desired. Whilst in Access, the format property can alter the way that currency values are displayed, and still keep its accuracy and reliability. When exporting however, the property settings are ignored. .

If things have changed, I'd be happy to be wrong! I've been taking this into consideration for a long time.

Can you please explain this for me?
Cheers
Greg

Greg, you can change the underlying data that is exported by using functions to get it the way you want. One of these ways would be to use the Round function to get a number with fixed decimal places (I'm assuming it will also drop the currency symbol!)

 

[The_Doc_Man]

The other option is to use the Replace(field, "$","") AS some-other-name in the query and not worry about the fact that it would create the dollar signs. Just have to come up with a compatible but not identical name. Maybe Replace( UnitPriceNew, "$","") AS UnitPrice_New would do the trick?

 

[Micron]

The format PROPERTY affects how data looks, but not what it IS.

I know that. Here I'm talking about a calculated field - I thought I posted that along with a calculation suggestion. I must have removed it after seeing arnelgp's post.

I was hoping to not have to change the data format of the field from Currency to Number.

If you mean the table field, I didn't say that. I did say remove the custom format from that field, but I tried it and it made no difference to the data in your db. However, formatting the query field to standard or fixed did remove the dollar signs. If it's true that altering the query field data type to fixed won't work either (not my assertion because I don't know) in the transport maybe a calculation would be in order, or a function as was also mentioned. I have tried Cdbl and CStr conversion functions on the query field but the odd thing is, if it ends in 00 it doesn't seem to matter what I do to the field properties. If it ends in .01 no problem.

I have to run out now so will have to review this later. I might be inclined to try a function call in the query whereby the currency value is converted to a string without the dollar sign if nothing else works.

 

[bastanu]

@ Lateral - you can find and edit the import/export specifications in these two system tables:
MSysIMEXSpecs (holds the specification info)
MSysIMEXColumns (holds the specific column detail, linked by foreign key SpecID to the first one)

Cheers,
Vlad

 

[Gasman]

@ Lateral - you can find and edit the import/export specifications in these two system tables:
MSysIMEXSpecs (holds the specification info)
MSysIMEXColumns (holds the specific column detail, linked by foreign key SpecID to the first one)

Cheers,
Vlad

isladogs wrote a utility a little while back to do this

https://www.access-programmers.co.uk/forums/threads/import-export-data-tasks.307878/#post-1650617

https://www.access-programmers.co.uk/forums/threads/view-edit-imex-data-task-specifications.307897/

HTH