StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POJoining two CSVs using SQL/ADO in VBA - Invalid Data
text
Body
copied!<p>I'm at a loss here. I'm trying to join two CSV tables using SQL via ADO in VBA. The function is to match FX rates against specific dates and currency types. I'm using a string made of the date and currency code as a KEY to try to match these, as joining on matching dates and currency codes didn't work.</p> <p>Instead of giving the proper FX rate, the code below instead returns 1 or 0.</p> <pre><code>adoRS.Open "SELECT tmpFile.[KEY], fxFile.[KEY], tmpFile.[TRADE_DATE]," & _ "tmpFile.[CURR_CODE]," & _ "tmpFile.[VOLUME]," & _ "tmpFile.[TRADE_PRICE]," & _ "tmpFile.[CAD_VAL]," & _ "fxFile.[FX_PRICE]" & _ " FROM tempFile.csv tmpFile, " & _ " 2011FXRates.csv fxFile" & _ " WHERE tmpFile.[KEY]=fxFile.[KEY]", _ adoCxn, _ adOpenForwardOnly, _ adLockReadOnly, _ adCmdText </code></pre> <p>Oddly, the KEYs match up fine. Why doesn't the FX_PRICE come in as I would expect? Any ideas?</p> <p><b>Sample Data</b></p> <p>From tempFile.csv</p> <pre><code> KEY TRADE_DATE CURR_CODE VOLUME TRADE_PRICE CAD_VAL 40554-000 40554 0 600 1.5 900 40556-000 40556 0 800 0.75 600 40556-000 40556 0 1500 0.25 375 40556-000 40556 0 800 2.8 2240 40574-000 40574 0 300 1.3 390 </code></pre> <p>From 2011FXRates.csv</p> <pre><code> KEY TRADE_DATE CURR_CODE FX_PRICE 40554-000 40554 0 1.2605 40555-000 40555 0 1.1609 40556-000 40556 0 1.1494 40557-000 40557 0 1.1362 40560-000 40560 0 1.128 </code></pre> <p><b>UPDATE</b> I just spotted the problem - it's only taking the first digit on the FX_PRICE, so 1.xxx becomes 1, 0.xxx becomes 0. How do I fix this?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload