CSV Standard File Format

Information related to the use of iMacros for form filling and data upload.
Forum rules
iMacros EOL - Attention!

The renewal maintenance has officially ended for Progress iMacros effective November 20, 2023 and all versions of iMacros are now considered EOL (End-of-Life). The iMacros products will no longer be supported by Progress (aside from customer license issues), and these forums will also no longer be moderated from the Progress side.

Thank you again for your business and support.

Sincerely,
The Progress Team
Post Reply
User avatar
Tech Support
Posts: 4948
Joined: Tue Sep 20, 2005 7:25 pm
Contact:

CSV Standard File Format

Post by Tech Support » Fri Dec 09, 2005 11:31 pm

The CSV ("Comma Separated Values") file format is often used to exchange data between differently similar applications. The CSV file format is useable by Internet Macros, Microsoft Excel and many other applications.

The CSV Format:

1. Each record is one line - Line separator may be LF (0x0A) or CRLF (0x0D0A), a line seperator may also be embedded in the data (making a record more than one line but still acceptable).

2. Fields are separated with commas.

3. Leading and trailing whitespace is ignored - Unless the field is delimited with double-quotes in that case the whitespace is preserved.

4. Embedded commas - Field must be delimited with double-quotes.

5. Embedded double-quotes - Embedded double-quote characters must be doubled, and the field must be delimited with double-quotes.

6. Embedded line-breaks - Fields must be surounded by double-quotes.

7. Always Delimiting - Fields may always be delimited with double quotes, the delimiters will be parsed and discarded by the reading applications.

Here are some examples that demonstrate the rules above. Each sample describes the data and how the reading application should interpret it.

Standard line:

Code: Select all

AAA, BBB, CCC
Leading and trailing whitespace and embedded commas

Code: Select all

"  AAA ", "B,B,B", "   CCC,,,"
sdtony
Posts: 31
Joined: Tue Dec 13, 2005 6:15 am

Inputting data from Excel

Post by sdtony » Tue Dec 13, 2005 6:29 am

Looking at the csv explaination, should I convert all excel data into csv if I plan on using it in a macro? I'd like to create a macro that copies a cell value and pastes it into an online form (requires loging into a website). I'd like to then run the report (clicking the search button on the web page). I need to do this for approx 26,000 cells one after the other. How should I do this?
User avatar
Tech Support
Posts: 4948
Joined: Tue Sep 20, 2005 7:25 pm
Contact:

Post by Tech Support » Tue Dec 13, 2005 9:50 pm

Excel will do all the conversion for you: Just select SAVE AS and use "CSV" as file format :)
sdtony
Posts: 31
Joined: Tue Dec 13, 2005 6:15 am

Post by sdtony » Wed Dec 14, 2005 12:20 am

how would i get the data into the macro?
User avatar
Tech Support
Posts: 4948
Joined: Tue Sep 20, 2005 7:25 pm
Contact:

Post by Tech Support » Wed Dec 14, 2005 1:57 pm

ender
Posts: 3
Joined: Tue Jun 12, 2007 7:54 am

Post by ender » Tue Jun 12, 2007 8:05 am

I'm banging my head on the wall !!

I have Excell 2002 and it uses ";" instead of "," for separating the colums
I've tried to set it from the sistem "international options .." but can't get it.

I have to edit the file manually with notepad to change ";" into ","

anybody can help?

ciao e grazie
ender
Posts: 3
Joined: Tue Jun 12, 2007 7:54 am

Post by ender » Tue Jun 19, 2007 4:06 pm

Sorry, but my problem is something else

when I save the .csv file, instead of simple "," i get ";" that imacros does not recognize

I know this is an excell problem, but maybe someone knows how to get around it


tx
User avatar
Tech Support
Posts: 4948
Joined: Tue Sep 20, 2005 7:25 pm
Contact:

Post by Tech Support » Wed Jun 20, 2007 10:40 pm

In the USA and some other countries, for example the United Kingdom, Windows uses a dot (.) as decimal delimiter. For example, 0.5 = 1/2. In these countries, the data entries in a CSV file are separated by commas (,).

In continental Europe and some countries elsewhere Windows uses a comma (,) as decimal delimiter. For example, 0,5 = 1/2. In these countries, the data entries in a CSV file are separated by semi-colons ( ; ).

You can switch between ; and , by going to
Start > Control Panel > Regional and Language Options > Standards and formats drop down. You can change the country there. For ; as CSV separator select (for example) USA and for , as separator select (for Example) Germany.
ctrpapa
Posts: 2
Joined: Sat Feb 02, 2008 12:04 am

possible workaround

Post by ctrpapa » Sat Feb 02, 2008 2:31 am

maybe your actual data contains ; in it but if it doesn't you can always open up the csv file in a text editor (notepad, vim) and do a find and replace

hopefully you find the right setting in excel though
gmatch
Posts: 11
Joined: Mon Jan 19, 2009 2:29 am

Re: CSV Standard File Format

Post by gmatch » Mon Jan 19, 2009 3:16 am

Quote:
5. Embedded double-quotes - Embedded double-quote characters must be doubled, and the field must be delimited with double-quotes.

I'm not finding this to work. What's the current solution when a field has quotes/commas both inside it? Here's an example of what I can't get into 1 field:

Couple words ending in a comma, "sentence with quotes around it".

I've tried but failed with:

"Couple words ending in a comma, "sentence with quotes around it"."
""Couple words ending in a comma, "sentence with quotes around it".""
""Couple words ending in a comma, ""sentence with quotes around it"".""
"Couple words ending in a comma, ""sentence with quotes around it""."
Marlon
Posts: 9
Joined: Tue Nov 20, 2007 8:27 pm

Re: CSV Standard File Format

Post by Marlon » Wed Mar 18, 2009 3:38 pm

I am having the same problem with entries in my csv file. For example, in a specific spreadsheet cell, I have the string: cats and "hot dogs". What modifications do I do to the actual spreadsheet cell? When doing the form-filling with a CONTENT={{!COL3}}, I get "cats and" as the value. I've tried (similar to the above author):

"cats and ""hot dogs"""
cats and ""hot dogs""

Thank you and looking forward to a response.
Valeo
Posts: 0
Joined: Thu Nov 19, 2009 1:43 pm

Re: CSV Standard File Format

Post by Valeo » Fri Nov 20, 2009 10:45 am

Hello,
I have 2 questions :
1> My file.CSV have the connexion parameters
login1,pwd1
login2,pwd2
login3,pwd3
login4,pwd4
My imaros works (It goes to the website et loads some documents) when i give the value of DATASOURCE_LINE
By SET !DATASOURCE_LINE 1 or 2 or 3 or 4
How can i do if one day the users change le number (add more login ou delete 1) of login and password ?
2> I have to test on a website if one links exists, If it exists, the macro continues, else goto the next step. Can you tell me if your tools imacro can do it ?

Thanks you for your answer.
Post Reply