creating a csv file causing double quotes

Discussions and Tech Support related to the iMacros Firefox Add-on, including the built-in Javascript scripting interface for playing .js files.
Forum rules
Before asking a question or reporting an issue:
1. Please review the list of FAQ's.
2. Use the search box (at the top of each forum page) to see if a similar problem or question has already been addressed.
3. Try searching the iMacros Wiki - it contains the complete iMacros reference as well as plenty of samples and tutorials.
4. We can respond much faster to your posts if you include the following information: CLICK HERE FOR IMPORTANT INFORMATION TO INCLUDE IN YOUR POST
Post Reply
penfold1992
Posts: 12
Joined: Wed Jan 21, 2015 4:07 pm

creating a csv file causing double quotes

Post by penfold1992 » Thu Jan 22, 2015 3:35 pm

iMacro's - 8.6.0
Browser - Firefox 17.0.8 (dont judge! I am unable to update past this version!)
OS - Windows 7 64bit

Hello again :)

I have a little extraction tool in the following:

Code: Select all

var macro = "CODE:";
	macro += "TAG POS=6 TYPE=TABLE ATTR=TXT:*\n";
	macro += "TAG POS={{i}} TYPE=TD ATTR=scope:row&&TXT:INN00* EXTRACT=TXT\n";

for (var i=1; i < 20; i++)
{
	iimSet("i",i);
	iimPlay(macro);
	extract += "\"" + iimGetExtract() + "\", "
}

var saved = "CODE:";
	saved += "SET !EXTRACT {{input}}\n";
	saved += "SAVEAS TYPE=EXTRACT FOLDER=C:\\test\\testing FILE=test.csv";

iimSet("input",extract);
iimPlay(saved);
as you can see I am essentially taking a list and turning into a CSV file... the reason I am doing it this was is because I need to modify and monitor the string that comes out of each extraction step. I have left this out just for the sake of redundancy.

I require the format:
"output1"; "output2"; "output3"; "output4"; etc but I am currently getting
"""output1"", ""output2"", ""output3"", ""output4""

is there a way to fix this?
chivracq
Posts: 8719
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: creating a csv file causing double quotes

Post by chivracq » Thu Jan 22, 2015 5:52 pm

penfold1992 wrote:iMacro's - 8.6.0
Browser - Firefox 17.0.8 (dont judge! I am unable to update past this version!)
OS - Windows 7 64bit

Hello again :)

I have a little extraction tool in the following:

Code: Select all

var macro = "CODE:";
	macro += "TAG POS=6 TYPE=TABLE ATTR=TXT:*\n";
	macro += "TAG POS={{i}} TYPE=TD ATTR=scope:row&&TXT:INN00* EXTRACT=TXT\n";

for (var i=1; i < 20; i++)
{
	iimSet("i",i);
	iimPlay(macro);
	extract += "\"" + iimGetExtract() + "\", "
}

var saved = "CODE:";
	saved += "SET !EXTRACT {{input}}\n";
	saved += "SAVEAS TYPE=EXTRACT FOLDER=C:\\test\\testing FILE=test.csv";

iimSet("input",extract);
iimPlay(saved);
as you can see I am essentially taking a list and turning into a CSV file... the reason I am doing it this was is because I need to modify and monitor the string that comes out of each extraction step. I have left this out just for the sake of redundancy.

I require the format:
"output1"; "output2"; "output3"; "output4"; etc but I am currently getting
"""output1"", ""output2"", ""output3"", ""output4""

is there a way to fix this?
Yep, no wonder..., you add Double Quotes yourself to each Extract with this Statement...:

Code: Select all

extract += "\"" + iimGetExtract() + "\", "
... and the "SET/ADD !EXTRACT" or the "SAVEAS" does it automatically as well (at least on FF).
Drop yours, I would say...
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE').
- I don't even read the Qt if that (required) Info is not mentioned...!
- Script & URL help a lot for more "educated" Help...
penfold1992
Posts: 12
Joined: Wed Jan 21, 2015 4:07 pm

Re: creating a csv file causing double quotes

Post by penfold1992 » Mon Jan 26, 2015 11:30 am

The result of removing those quotes is the following:

"output1,output2,output3,output4, "

The issue with this output is that a csv file would read this as one entry with the value "output1,output2,output3,output4, " rather than 4 entries.

additionally, is there a way to count the number of entries within a csv file? This would enable me to set a loop correctly leaving off the additional ", " at the end of the string
chivracq
Posts: 8719
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: creating a csv file causing double quotes

