Creating a CSV File

[J-G]

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.

That link does at least show the distinction between CSV and 'Delimited' files but as usual, for Micro$oft, obfuscates the issue by stating :-
"...in which the comma character (,) typically separates each field of text."

My pedantry often gets me into trouble 

[rvk]

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.

And to make matters even worse... (although it's a long time since I checked this)
Microsoft Excel treats opening a file from the explorer (double click) different from opening it in Excel itself.

If I have a file.txt with comma-separated and it gives me an import wizard when double clicking it in explorer, it will give one column when opening it via "Open File" (skipping the wizard). And if that's the case, a semi-colon-separated file will give one column when opening with double click, and the wizard when opening in Excel.

(and visa versa when you have different settings in the Regional and Language settings)

(not sure if this is still the case, last time I checked was years ago)

[J-G]

The initial query is sorted but now I have a 'weird' issue. It still concerns a CSV file but this time it's an odd compile error.

Code: Pascal  [Select][+][-]

1. procedure Open_JDay_file;
2. Var x : Int64;
3. begin
4.   {I-} reset(JDay_File); {I+}
5.   if IOResult<>0 then
6.     begin
7.       rewrite(JDay_File);
8.       write(JDay_File,'Date,Time,......');
9.     end
10.   else
11.     begin
12.       x := fileSize(JDay_File);
13.       seek(JDay_File,x);
14.     end;
15. end;
16.  

JDay_File is declared as a TextFile and the error only triggers at line 12 - it doesn't complain at 'Reset', 'Rewrite' or 'write'  -  just at 'filesize' and 'seek'. the oddity is that the error is :-

Call by var for arg no. 1 has to match exactly: Got "Text" expected "File"

How can it think that 'JDay_File' is text at lines 12 & 13 but not at lines 4, 7 and 8 ?

I originally had line 12 as    seek(JDay_File,fileSize(JDay_File));    only putting x= in to try a resolution.

[Phil]

You're mixing functions for two different types of files.

Seek and FileSize can't be used with text files.

http://www.freepascal.org/docs-html/rtl/system/filefunctions.html

[J-G]

You're mixing functions for two different types of files.

Seek and FileSize can't be used with text files.

http://www.freepascal.org/docs-html/rtl/system/filefunctions.html

Thanks Phil  -  seekEOF(JDay_File)  compiles, I now have to test to see if it does what I hope  -  that is puts the pointer at the end of the file ready to write the next 'record'.

I'm still trying to get to grips with where to find answers in www.freepascal.org so please bear with me when I ask 'simple' questions.

[molly]

@J-G:
You hit one of those things that confuses many starters (and even sometime the more experienced users). File of xxx and text file are two different things. Probably why most prefer to use streams or other modern family of file functions.

....I now have to test to see if it does what I hope  -  that is puts the pointer at the end of the file ready to write the next 'record'.

Putting  the 'pointer' at end of file, yes perhaps. But, writing to it, no ;-) (think on which mode you opened the file in).

Perhaps you can consider using append instead ?

I'm still trying to get to grips with where to find answers in www.freepascal.org so please bear with me when I ask 'simple' questions.

Don't you worry about that. As long as people can see you are trying but simply missing some things here and there, then there will be no issues whatsoever 

[J-G]

@J-G:
You hit one of those things that confuses many starters (and even sometime the more experienced users). File of xxx and text file are two different things. Probably why most prefer to use streams or other modern family of file functions.

....I now have to test to see if it does what I hope  -  that is puts the pointer at the end of the file ready to write the next 'record'.

Putting  the 'pointer' at end of file, yes perhaps. But, writing to it, no ;-) (think on which mode you opened the file in).

Perhaps you can consider using append instead ?

I'm still trying to get to grips with where to find answers in www.freepascal.org so please bear with me when I ask 'simple' questions.

Don't you worry about that. As long as people can see you are trying but simply missing some things here and there, then there will be no issues whatsoever 

Thanks Molly.   Even when I don't ask the right question, You (and others) always come back with the right answer 

'Append' is another rust spot rubbed out of my memory bank   mind you, I seldom worked with text files. 8-10 years ago it was mostly database work and even then using BTreeFiler.

[molly]

You're more than welcome J-G.

Uhm.... i think i noticed an error inside your code but, it could very well the message board here ate away some characters with copy-pasting.

Code: Pascal  [Select][+][-]

