View previous topic :: View next topic |
Author |
Message |
ognik
Joined: 04 Feb 2015 Posts: 19
|
Posted: Mon Nov 09, 2015 8:42 pm Post subject: 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 |
|
Back to top |
|
|
wahorger
Joined: 13 Oct 2014 Posts: 1217 Location: Morrison, CO, USA
|
Posted: Wed Nov 11, 2015 9:27 pm Post subject: |
|
|
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. |
|
Back to top |
|
|
JohnCampbell
Joined: 16 Feb 2006 Posts: 2554 Location: Sydney
|
Posted: Wed Nov 11, 2015 11:41 pm Post subject: |
|
|
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 |
|
Back to top |
|
|
wahorger
Joined: 13 Oct 2014 Posts: 1217 Location: Morrison, CO, USA
|
Posted: Thu Nov 12, 2015 12:08 am Post subject: |
|
|
John,
I wondered: is it the displayed precision, or the internal precision?
I did a quick experiment. the CSV file is:
Code: |
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! |
|
Back to top |
|
|
mecej4
Joined: 31 Oct 2006 Posts: 1886
|
|
Back to top |
|
|
wahorger
Joined: 13 Oct 2014 Posts: 1217 Location: Morrison, CO, USA
|
Posted: Thu Nov 12, 2015 1:26 am Post subject: |
|
|
Yep, there it is! |
|
Back to top |
|
|
IanLambley
Joined: 17 Dec 2006 Posts: 490 Location: Sunderland
|
Posted: Thu Nov 12, 2015 1:31 pm Post subject: |
|
|
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 |
|
Back to top |
|
|
ognik
Joined: 04 Feb 2015 Posts: 19
|
Posted: Sat Nov 14, 2015 12:22 am Post subject: Annoying |
|
|
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 ) |
|
Back to top |
|
|
JohnCampbell
Joined: 16 Feb 2006 Posts: 2554 Location: Sydney
|
Posted: Sat Nov 14, 2015 9:05 am Post subject: |
|
|
Quote: | 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 Code: | subroutine write_real_to_csv ( value, csv_line )
!
! writes real number to line
! removes trailing zero values
!
real*8 value
character csv_line*(*)
!
character string*30
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 ? |
|
Back to top |
|
|
IanLambley
Joined: 17 Dec 2006 Posts: 490 Location: Sunderland
|
Posted: Mon Nov 16, 2015 1:28 pm Post subject: |
|
|
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 |
|
Back to top |
|
|
JohnCampbell
Joined: 16 Feb 2006 Posts: 2554 Location: Sydney
|
Posted: Mon Nov 16, 2015 10:52 pm Post subject: |
|
|
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 |
|
Back to top |
|
|
|