Post by chivracq » Mon Jan 26, 2015 1:09 pm

penfold1992 wrote:The result of removing those quotes is the following:

"output1,output2,output3,output4, "

The issue with this output is that a csv file would read this as one entry with the value "output1,output2,output3,output4, " rather than 4 entries.
Hum, yeah-yeah, I understand the "Issue" with the Output as one String between Double Quotes...

But, hum..., then instead of building your final Extract for the SAVEAS from the .js Script, I would try to build it from your 'macro' .iim Script..., by passing it back and forth from the .js Script to the .iim Script where the new Extract would be added to the existing '!EXTRACT' that would already contain all the Extracts that you've already collected from the previous Runs...
But before trying that, you can first test if one Run of the .iim Macro with at least 2 Extracts in it, passed to the .js Script which then saves them in the final SAVEAS .iim Script returns the Results that you expect...

Hum, and for the Semi-Colon as a Delimiter instead of the Colon, you can use '!DATASOURCE_DELIMITER', but then you need to build the Final Extract from .iim anyway...
penfold1992 wrote:additionally, is there a way to count the number of entries within a csv file? This would enable me to set a loop correctly leaving off the additional ", " at the end of the string
Pfff, not really, or not straight forward, at least that I know of... There is an Enhancement Request here to add Support for Nested Variables in .iim Scripts for '!COLn', but we've been crying and shouting and begging for a long time already, but it's not been implemented (yet...!?) by Development... Grrrrr...!!! :mrgreen: Add your Voice in the Thread, I would say...

What you could do is, if you know that your Max-Nb of Columns is 20 for example, have an .iim Macro (try to) retrieve the Values of {{!COL1}} ... {{!COL20}} and do your own Counting in your .js Script on the Array returned in '!EXTRACT'... A bit cumbersome but that should work...

EDIT: But, hum, I forgot, but in the Post just after the Post I referred you to in the Thread where there is the Enhancement Request for Nested Variables, there is a Workaround by 'Skippyto' in JavaScript that you can pretty well reuse/adapt in/to your case... :idea:
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE').
- I don't even read the Qt if that (required) Info is not mentioned...!
- Script & URL help a lot for more "educated" Help...
penfold1992
Posts: 12
Joined: Wed Jan 21, 2015 4:07 pm

Re: creating a csv file causing double quotes

Post by penfold1992 » Mon Jan 26, 2015 1:36 pm

alright mate, cool I will look into some of those things and post back if I have an elegant solution.

passing it back and forth feels like a pain because my process is the following:
(x is the number of rows in the csv)

for x times
{
run macro to extract data
if data(1) == specific value
{remember data(2)}
}

for each value within remembered data string
{
pass each value to another macro to store in EXTRACT
extract
}

all I want to do is test each value and output it if it passes the test yet the solution is not exactly nice.
HOWEVER, negativity aside, the main point is that its possible to do.

the delimiter matters because before any of this is done, I extract another set of results from a database and the delimiter of that csv file is different to the delimiter iMacros spit out, so when I try to compare the two results I end up with a reading error!

I am trying to do an awful lot with my first project for iMacros (at least in my opinion I am XD) and I have now thought of an additional issue which requires me to read and write another file based on a csv file... again its absolutely possible to do with imacros and javascript but much easier in other programming languages that I have access to at work.
So why dont I do it that way... -sighs- because thats ANOTHER involved programming language that I would be running and another application the user has to run to get the result. In order to do a simple task on paper I would be using 3 programming languages to do the job.

finally, these issues can also be resolved with jquery. I dont know if jquery can be used with imacro's using javascript but the solution is also not elegant because everyone would have to have imacros with jquery enabled somehow and not only do I not have a dedicated server to #include jquery but the users wont necessarily have access to those areas anyway!

/rant.
I love programming :mrgreen:
skippyto
Posts: 108
Joined: Wed Aug 20, 2008 8:14 am

Re: creating a csv file causing double quotes

Post by skippyto » Mon Jan 26, 2015 4:03 pm

Hi penfold1992
additionally, is there a way to count the number of entries within a csv file? This would enable me to set a loop correctly leaving off the additional ", " at the end of the string
Fast solution (no need to count lines number) :
ret=1;
i=1 ;

Code: Select all

while (iimGetExtract() != "#EANF#" && ret == 1)
{
   if (i>1)
   {
       extract += ";" ;
   }
   iimSet("i",i);
   ret = iimPlay(macro);
   extract += iimGetExtract() ;
   i++ ;
}
A better solution to count the number of lines in a file (not only a csv) is to use the browser objects to open the file and count the lines (you can also use a smilar method to write your csv output in javascript instead of using a macro) :

