forums.silverfrost.com Forum Index forums.silverfrost.com
Welcome to the Silverfrost forums
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Set csv cell properties

 
Post new topic   Reply to topic    forums.silverfrost.com Forum Index -> General
View previous topic :: View next topic  
Author Message
ognik



Joined: 04 Feb 2015
Posts: 19

PostPosted: Mon Nov 09, 2015 8:42 pm    Post subject: Set csv cell properties Reply with quote

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
View user's profile Send private message
wahorger



Joined: 13 Oct 2014
Posts: 1217
Location: Morrison, CO, USA

PostPosted: Wed Nov 11, 2015 9:27 pm    Post subject: Reply with quote

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
View user's profile Send private message Visit poster's website
JohnCampbell



Joined: 16 Feb 2006
Posts: 2554
Location: Sydney

PostPosted: Wed Nov 11, 2015 11:41 pm    Post subject: Reply with quote

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
View user's profile Send private message
wahorger



Joined: 13 Oct 2014
Posts: 1217
Location: Morrison, CO, USA

PostPosted: Thu Nov 12, 2015 12:08 am    Post subject: Reply with quote

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
View user's profile Send private message Visit poster's website
mecej4



Joined: 31 Oct 2006
Posts: 1885

PostPosted: Thu Nov 12, 2015 1:25 am    Post subject: Reply with quote

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 .
Back to top
View user's profile Send private message
wahorger



Joined: 13 Oct 2014
Posts: 1217
Location: Morrison, CO, USA

PostPosted: Thu Nov 12, 2015 1:26 am    Post subject: Reply with quote

Yep, there it is!
Back to top
View user's profile Send private message Visit poster's website
IanLambley



Joined: 17 Dec 2006
Posts: 490
Location: Sunderland

PostPosted: Thu Nov 12, 2015 1:31 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
ognik



Joined: 04 Feb 2015
Posts: 19

PostPosted: Sat Nov 14, 2015 12:22 am    Post subject: Annoying Reply with quote

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 or Very Mad
Back to top
View user's profile Send private message
JohnCampbell



Joined: 16 Feb 2006
Posts: 2554
Location: Sydney

PostPosted: Sat Nov 14, 2015 9:05 am    Post subject: Reply with quote

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
View user's profile Send private message
IanLambley



Joined: 17 Dec 2006
Posts: 490
Location: Sunderland

PostPosted: Mon Nov 16, 2015 1:28 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
JohnCampbell



Joined: 16 Feb 2006
Posts: 2554
Location: Sydney

PostPosted: Mon Nov 16, 2015 10:52 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    forums.silverfrost.com Forum Index -> General All times are GMT + 1 Hour
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group