1. procedure Open_JDay_file;
2. Var x : Int64;
3. begin
4.   {I-} reset(JDay_File); {I+}
5.   if IOResult<>0 then
6.     begin
7.       rewrite(JDay_File);
8.       write(JDay_File,'Date,Time,......');
9.     end
10.   else
11.     begin
12.       x := fileSize(JDay_File);
13.       seek(JDay_File,x);
14.     end;
15. end;
16.  

Note the highlighted line... the correct way to do that would read:

Code: Pascal  [Select][+][-]

1.   {$I-} reset(JDay_File); {$I+}
2.  

Note the dollar sign.

And in case it is able to help you out,  i've come up with the following code for you to study. Please note however that i haven't done this kind of thing in ages so there might be something amiss (although things do seem to work for me as expected). So, in case someone spots an error, please feel free to correct.

Code: Pascal  [Select][+][-]

1. program opening;
2.  
3. {$MODE OBJFPC}{$H+}
4.  
5. // Opens a text file with given filename in write mode.
6. // If the file does not exist it will be created else it will be opened in append mode
7. // Either way the file is opened in write mode and ready to be written to at the end
8. // of its content (if there is any).
9. Function OpenATextFile(Filename: String; var AtextFile: TextFile): Boolean;
10. var
11.   LastError: Integer;
12. begin
13.   // Check and see if the file exists
14.   {$I-}
15.   Assign(ATextFile, FileName);
16.   Reset(ATextFile);
17.   {$I+}
18.   // Retrieve and store IOError. Each time IOResult is read from it is
19.   // automatically restored to being zero. It will not keep the value of
20.   // the last encountered error.
21.   // So we safe-keep it in LastError variable.
22.   LastError := IOResult;
23.  
24.   // In case the file was opened in read mode correctly we have to close it
25.   // again (so we can open it in write mode).
26.   if (LastError = 0) then Close(ATextFile);
27.  
28.   // If LastError = 0 then the file existed and we would like to append,
29.   // otherwise we open the file with rewrite in order to create the file
30.   // Note that when doing rewrite the file is opened in write mode only
31.   // if you wish to be able to read as well then do an additional call to
32.   // function reset()
33.   if (LastError = 0) then
34.   begin
35.     {$I-}  
36.     Append(ATextFile);
37.     {$I+}
38.     Result := (IOResult = 0);
39.     if Result
40.     then WriteLn('File ', Filename,' opened for writing at end')
41.     else WriteLn('Failed to open existing File ', Filename,' for appending');
42.   end
43.   else
44.   begin
45.     {$I-}
46.     ReWrite(ATextFile);
47.     {$I+}
48.     Result := (IOResult = 0);
49.     if Result
50.     then WriteLn('File ', Filename,' newly created')
51.     else WriteLn('Failed to create new file ', Filename);
52.   end;
53. end;
54.  
55. var
56.   JDayFile: TextFile;
57.  
58. begin
59.   WriteLn('Opening file');
60.   If OpenATextFile('test.csv', JDayFile) then
61.   begin
62.     WriteLn('Writing to file');
63.     WriteLn(JDayFile, 'hello');
64.     WriteLn('Closing file');
65.     Close(JDayFile);
66.   end
67.   else WriteLn('Error');
68. end.
69.  

[J-G]

You're more than welcome J-G.

Uhm.... i think i noticed an error inside your code but, it could very well the message board here ate away some characters with copy-pasting.

Code: Pascal  [Select][+][-]

1. procedure Open_JDay_file;
2. Var x : Int64;
3. begin
4.   {I-} reset(JDay_File); {I+}
5.   if IOResult<>0 then
6.     begin
7.       rewrite(JDay_File);
8.       write(JDay_File,'Date,Time,......');
9.     end
10.   else
11.     begin
12.       x := fileSize(JDay_File);
13.       seek(JDay_File,x);
14.     end;
15. end;
16.  

Note the highlighted line... the correct way to do that would read:

Code: Pascal  [Select][+][-]

1.   {$I-} reset(JDay_File); {$I+}
2.  

Note the dollar sign.

Ahhhhh!!!    &  Duh !! 

I knew that the Dollar sign should be there - Error trapping is an area I've used for so long but it is now past 2am here and I'm not a sharp as I was last night 

When I ran the program (it compiled even with that error) it threw up an exception which I didn't understand (I do now!) so I moved the file creation to 'OnCreate' as a fudge and I could continue the development.

For the previous .CSV file I didn't need to concern myself with checking if the file existed, It could be created anew each run but with this one I needed to create it on the first run and then use it subsequently.