Code: Select all

file = Components.classes["@mozilla.org/file/local;1"].createInstance(Components.interfaces.nsILocalFile);
more information and examples here : https://developer.mozilla.org/en-US/doc ... ILocalFile and here : https://developer.mozilla.org/en-US/Add ... s/File_I_O

Skippyto
penfold1992
Posts: 12
Joined: Wed Jan 21, 2015 4:07 pm

Re: creating a csv file causing double quotes

Post by penfold1992 » Mon Jan 26, 2015 4:30 pm

ok so I am back with good and bad news.

Good news. Solution found:

Code: Select all

var m = "CODE:\n";
    m += "SET !DATASOURCE [enter datasource here]\n";
    m += "SET !DATASOURCE_LINE {{line}}\n";
    m += "SET !EXTRACT {{!COL1}}\n";

var extract = ""
var i = 1

do
{
	iimSet("line",i);
	iimPlay(m);
	if (iimGetLastExtract(1) != null)
		{extract += iimGetLastExtract(1) + ",";}
	i++;
}
while (iimGetLastExtract(1) != null);

extract = extract.substr(0,extract.length - 1);
var req = extract.split(',');

window.alert(req.length - 1) // change this value to 2 if your csv has headers
bad news
A nicer solution would be to incorporate this into a function. This can be done of course by wrapping function around it, yeah nothing great there BUT an even better solution would be to store this function outside of your main functions. Thus not cluttering up the main function (as I said, I am going to be going a lot of actions within this one imacro script... if it breaks and I am not there to look over it, someone will have a heart attack when the open the file and see pages and pages of code)

so this is where another hurdle comes because with js its not as easy to just say "#include" but looking on the web you can see a solution involves the following:

Code: Select all

function include(filename)
{
    var head = document.getElementsByTagName('head')[0];
   
    script = document.createElement('script');
    script.src = filename;
    script.type = 'text/javascript';
   
    head.appendChild(script)
}
using this prompts an error that "document" does not exist.

as I was writing this reply, I saw Skippyto replied and it appears that issue might be resolvable as well...
penfold1992
Posts: 12
Joined: Wed Jan 21, 2015 4:07 pm

Re: creating a csv file causing double quotes

Post by penfold1992 » Tue Jan 27, 2015 3:34 pm

skippyto wrote: A better solution to count the number of lines in a file (not only a csv) is to use the browser objects to open the file and count the lines (you can also use a smilar method to write your csv output in javascript instead of using a macro) :

Code: Select all

file = Components.classes["@mozilla.org/file/local;1"].createInstance(Components.interfaces.nsILocalFile);
more information and examples here : https://developer.mozilla.org/en-US/doc ... ILocalFile and here : https://developer.mozilla.org/en-US/Add ... s/File_I_O

Skippyto
after trying for almost 3 hours now, I am not getting anywhere with this :(
I am completely new to javascript and I have no idea what I am doing if I am honest, I understand the concept here but i am having no luck.

maybe someone else can find something in that website but its a huge confusion to me :(
Airmanbzh
Posts: 12
Joined: Wed May 23, 2012 2:20 pm

Re: creating a csv file causing double quotes

Post by Airmanbzh » Tue Jan 27, 2015 4:22 pm

Hi,

You will find what you need in this part of skippyto link : https://developer.mozilla.org/en-US/Add ... ne_by_line

It shows you how to read a file line by line (you have to add a counter) but all the code is here ;)

Warning : This code performs synchronous readings
penfold1992
Posts: 12
Joined: Wed Jan 21, 2015 4:07 pm

Re: creating a csv file causing double quotes

Post by penfold1992 » Wed Jan 28, 2015 11:44 am

fresh eyes work wonders :)
Something that seemed so impossibly difficult yesterday just worked in about 5 minutes today.
I should take more breaks :roll:
chivracq
Posts: 8719
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: creating a csv file causing double quotes

Post by chivracq » Wed Jan 28, 2015 1:00 pm

penfold1992 wrote:fresh eyes work wonders :)
Something that seemed so impossibly difficult yesterday just worked in about 5 minutes today.
I should take more breaks :roll:
Ah-ah...! Good that you got it working...

Would be nice (might help other Users (in the future...)) if you could post your Final Script... :idea:
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE').
- I don't even read the Qt if that (required) Info is not mentioned...!
- Script & URL help a lot for more "educated" Help...
Post Reply