Set csv cell properties

Hi - I am writing a table of text lines and numeric results to a csv file - very simple open/write approach. What I'd like to do is set the column width / cell size from within the program, so that when it is opened the user doesn't have to do it. Any ideas? Thanks

I do something similar. There is no way to set the column width upon opening the CSV file in Excel.

Excel is pretty dumb that way.

I wouldn't say 'no way' but I don't know either. There probably is some coding, but I have never seen a .csv file that does change from 8.43 character columns.

I use a few subroutines to generate .csv flies. I struggle with a general routine to add a real number to a csv line character buffer. Any suggestions for a solution to ' subroutine add_real_to_csv_line ( value ) ' that retains the precision ? I've experimented with removing trailing 0 from a (', ', F0.12) format for numbers whose power is -3:8, else use (', ',ES15.8) format.

John

John,

I wondered: is it the displayed precision, or the internal precision?

I did a quick experiment. the CSV file is:

12.34567890123456789,-1.23543678901234567

The first cell is shown as 12.3456789012345 (dropped the last 6789). The second is: -1.23543678901234 dropping the 567. It would appear Excel has a problem with the total number of digits. It would appear that 15 is it.

So Excel is truncating the number after it is ingested. If this is the case, then you are likely sunk.

Don't get me started with text fields; that's another wild ride!

Numbers stored as 8-byte IEEE reals have 53-bit precision, which corresponds to 15 to 16 decimal digits. See https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel .

Yep, there it is!

I had to format a series of csv files into a standard Excel arrangement and used VBA program to transfer them into a formatted spreadsheet. The formatting function was automatically started when you open the .xls file and looked for a file containing a list of files to process. It then opened a series of csv files and stored then in output excel files. You just needed the empty template Excel files to exist and it blasted through the procedure. The csv files were created from FTN95 and the Excel processing initiated by a use_url@ command.

Ian

Annoying! To test, I manually formatted the csv, saved as csv - then opened in xcel, formatting gone! So xcel deliberately overrides the user - typical of some company I won't name (like harry and you know who ) :evil:

So xcel deliberately overrides the user

I don't think that is the case, as .csv file is a text file with no column width syntax being stored; or at least that is what I am assuming.

Anyway, for my previous question: I created a csv write routine for real that I think retains precision, and shrinks the text and came up with subroutine write_real_to_csv ( value, csv_line ) ! ! writes real number to line ! removes trailing zero values ! real8 value character csv_line() ! character string30 integer n ! write ( string,* ) value call left_adjust (string) call right_adjust_real (string) n = len_trim (csv_line) + 1 if ( n > 1 ) then csv_line(n:) = ', ' // string else csv_line = string end if ! end subroutine write_real_to_csv

   subroutine left_adjust (string)
!
!  removes leading blanks from string
!   
     character string*(*), new*30
     integer*4 i
     new = ' '
     do i = 1, len_trim (string)
       if ( string(i:i) == ' ') cycle
       new = string(i:)
       exit
     end do
     string = new
   end subroutine left_adjust

   subroutine right_adjust_real (string)
!
!  removes trailing zeros from real number
!
     character string*(*)
     integer*4 i,l
     l=2
     do i = 1, len_trim (string)
       if ( string(i:i) == '0') cycle
       if ( string(i:i) == ' ') cycle

       if ( string(i:i) == 'E' .or.   &
            string(i:i) == 'e') then
         if ( l < i ) string = string(1:l-1) // string(i:)
         return
       end if

       l = i+1
       if ( string(i:i) == '.') l = i+2
     end do
     string(l:) = ' '
   end subroutine right_adjust_real

Any suggestions ?

And if it is a text item that may contain the comma separator, then it has to be enclosed in double quotation marks. And if it contains a double quotation mark, then it has to be a double-double quotation mark.

And if it is in Europe where the comma separator is actually a semi-colon and the decimal place is also a comma then these has to be translated too.

And if you use comma thousand separators, then in Europe, they have to be decimal places.

Good luck Ian

Ian,

I write out 'a long string' for strings, exclude ' in strings and ignore all the others ! I use .csv files as a general format for exporting to other programs and importing into Excel. It does provide some expected layout; all you need to know is the variable order. Each record has a record type ID. I have been trying to compare results between different versions of programs that are running on different compilers and need to determine the magnitude of error.

John