I did have an issue with close(JDay_File) in the 'OnCreate' which I was able to solve with system.close(.....

Thanks for your efforts and the code to study - I'll do so properly tomorrow    there looks to be a number of concepts new to me that will be very helpful.

[molly]

When I ran the program (it compiled even with that error) it threw up an exception which I didn't understand (I do now!) ...

Well, the tricky part there is that it is not actually an error (except for generating a run-time error when IO-Errors are encountered).

The curly braces are also used as comment delimiters, see also here.

So, in fact the {I-} and {I+} was treated as being comments and, comments are... well.. ignored by the compiler 

The only indication you might be able to get that it is indeed a comment is in case you are using an editor that has good support for syntax highlighting. For example i have my editor configured to display comments with a grey tone of colour using italics while the compiler directives are shown in bold and red. I'm unable to miss that in case i mistakenly forgot to put up the dollar sign there 

[J-G]

When I ran the program (it compiled even with that error) it threw up an exception which I didn't understand (I do now!) ...

Well, the tricky part there is that it is not actually an error (except for generating a run-time error when IO-Errors are encountered).

   Of course it's not      

The curly braces are also used as comment delimiters, see also here.

So, in fact the {I-} and {I+} was treated as being comments and, comments are... well.. ignored by the compiler 

That fact is well known to me   -   I'm playing the 'Past 2am' card !!!

The only indication you might be able to get that it is indeed a comment is in case you are using an editor that has good support for syntax highlighting. For example i have my editor configured to display comments with a grey tone of colour using italics while the compiler directives are shown in bold and red. I'm unable to miss that in case i mistakenly forgot to put up the dollar sign there 

After the Blue and White screen of the Turbo Pascal Editor, the syntax highlighting of the Laz Source Editor is fantastic, but even that doesn't help if you haven't got your brain in gear!

I've just found a way to change the colour of comments so I'll set that to grey rather than the default blue - whether it will curb my stupidity, we'll have to wait and see 

Although I solved the issue with a 'fudge', --  which did help for the long term by making me evaluate the concept of 'OnCreate'  --  I'll re-code the 'File exists?' option - maybe even using the 'Result :=' construct. ( I have read your code! )

[Sheepdog]

How big will the files you need to be, typically? How many rows, how many characters in a long one?

Will all of the data be available before you need to do the write-to-file, or are records coming in one at a time, over a long period, and writing them to the file, one at a time, as they arise best? (Like a system log file, for instance)

Even for quite big files, one approach would be to "assemble" the CSV file in a memo, and then use...

meYourMemo.lines.SaveToFile('YourCSV_file.txt');

...to save the memo to a file.

In the memo: Turn WordWrap off.

To look, in detail at what is in a file, use Textpad. Free evaluation copy from textpad.com Open the file of interest as "Binary", and you can see exactly what your program is doing to create new lines. EXPORT, FROM EXCEL, a small CSV file of some known data, and you can see what it expects to see for new lines.

[J-G]

I can't tell if you've read the whole thread (my problem is solved) but since you have shown an interest and bothered to comment I'll respond.

How big will the files you need to be, typically? How many rows, how many characters in a long one?

The biggest file I've created up to now is 12000 lines of 180 characters  -  1.1Mb

Will all of the data be available before you need to do the write-to-file, or are records coming in one at a time, over a long period, and writing them to the file, one at a time, as they arise best? (Like a system log file, for instance)

The data is created 'on the fly' so each iteration of a loop creates one record which has to be recorded before moving on.

Even for quite big files, one approach would be to "assemble" the CSV file in a memo, and then use...

meYourMemo.lines.SaveToFile('YourCSV_file.txt');

...to save the memo to a file.

In the memo: Turn WordWrap off.

An interesting idea but I would never save a CSV file as .TXT - I'm a pedant   I've been reading the RTL PDF today with a view to using the Sysutils unit calls rather than the deprecated DOS unit calls, so I am looking at 'SaveToFile' but not implemented anything yet.

To look, in detail at what is in a file, use Textpad. Free evaluation copy from textpad.com Open the file of interest as "Binary", and you can see exactly what your program is doing to create new lines. EXPORT, FROM EXCEL, a small CSV file of some known data, and you can see what it expects to see for new lines.

I already know that it expects only #13  which is a 'Carriage Return'  -  Printers demand #10 as well as that is a line feed so without it a printer would not advance the paper, just overprint on the same line. Excel deals with the line feed automatically.