Recent

Author Topic: Creating a CSV File  (Read 14595 times)

J-G

  • Hero Member
  • *****
  • Posts: 953
Creating a CSV File
« on: September 27, 2016, 08:55:07 pm »
I've spent the day going around in circles so I'm looking for sanity here  >:D

I need to write a .CSV file which can be simply opened in Excel. I've solved all the issues of creating and closing files (I did have to search the forum for the issue with 'assign' !!) but what I now get is mostly the correct data in the file but with extraneous characters appended to each 'cell'.

Even the 'header' line gets corrupted :-
Code: Pascal  [Select][+][-]
  1. Procedure write_Header;
  2. begin
  3.   write(data_File,'JD,','Sun L,','Moon L,','Diff,','Node Ang,','Moon Age,',);
  4.   write(data_file,#13);
  5. end;
  6.  

produces
- JD,€\#€\# - - Sun L,€\# - - Moon L,€\# - - Diff,L,€\# -     in cell A1   
I've had to replace some characters with ' - ' so that the preview here could cope!

Cell B1 has   Node Ang,# -    and C1 has   Moon Age,#   -  these ought to be in E1 and F1

The main data writing procedure is :-

Code: Pascal  [Select][+][-]
  1. procedure Write_Rec;
  2. Var s : string;
  3.   procedure save_data;
  4.   begin
  5.     Write(Data_File,S);
  6.   end;
  7. begin
  8.  
  9.   Str(Julian_Day:16:4,S);   S:=S+','; save_data;
  10.   Str(Lamda_S:8:4,S);       S:=S+','; save_data;
  11.   Str(Lamda_M:8:4,S);       S:=S+','; save_data;
  12.   Str(Ecliptic_Diff:8:4,S);    S:=S+','; save_data;
  13.   Str(Nodes_Aligned:8:4,S); S:=S+','; save_data;
  14.   Str(Moon_Age:8:4,S);      S:=S+','; save_data;
  15.  
  16.   write(Data_File,#13);
  17.  
  18. end;
  19.  

and the data in column A always starts with   ðØ -  and the correct data. All the other columns have the correct data plus the comma and another character such as   œ, Ä, ¤, ü  etc.

I've tried putting the comma in at different parts of the code --- I've even tried #44  ---  without success, so I'd be grateful for advice as to where my string handling is defective.

---  I'm aware that the 'save_data' procedure doesn't save any coding, it's a hang-over from a previous attempt which had extra work to do.


FPC 3.0.0 - Lazarus 1.6 &
FPC 3.2.2  - Lazarus 2.2.0 
Win 7 Ult 64

wp

  • Hero Member
  • *****
  • Posts: 11855
Re: Creating a CSV File
« Reply #1 on: September 27, 2016, 09:36:33 pm »
No idea what you are doing with the header, it looks correct except that I would write one single string and use WriteLn instead of write(...) and write(#13). (BTW - maybe Excel does not like the #13 as a line ending of a text line, because the line ending on Windows contains an additional #10).

Code: Pascal  [Select][+][-]
  1.     Procedure write_Header;
  2.     begin
  3.       writeLn(data_File, 'JD,Sun L,Moon L,Diff,Node Ang,Moon Age');
  4.     end;

The "Write_rec" is big nonsense (sorry for this word...). You use one string variable into which you write the converted numbers, and then you add a comma to it. In the next line you overwrite this variable with a new number value - i.e. you constantly erase in one line what you achieve in the previous line.

Use a second string for the total result and always add the previously converted number string to the current total string and use a comma as separator. But you must be aware that the first character of the total string is a comma because the total string was empty before you added the first comma and number string. You must delete this comma before writing to disk:
Code: Pascal  [Select][+][-]
  1. procedure Write_Rec;
  2. Var s, total : string;
  3. begin
  4.   total := '';
  5.   Str(Julian_Day:16:4,S);      total :=total+','+s;
  6.   Str(Lamda_S:8:4,S);          total:= total+','+s;
  7.   Str(Lamda_M:8:4,S);          total:=total+','+s;
  8.   Str(Ecliptic_Diff:8:4,S);    total:=total+','+s;
  9.   Str(Nodes_Aligned:8:4,S);    total:=total+','+s;
  10.   Str(Moon_Age:8:4,S);         total:=total+','+s;
  11.   Delete(total, 1, 1);  // "Delete at 1st string position 1 character" --> delete the first comma
  12.   WriteLn(data_file, total);
  13. end;

An alternative: Instead of adding all number strings to a total string you can stuff them individually into the WriteLn procedure:
Code: Pascal  [Select][+][-]
  1. procedure Write_Rec;
  2. begin
  3.   WriteLn(Julian_Day:16:4, ',', Lamda_S:8:4, ',', Lamda_M:8:4, ',', Ecliptic_Diff:8:4, ',', Nodes_Aligned:8:4, ',', Moon_Age:8:4);
  4.  
  5.   { or for better clarity add line breaks to the code
  6.   WriteLn(
  7.     Julian_Day:16:4, ',',
  8.     Lamda_S:8:4, ',',
  9.     Lamda_M:8:4, ',',
  10.     Ecliptic_Diff:8:4, ',',
  11.     Nodes_Aligned:8:4, ',',
  12.     Moon_Age:8:4
  13.   ); }
  14. end;





lainz

  • Hero Member
  • *****
  • Posts: 4460
    • https://lainz.github.io/
Re: Creating a CSV File
« Reply #2 on: September 27, 2016, 09:45:19 pm »
Maybe you want to try TStringList.

CSV in Excel is separated by ";". In LibreOffice you can choose what is the separator.

Code: Pascal  [Select][+][-]
  1. program project1;
  2.  
  3. uses
  4.   Classes;
  5.  
  6. var
  7.   s: TStringList;
  8. begin
  9.   s := TStringList.Create;
  10.   // I think each one go on a cell; is this right?
  11.   s.Add('JD; Sun L; Moon L; Diff; Node Ang; Moon Age');
  12.   s.Add('col1; col2; col3; col4; col5; col6'); // A row
  13.   s.SaveToFile('yourfile.csv');
  14.   s.Free;
  15. end.            

wp

  • Hero Member
  • *****
  • Posts: 11855
Re: Creating a CSV File
« Reply #3 on: September 27, 2016, 09:58:23 pm »
CSV in Excel is separated by ";". In LibreOffice you can choose what is the separator
In Excel too. The best separator, BTW, is a tab, it has a very low collision probability with characters inside the cells. And the best file extension is .txt. Excel opens such files directly without starting the import wizard.

lainz

  • Hero Member
  • *****
  • Posts: 4460
    • https://lainz.github.io/
Re: Creating a CSV File
« Reply #4 on: September 27, 2016, 10:10:35 pm »
CSV in Excel is separated by ";". In LibreOffice you can choose what is the separator
In Excel too. The best separator, BTW, is a tab, it has a very low collision probability with characters inside the cells. And the best file extension is .txt. Excel opens such files directly without starting the import wizard.

I have old excel, Office 2007 maybe for that. It opens only the CSV separated by ";", else everything in the line is inside the same cell (the first cell of the row). But as you say, if I save it as ".txt" it brings me the option to choose the format. Nice to know!

wp

  • Hero Member
  • *****
  • Posts: 11855
Re: Creating a CSV File
« Reply #5 on: September 27, 2016, 10:32:18 pm »
I have Excel2007, too. There is a "Text conversion assistant" (or similar) which allows to set up everything: the column separator, the decimal separator, the date format etc. The only problem is that it does not appear when I need it, and it does  when I do not want it - I did not investigate how to set this up.

lainz

  • Hero Member
  • *****
  • Posts: 4460
    • https://lainz.github.io/
Re: Creating a CSV File
« Reply #6 on: September 27, 2016, 11:56:00 pm »
When I open a txt it says "Text Import Wizard" but not for csv, is the opposite as your Excel is working :)

J-G

  • Hero Member
  • *****
  • Posts: 953
Re: Creating a CSV File
« Reply #7 on: September 28, 2016, 12:56:05 am »
Thanks for the feedback, I’ll try to address the issues raised in order.

Maybe I should first say that I think I know where my problem lies - but I don’t yet have a solution.

@WP   
I am aware that #13 is a carriage return and that #10 is a line feed and that they are often used together. I had used both in testing but #13 does the job on its own.

I don’t have a problem with the honesty of ‘nonsense’ :)   but in fact re-use of the same variable doesn’t have the impact you suggest since I [save] the result after each use so the existing value is of no consequence. The reason for this (rather than a single string of all values) is because the Data_File is declared as a file of string[20]. I think this is my basic problem so changing the declaration may well be the solution. I did try declaring it as ‘file of string’ but that threw up the error ‘Typed files cannot contain reference-counted types’.

There would be no point in adding the first comma (line 5 of your code) and then removing it later.

I did try ‘writeln’ but got an error at compile time ‘can’t use readln or writeln on a typed file’. That’s why I used ‘write’ and  #13.

@lainz
I’ve no idea where you get the idea that CSV is separated by a semi-colon (“;”) it is one of the options in Excel but  CSV = Comma Separated Values. I have done some tests (just in case I was mistaken) but there is no doubt that using ‘;’ does not separate the values when imported into Excel, the string all goes into the first column.

@WP
Using tab might be an option but that wouldn’t be a CSV file so would have to be ‘imported’ into Excel using the Get External Data facility (aka the import wizard). Using .TXT does use the import wizard but worse than that it ignores the commas unless you go through the lengthy process of [next][next] etc.

The import wizard does allow you to select which separator should be considered - tab, semi-colon, comma, space or other but a ‘normal’ CSV file is simply ‘opened’ - it even displays the Excel icon as it’s filetype.

As I’ve said, I think my problem is that I used string[20] as the ‘type’ and that is simply collecting the ‘junk’ in memory adjacent to the real data. Why it doesn’t just use the formatted string and leave the rest as spaces or, better still, just trim the string to the characters used I can’t understand so I’ll have to do some more testing.

I also don’t understand why I can’t use writeln or why ‘file of string’ is unacceptable.

FPC 3.0.0 - Lazarus 1.6 &
FPC 3.2.2  - Lazarus 2.2.0 
Win 7 Ult 64

lainz

  • Hero Member
  • *****
  • Posts: 4460
    • https://lainz.github.io/
Re: Creating a CSV File
« Reply #8 on: September 28, 2016, 01:08:03 am »
Code: Pascal  [Select][+][-]
  1. @lainz
  2. I’ve no idea where you get the idea that CSV is separated by a semi-colon (;) it is one of the options in Excel but  CSV = Comma Separated Values. I have done some tests (just in case I was mistaken) but there is no doubt that using ‘;’ does not separate the values when imported into Excel, the string all goes into the first column.

As already said my Excel only works with CSV files with semi-colon separated when saved as CSV. From there I take that idea, because your code did not works for my Excel!

molly

  • Hero Member
  • *****
  • Posts: 2330
Re: Creating a CSV File
« Reply #9 on: September 28, 2016, 01:19:25 am »
I also don’t understand why I can’t use writeln or why ‘file of string’ is unacceptable.
I have no idea about the writeln as i did not read the context.

The file of string part is logical. A pascal string consist of a length field + the actual text. A normal pascal string has a fixed length of 255 chars. The length field is written to your file as well as the remaining characters that you have not filled (up to 255 chars). This is a storage format not recognized as being .csv and therefor programs such as excel are not able to make any sense out of it.

wp

  • Hero Member
  • *****
  • Posts: 11855
Re: Creating a CSV File
« Reply #10 on: September 28, 2016, 01:23:08 am »
You should have shown more code. Using a file of string[20] changes my answer completely:

Such a file is a binary file, although it seems to contain only text. In particular, a String[20] has a byte with the string length in front of the characters, this is the strange character you see in the header.

In total, this will not result in a valid csv file. A csv file consists only of "characters", no control characters. You must use a TextFile type to create such a file. And this is also where WriteLn is working.

Code: Pascal  [Select][+][-]
  1. var
  2.   data_file: TextFile;
  3. begin
  4.   AssignFile(data_file, 'c:\your_data_file_name.txt');
  5.   Rewrite(data_file);
  6.   Write_header;
  7.   // etc.

In my opinion there is no clear definition of csv files and no clear distinction to txt files. It is not true that only commas are allowed as column separators.

Why don't you write the Excel files in its native format? We have a very powerful library, fpspreadsheet, for this purpose. See http://wiki.lazarus.freepascal.org/FPSpreadsheet

molly

  • Hero Member
  • *****
  • Posts: 2330
Re: Creating a CSV File
« Reply #11 on: September 28, 2016, 01:38:05 am »
In my opinion there is no clear definition of csv files and no clear distinction to txt files. It is not true that only commas are allowed as column separators.
I agree to that although many references seem to agree that text/string literals should be enclosed by quotes and values can be just written, separated by whatever separator.

Hence, i prefer to use the tab separator as well as that allows for omitting the quotes, unless wanting to allow tabs in string literals.
« Last Edit: September 28, 2016, 01:40:07 am by molly »

J-G

  • Hero Member
  • *****
  • Posts: 953
Re: Creating a CSV File
« Reply #12 on: September 28, 2016, 01:42:42 am »
I also don’t understand why I can’t use writeln or why ‘file of string’ is unacceptable.
I have no idea about the writeln as i did not read the context.

The file of string part is logical. A pascal string consist of a length field + the actual text. A normal pascal string has a fixed length of 255 chars. The length field is written to your file as well as the remaining characters that you have not filled (up to 255 chars). This is a storage format not recognized as being .csv and therefor programs such as excel are not able to make any sense out of it.

Thanks Molly  -  another 'nugget' added to my armoury  :)
FPC 3.0.0 - Lazarus 1.6 &
FPC 3.2.2  - Lazarus 2.2.0 
Win 7 Ult 64

