|
When exporting a data file from MS Access into a CSV file, the dollar amount loses the second decimal place. When clicking the mouse on the cell with the data, it shows .72 in the top address bar, but the cell itself only shows .7. What is the reason, and how do I correct it so that it shows the correct amount.
I suspect that there may need to be some formatting before it is exported, but I do not know how to format it correctly. I believe it should be formatted as "Format Cells" and "Number" with "2" decimal places, but how do I do this programmatically?
|
|
|
|
|
The second decimal place is not being lost.
You can check this by opening the CSV file in Notepad or some other text editor).
I assume you are opening the CSV in Excel so the fact that Excel displays it in the 'address bar' shows that.
You need to set the cell formatting in Excel to display 2 decimal places.
|
|
|
|
|
Correct. When I put the cursor in the cell, it shows the value as being stored in the cell as .72 but it only displays .7. However, when I manually format it using "Number" with "2" decimal places, it shows the full value.
If I open the CSV file and use the AutoSum feature, the aggregate total does not match the original data file. From this effect, I can conclude that it is not really being used in the calculation properly. To that end, I need to programmatically save the value as if I were manually sitting there in the middle of the code and applying the "Format > Cells" and "Number" with "2" decimal places. How can this be done?
Here is some code.
strAggregateGroupFileName1 = "qryAggregationMatching"
strAggregateGroupSQLString1 = "SELECT tblGroupGLCodeMapping.GL_CODE, " & _
"tblGroupGLCodeMapping.GL_CATEGORY, tblInputGroupFile.GL_AMOUNT " & _
"FROM tblGroupGLCodeMapping INNER JOIN tblInputGroupFile " & _
"ON tblGroupGLCodeMapping.GL_CODE = tblInputGroupFile.GL_CODE"
Set qdfLinkedTables = dbsBalanceSheet.CreateQueryDef(strAggregateGroupFileName1, _
strAggregateGroupSQLString1)
The code should probably go around: tblInputGroupFile.GL_AMOUNT
|
|
|
|
|
It can't. You are exporting data to a flat file, if you open your csv file in notepad you will see the data is actually correct. It is Excel that is applying the formating, and as you are not exporting to Excel you have no control over it.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Actually, in the data can be formatted in the query but it would need to be done explicitly with code like CovertDecimal(AMOUNT, 2). Is there code like this?
|
|
|
|
|
In actual fact you are not taking any notice of what people are telling you. THE DATA IN THE FILE WILL BE CORRECT IF YOU TAKE THE TROUBLE TO LOOK. The problem is the default format for the data in EXCEL.
Yes, you can format the data to decimal, look in help for how to do it, but it will make no difference.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Here is the code.
'Still in VBA in MS Access
strFileName = "qryAggregationGroupEntityMatching"
strSQLString = "SELECT tblBSMappingGroup.COUNTRY, " & _
"tblBSMappingGroup.ENTITY, tblBSMappingGroup.ACCOUNT_LABEL, " & _
"tblBSMappingGroup.GL_CATEGORY, qryAggregationRollingTotals.ACTUAL " & _
"FROM qryAggregationRollingTotals INNER JOIN tblBSMappingGroup ON " & _
"qryAggregationRollingTotals.GL_CATEGORY=tblBSMappingGroup.GL_CATEGORY"
Set qdfLinkedTables = dbsBalanceSheet.CreateQueryDef(strFileName , _
strSQLString)
'Exports to CSV
strOutputCSV1Path = strOutputFilePath + " - Grouping File.xls"
strMakeTableUpdateQuery = "SELECT qryAggregationGroupEntityMatching.ENTITY, " & _
"qryAggregationGroupEntityMatching.ACCOUNT_LABEL, qryAggregationGroupEntityMatching.ACTUAL " & _
"INTO tblExportCSV1 FROM qryAggregationGroupEntityMatching"
DoCmd.SetWarnings False
DoCmd.RunSQL (strMakeTableUpdateQuery)
strReplacementOutputString = "csv"
strNewOutputString = Replace(strOutputCSV1Path, _
strRemovedOutputString, strReplacementOutputString)
DoCmd.TransferText acExportDelim, "Standard Output", _
"tblExportCSV1", strNewOutputString, False
|
|
|
|
|
OK, one last time. A CSV file is NOT an Excel file, it does NOT contain formatting information and there is no way under the sun you are gong to make Excel put the fields in any format except its default one. The ONLY reason a csv file open in Excel is because of the file associations on your computer.
If you want, you could make Word the default association for a csv file - would you expect it to input and format the text then?
It does not matter what you do with decimal places in the csv file, Excel will format the column with its default settings.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
The question then is, how do I set it explicitly in Excel using code to decimal of 2 digits? I believe that it is something like:
Convert.Decimal(tblInputGroupFile.GL_AMOUNT, 2)
I know that this is not correct, but could you give me some idea as to how to convert it using an auxiliary function?
|
|
|
|
|
Please see the answers you have been given. YOU ARE NOT SETTING ANYTHING IN EXCEL, YOU ARE WRITING A FLAT FILE. For the last time, the problem is the default format for Excel is not 2 decimal places, and there is NO way you can set it from a csv file as it is NOT Excel
Bob
Ashfield Consultants Ltd
|
|
|
|
|
The Selection.NumberFormat function will do it in Excel.
E.g. Range("A1:A5").Select
Selection.NumberFormat = "0.00"
will format cells A1 to A5. To format all of column A use Columns("A:A").Select instead of Range.
This has to be a macro or VBA subroutine in Excel. If you use a subroutine you could trigger it with the the WorkBook_Open event.
|
|
|
|
|
|
I think query have to be something like this:
select table1.*,table2.*,table3.*<br />
from table1,table2,table3<br />
where table2.id = table1.id and table2.id = table3.id
Hope it will help you.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
|
You can do it like this:
select t1.*, t2.*, t3.*
from table1 t1
inner join table2 t2 on t2.table1_id = t1.id
inner join table3 t3 on t3.table2_id = t2.id
where t1.field1 = <something>...</something>
These are just standard joins.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Do not delete messages after they are answered!
The idea is that people can find answers from these forums based on keywords in both questions and answers.
|
|
|
|
|
Mika Wendelius wrote: Do not delete messages after they are answered!
The idea is that people can find answers from these forums based on keywords in both questions and answers
This why, if possible, I'll highlight their inquiry so it shows up in the {so-and-so} wrote:. If it is in the post of the first person who answers, then the question pretty much stays there unless Chris gets a hamster to zap the entire thread.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Good point. Personally I must learn to use that technique more often (started immediately to quote on a new thread  )
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Like Mika said, don't delete your message even if it has been answered. Others may want to chime in their thoughts/ideas later on, or learn from a question you have that was answered.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
hi,
I have created C++ application which create DSN with ODBC Microsoft text driver to use .csv file
as Database .
I want to change data type of some columns.So I tried
cmdstr = "ALTER TABLE table1 ALTER COLUMN RATE char(20)";
SQLExecDirect(hstmt, cmdstr, SQL_NTS);
But its give error as "syntax error in Alter Statement".
When we create DSN using ODBC wizard schema.ini file generate which contains the schema
information.But When I generate DSN by programmatically Schema.ini file not generate.
Pointer in this direction will be helpful for me.
Sunil
|
|
|
|
|
|
try using:
ALTER TABLE table1 modify COLUMN RATE char(20)
|
|
|
|
|
I wan to search sql server table to find any word contains some all the letter what should i do.
I'm using Asp.net vb to create web page. I create a text box I want to search for special letters.
I'm using sqldatasource to connect to sql server with the following procedure:
SELECT Name, Emai FROM Item WHERE (itemName = @itemName)
What should i do instead of (itemName = @itemName)
Thanks
|
|
|
|
|
If it is letters in a series (rather than any word containing any of these letters) you can use
WHERE ItemName like '%xyz%'
|
|
|
|
|
As in previous post use LIKE for simple pattern matching or if you need more logic for the search, use CONTAINS[^] predicate.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
