@Lenche08, where are you geographically? Is your Excel using commas as decimal separators and dots as the thousands separator? From your screenshots in msg#36 it looks like it is.
My solution recognises that the values in the csv file uses commas as the decimal separator (because I told it to which is because I recognised that they were (comparing it with the Excel file you attached to your first message here)), but my solution should work in Excel set up in any
locale.
edit: I see you've just posted another message and you mentioned that some csv files have only 2 intervals, if the intervals don't start with 1 my query will fail (I was being lazy - work in progress and all that) I'll have a look at that and supply an update later anyway.
@vletm,
When you open a csv file in an Excel set up in a
locale which uses a full stop (a period/a dot) as the decimal separator, Excel sees a string such as "22,219" and considers it likely to be a number since the comma is in the right place for being a thousands separator, so ever helpful as Excel tries to be, it converts that value into a number 22219, and while being helpful it also changes the formatting of that cell to show the comma thousands separator.
When it sees a string such as "22,23" it see the comma in the 'wrong' place so keeps the value as a string "22,23".
When you look at the
.Value of 22219 it sees no comma so no replacing of the comma with a full stop takes place, but when it looks at the
.Value of the string "22,23" it does see a comma so it gets replaced.
If instead of using the
.Value property of the cell, you use the
.Text property, it sees the comma in both (thanks to Excel's change in the formatting) and does the replacement in both.
How this will behave in Excel set up with a comma as the decimal separator I can only guess. That guess would be that such an Excel would see the commas as the decimal separator and correctly interpret the text in the csv as numbers, and do so consistently, so perhaps no comma replacement would be necessary?
It all stems from Excel being helpful and trying (and failing properly) to interpret csv files when they're opened.
One way round it is to avoid opening the csv files onto an Excel sheet, perhaps reading the file directly into memory (there are several ways) and parsing the intact string data from that before writing the data you want onto a sheet. Quite hard work, but it would be quick.