J-G

  • Hero Member
  • *****
  • Posts: 953
Re: Creating a CSV File
« Reply #13 on: September 28, 2016, 02:06:41 am »
You should have shown more code. Using a file of string[20] changes my answer completely:
That's what Molly keeps telling me   :)   but I only realized that that might be an issue after posting the original question

Such a file is a binary file, although it seems to contain only text. In particular, a String[20] has a byte with the string length in front of the characters, this is the strange character you see in the header.

In total, this will not result in a valid csv file. A csv file consists only of "characters", no control characters. You must use a TextFile type to create such a file. And this is also where WriteLn is working.

Code: Pascal  [Select][+][-]
  1. var
  2.   data_file: TextFile;
  3. begin
  4.   AssignFile(data_file, 'c:\your_data_file_name.txt');
  5.   Rewrite(data_file);
  6.   Write_header;
  7.   // etc.

!!!!!!!   That's my answer    !!!!!!!!

I did try 'file of text'  and even just 'text'    - - -  hadn't got as far as TextFile  :(

I have now created good CSV files - there is still a small issue ( the last figure is missing ) - but I do have clean data. - -  Thanks WP

In my opinion there is no clear definition of csv files and no clear distinction to txt files. It is not true that only commas are allowed as column separators.
To be fair I didn't mean to imply that ONLY commas are allowed - I meant that literally CSV means COMMA separated.

