CSV Output

[eu4ic]

Hi,

Firstly, WOW, what a fantastic script. This has saved my life.

One quick question.

When outputting to a CSV file it produces quotation marks around each field entry. Is there a way to produce a CSV file without the quotation marks.

Just so you know, I have written a program in Visual Basic that when a button is pressed it downloads the file from the server and then has to load the values into text boxes on a form.

This is where the problem lies. It seems that it does not like the quotation marks. I have written into the VB program so that it strips the quotation marks but that still causes a problem.

The program already opens a csv file and populates text boxes on a form but the csv file is supplied by another company and that has no quotation marks. That CSV file works fine so I can only think that it is the quotation marks causing the problem.

Hope you can help.

Thanks in advance.

Symon.

[russellr]

Hi,

Up till now the only way to do what you're asking was to modify the code within FormMail.

But we liked you're idea so much, that we've implemented it as a standard feature.

The new version is 2.05 and it's attached below. You need to change a new configuration variable which is around line 1032.

Change it from:

$CSVQUOTE = '"'; // all fields in the CSV are quoted with this character;

to

$CSVQUOTE = ''; // all fields in the CSV are quoted with this character;


In other words, make it an empty string.

Please let us know how you go with the attached version, and if all is OK, we'll release it generally. (We have tested it quite extensively before giving this to you.)

[eu4ic]

Hi Russel,

Thanks for you quick response. The script now works great as far as the quotation marks are concerned.

However, when it creates the csv file is puts the field names and the data on the same line when I open it in notepad yet has both lines when opened in excel. I think that this may be causing a problem in my VB app.

As you can probably see, this means that it is read as double the number of fields.

Is there something I am missing. Do I need to create the CSV file with the field names already in it or is something strange happening.

I am happy to send you the CSV file if you want to have a look.

Thanks again for your help.

I will not be able to respond to this until the morming as it is 2:00 am and I need to go to bed.

Cheers

Symon.

[russellr]

This is a common problem with Windows systems.

The CSV file is written with UNIX-style line termination: lines are terminated with just a line feed.

On Windows, lines are terminated with carriage return plus line feed.

Notepad, therefore, doesn't see the lines properly. Excel is smarter and does.

You've got these options:

Use WordPad instead of Notepad - WordPad understands UNIX-style line termination.
When you download the CSV file from your server, ensure you download it as a text file. Your download program will convert the line termination for you. FTP and WinSCP both have options for this.
If you're not downloading the file before reading it with your VB program, then you'll need us to put an option in FormMail to configure the line termination characters.

[eu4ic]

Hi Russell,

Thanks for your reply. I am using the Internet Transfer Control in VB and can not seem to find any option to download the file as text.

Secondly, the way the file s being opened by the VB application is very basic, much like notepad.

Based on the above, would it be possible for you to configure formmail to put in the line feeds etc as notepad can read them.

Thanks in advance

Kindest regards

Symon.

[russellr]

Hi,

OK. Attached is another new version of FormMail (v 2.06).

You need to find this line (approx 1037):



$CSVOPEN = ""; // set to "t" to ensure text file conventions for your


and change it to:



$CSVOPEN = "t"; // set to "t" to ensure text file conventions for your


You'll also need to empty out (remove and re-create) your CSV file.

When FormMail writes to it again, it should have the Windows-style line terminations.

Let me know how you go. :)

[eu4ic]

Hi Russell,


thanks for that. But it still shows on a single line. I have completely deleted the original csv file a re-created a one from notepad, then re uploaded it.

Still seem to have the same problem.

Sorry ideas

Cheers

Symon.

[russellr]

Hi Symon,

I've been working on the assumption that your server is Windows. It's Linux or other UNIX isn't it, not Windows?

In that case, correct line termination for Linux is what the CSV file should have.

When you download the CSV file from your server to Windows, that's when the line termination should be changed (like I mentioned earlier with FTP, etc.).

If your VB program is reading directly from the Web, then it is its responsibility to handle the line terminations properly. To interoperate with other systems, it must handle the differences.

If can confirm that your server is Linux or other UNIX, then maybe we can add an option to force Windows line termination in the CSV file.

[eu4ic]

Thanks Russell,

Sorry about the confusion.

Yes the server I am using is unix, what version I do not know.

Thanks

Symon.

[russellr]

Hi Symon,

Version 2.06 was flawed (the $CSVOPEN configuration was incorrectly documented). Please ensure you delete any copies of v2.06 you have.

The attached version 2.07 should solve your problem.

You need to set two configuration variables as follows:

Around line 1045:


$CSVOPEN = "b"; // set to "b" to force line terminations to be


and, around line 1051:


$CSVLINE = "\r\n"; // line termination for CSV files. The default is


This tells FormMail to write CSV files with a line termination of carriage return plus line feed. Which is what you appear to need for your VB program.

Please let me know how you go.

[eu4ic]

Hi Russell,


You are an absolute legend, it works perfectly now thanks.

Cheers

Symon.

[russellr]

Terrific. Sorry it took a few attempts to sort out the mystery.....

System interoperability can be tricky sometimes.

Cheers. :D

[eu4ic]

Hi Russell,


No problems, you responded far quicker than most.

I am really appreciative of all your help and will be recommending your script for here on in.

Thanks again.

(A very happy) Symon.

[eu4ic]

Hi Russell,

Just a quick question, I have over 40 fields in the from that is being processed by your script. I create a CSV file as you know (thanks again). Is there a way to set a flag to say "include all fields".

The reason I ask this is that it takes some doing to add all 40 odd fields to the csvcolumns in the html form page.

Just a thought that would make life much easier for those with large forms.

Cheers

Symon.

[russellr]

An early draft of FormMail from several years ago did just what you're suggesting; all fields went into the CSV file regardless.

There are at least two problems with this:

Do you or do you not want "special" fields to go into the CSV file? In general, the answer is "I want some, but not all".
What order should the fields be put into the CSV file? This is particularly important because the App that's going to read the CSV file is often fixed in the order of fields it will accept. There's no automatic way to find the order of the fields as they appear on the form, so that's not an option (and even if you could, it's a bit "flakey" - you might want to change the format of your form in the future; also, unset fields don't get into the submission at all).
So, basically, the only solution to these two problems is to make the form specify the columns and their order for the CSV file.

Remember you can now format the "csv_columns" value over multiple lines:


<input type="hidden" name="csvcolumns" value="firstname, lastname,
email, Date, Time,
address, country">


That makes it a lot easier to deal with. :)