Why don't you write the Excel files in its native format? We have a very powerful library, fpspreadsheet, for this purpose. See http://wiki.lazarus.freepascal.org/FPSpreadsheet
That will be my reading for tomorrow  :D  I don't think it will be pertinent to this particular problem (I have had a quick look) but it could certainly be useful in future.

Essentially my problem is that I'm using Laz/FP as a 'Tool' not as an end in itself and there are so many new features that reading everything there is doesn't actually get the job in hand done. No doubt Thaddy will be along shortly to say RTFM  :D

FPC 3.0.0 - Lazarus 1.6 &
FPC 3.2.2  - Lazarus 2.2.0 
Win 7 Ult 64

Phil

  • Hero Member
  • *****
  • Posts: 2737
Re: Creating a CSV File
« Reply #14 on: September 28, 2016, 02:20:25 am »
Code: Pascal  [Select][+][-]
  1. @lainz
  2. I’ve no idea where you get the idea that CSV is separated by a semi-colon (;) it is one of the options in Excel but  CSV = Comma Separated Values. I have done some tests (just in case I was mistaken) but there is no doubt that using ‘;’ does not separate the values when imported into Excel, the string all goes into the first column.

As already said my Excel only works with CSV files with semi-colon separated when saved as CSV. From there I take that idea, because your code did not works for my Excel!

The character that Excel looks for as the separator between values in a CSV file is your locale's list character, typically a comma but can be a semi-colon or other character.

https://support.office.com/en-us/article/Import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba

So "Comma Separated Value" is not quite accurate and a bit confusing. But to use a comma everywhere means all items would need to be quoted, so it makes a kind of sense in that regard.

On Windows go to Regional and Language settings in Control Panel to see what it is on your system.




 

TinyPortal © 2005-2018