[Help] C# + Excel Extraction

Discussions and Tech Support related to website data extraction, screen scraping and data mining using iMacros.
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
mikeeam
Posts: 10
Joined: Thu Jul 07, 2011 4:54 pm

[Help] C# + Excel Extraction

Post by mikeeam » Thu Jul 07, 2011 5:52 pm

Hi guys!

This is my first post, but I often visit here when I need some help. Always I find the answer. Thanks for that.
But now i'm with some trouble here.. I'm not a developer and I'm in the first year of my college and just have a few classes of C#.

I'm having some problems here, and hope anybody can help. Down to business:

Every month I got to go to a site, do login with about 100 different logins to put some values that are found in a excel file. First I simplified the process with a macro that fill the needed informations so I just need to put the captcha.

Now I tried to make the macro to put the data to web, and it worked when I write the data into the macro.
Here is my macro:
VERSION BUILD=7361445
TAB T=1
URL GOTO=https://www8.receita.fazenda.gov.br/Sim ... .aspx?id=5
TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:frmPrincipal ATTR=ID:txtCNPJ CONTENT=07366228000117
TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:frmPrincipal ATTR=ID:txtCPF CONTENT=12164155847
SET !ENCRYPTION NO
TAG POS=1 TYPE=INPUT:PASSWORD FORM=NAME:frmPrincipal ATTR=ID:txtCodAcesso CONTENT=011369666574
PAUSE
'CAPTCHA'
URL GOTO=https://www8.receita.fazenda.gov.br/Sim ... fault.aspx
SIZE X=1098 Y=673
DS CMD=CLICK X=251 Y=98 CONTENT=
'Inserir o periodo de apuração
DS CMD=KEY X=541 Y=199 CONTENT=06/2011
DS CMD=CLICK X=540 Y=234 CONTENT=
'inserir o cmd key valor da apuração
DS CMD=CLICK X=531 Y=195 CONTENT=
DS CMD=KEY X=531 Y=195 CONTENT=965000
DS CMD=CLICK X=538 Y=261 CONTENT=
DS CMD=CLICK X=589 Y=259 CONTENT=

'Determina quais as receitas. no caso, apenas serviço anexo III
DS CMD=CLICK X=92 Y=369 CONTENT=
DS CMD=CLICK X=107 Y=435 CONTENT=
DS CMD=CLICK X=601 Y=528 CONTENT=

'Valor do serviço anexo III
DS CMD=CLICK X=100 Y=311 CONTENT=
DS CMD=KEY X=100 Y=311 CONTENT=965000
DS CMD=CLICK X=597 Y=530 CONTENT=
DS CMD=CLICK X=670 Y=513 CONTENT=
ONDIALOG POS=1 BUTTON=YES
DS CMD=CLICK X=691 Y=511 CONTENT=
ONDIALOG POS=1 BUTTON=YES
Where I filled with "965000", I found this value in the Excel. I need the macro to extract this value and do it all alone.

The Excel CSV is like this:

110,AFR INDUSTRIA PLASTICA,"R$ 426,00",,,"R$ 426,00"
119,JR & MARTO ,"R$ 0,00"
127,ALTERNATIVA IMPRESSOES DIGITAIS[57],"R$ 74.774,74",,,,"R$ 74.774,74"


There will be one macro to each login. The first CSV column it's a unique code. So I need something like this (probably it's all completely wrong ;/) :
VERSION BUILD=7361445

SET !DATASOURCE C:\Fechamento.csv

int line, cod;
line = 3;

do
{
cod = {{!COL1}}
line++
}
while (cod != 127)

line = line-1

SET !DATASOURCE_LINE {{line}} //it will check the code I manually put before, and use the column 3 of the line with the code.

TAB T=1
URL GOTO=https://www8.receita.fazenda.gov.br/Sim ... .aspx?id=5
TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:frmPrincipal ATTR=ID:txtCNPJ CONTENT=07366228000117
TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:frmPrincipal ATTR=ID:txtCPF CONTENT=12164155847
SET !ENCRYPTION NO
TAG POS=1 TYPE=INPUT:PASSWORD FORM=NAME:frmPrincipal ATTR=ID:txtCodAcesso CONTENT=011369666574
PAUSE
'CAPTCHA'
URL GOTO=https://www8.receita.fazenda.gov.br/Sim ... fault.aspx
SIZE X=1098 Y=673
DS CMD=CLICK X=251 Y=98 CONTENT=
'Inserir o periodo de apuração
DS CMD=KEY X=541 Y=199 CONTENT=06/2011
DS CMD=CLICK X=540 Y=234 CONTENT=
'inserir o cmd key valor da apuração
DS CMD=CLICK X=531 Y=195 CONTENT=
DS CMD=KEY X=531 Y=195 CONTENT={{!COL3}}
DS CMD=CLICK X=538 Y=261 CONTENT=
DS CMD=CLICK X=589 Y=259 CONTENT=

'Determina quais as receitas. no caso, apenas serviço anexo III
DS CMD=CLICK X=92 Y=369 CONTENT=
DS CMD=CLICK X=107 Y=435 CONTENT=
DS CMD=CLICK X=601 Y=528 CONTENT=

'Valor do serviço anexo III
DS CMD=CLICK X=100 Y=311 CONTENT=
DS CMD=KEY X=100 Y=311 CONTENT={{!COL3}}
DS CMD=CLICK X=597 Y=530 CONTENT=
DS CMD=CLICK X=670 Y=513 CONTENT=
ONDIALOG POS=1 BUTTON=YES
DS CMD=CLICK X=691 Y=511 CONTENT=
ONDIALOG POS=1 BUTTON=YES
I need it desperately.
Thank you guys!
MattBell7
Posts: 627
Joined: Thu Nov 26, 2009 11:07 am
Location: United Kingdom

Re: [Help] C# + Excel Extraction

Post by MattBell7 » Thu Jul 07, 2011 8:41 pm

So you want to search through the spreadsheet, until you find a row with a certain value, then use that's rows values to pass to the macro??
mikeeam
Posts: 10
Joined: Thu Jul 07, 2011 4:54 pm

Re: [Help] C# + Excel Extraction

Post by mikeeam » Fri Jul 08, 2011 10:38 am

MattBell7 wrote:So you want to search through the spreadsheet, until you find a row with a certain value, then use that's rows values to pass to the macro??
Yes!

It will search the value in the first column, then use (in the same line it found the value) the value of the column 3!
Any chance?!?

Thank you!
MattBell7
Posts: 627
Joined: Thu Nov 26, 2009 11:07 am
Location: United Kingdom

Re: [Help] C# + Excel Extraction

Post by MattBell7 » Fri Jul 08, 2011 12:39 pm

ok, you can't do it using purely iMacros, you'll need to use the scripting interface.

is your file a CSV or a spreadsheet?

if its a spreadsheet you can use a Macro quite easily to check the values, if its a CSV you'll have to use something else to read/interogate the values.
mikeeam
Posts: 10
Joined: Thu Jul 07, 2011 4:54 pm

Re: [Help] C# + Excel Extraction

Post by mikeeam » Fri Jul 08, 2011 12:44 pm

Better then.

It's a Google Doc. So I can export CSV and .xls (Spreadsheet). Using spreadsheet its easier?
MattBell7
Posts: 627
Joined: Thu Nov 26, 2009 11:07 am
Location: United Kingdom

Re: [Help] C# + Excel Extraction

Post by MattBell7 » Fri Jul 08, 2011 12:56 pm

well, with the scripting interface you can use VBA within excel to control iMacros, and the spreadsheets cells directly.
have a look at:
http://wiki.imacros.net/Sample_Code#VBA
mikeeam
Posts: 10
Joined: Thu Jul 07, 2011 4:54 pm

Re: [Help] C# + Excel Extraction

Post by mikeeam » Fri Jul 08, 2011 5:06 pm

Thank you.

I will try it here!
Tom, Tech Support
Posts: 3529
Joined: Mon May 31, 2010 4:59 pm

Re: [Help] C# + Excel Extraction

Post by Tom, Tech Support » Mon Jul 18, 2011 2:32 pm

Mike,

The following vbs script shows a simplistic method for searching the file for the starting row. The concept would be the same with C#, just the syntax will be different. This does not require using Excel, but it also isn't necessarily the most efficient approach.

Code: Select all

Const END_OF_FILE = -1240

Dim macroFindRow
macroFindRow = "CODE:"
macroFindRow = macroFindRow & "SET !DATASOURCE C:\Fechamento.csv" & vbNewLine 
macroFindRow = macroFindRow & "SET !DATASOURCE_LINE {{line}}" & vbNewLine 
macroFindRow = macroFindRow & "SET !EXTRACT {{!COL1}}" & vbNewLine 

Dim macroMain
macroMain = "CODE:"
macroMain = macroMain & "SET !DATASOURCE C:\Fechamento.csv" & vbNewLine 
macroMain = macroMain & "SET !DATASOURCE_LINE {{line}}" & vbNewLine 
macroMain = macroMain & "TAB T=1" & vbNewLine 
macroMain = macroMain & "URL GOTO=https://www8.receita.fazenda.gov.br/SimplesNacional/controleacesso/autentica.aspx?id=5" & vbNewLine 
macroMain = macroMain & "TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:frmPrincipal ATTR=ID:txtCNPJ CONTENT=07366228000117" & vbNewLine 
macroMain = macroMain & "TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:frmPrincipal ATTR=ID:txtCPF CONTENT=12164155847" & vbNewLine 
macroMain = macroMain & "SET !ENCRYPTION NO" & vbNewLine 
macroMain = macroMain & "TAG POS=1 TYPE=INPUT:PASSWORD FORM=NAME:frmPrincipal ATTR=ID:txtCodAcesso CONTENT=011369666574" & vbNewLine 
macroMain = macroMain & "PAUSE" & vbNewLine 
macroMain = macroMain & "'CAPTCHA'" & vbNewLine 
macroMain = macroMain & "URL GOTO=https://www8.receita.fazenda.gov.br/SimplesNacional/Aplicacoes/ATSPO/pgdarf.app/Default.aspx" & vbNewLine 
macroMain = macroMain & "SIZE X=1098 Y=673" & vbNewLine 
macroMain = macroMain & "DS CMD=CLICK X=251 Y=98 CONTENT=" & vbNewLine 
macroMain = macroMain & "'Inserir o periodo de apuração" & vbNewLine 
macroMain = macroMain & "DS CMD=KEY X=541 Y=199 CONTENT=06/2011" & vbNewLine 
macroMain = macroMain & "DS CMD=CLICK X=540 Y=234 CONTENT=" & vbNewLine 
macroMain = macroMain & "'inserir o cmd key valor da apuração" & vbNewLine 
macroMain = macroMain & "DS CMD=CLICK X=531 Y=195 CONTENT=" & vbNewLine 
macroMain = macroMain & "DS CMD=KEY X=531 Y=195 CONTENT={{!COL3}}" & vbNewLine 
macroMain = macroMain & "DS CMD=CLICK X=538 Y=261 CONTENT=" & vbNewLine 
macroMain = macroMain & "DS CMD=CLICK X=589 Y=259 CONTENT=" & vbNewLine 
macroMain = macroMain & "'Determina quais as receitas. no caso, apenas serviço anexo III" & vbNewLine 
macroMain = macroMain & "DS CMD=CLICK X=92 Y=369 CONTENT=" & vbNewLine 
macroMain = macroMain & "DS CMD=CLICK X=107 Y=435 CONTENT=" & vbNewLine 
macroMain = macroMain & "DS CMD=CLICK X=601 Y=528 CONTENT=" & vbNewLine 
macroMain = macroMain & "'Valor do serviço anexo III" & vbNewLine 
macroMain = macroMain & "DS CMD=CLICK X=100 Y=311 CONTENT=" & vbNewLine 
macroMain = macroMain & "DS CMD=KEY X=100 Y=311 CONTENT={{!COL3}}" & vbNewLine 
macroMain = macroMain & "DS CMD=CLICK X=597 Y=530 CONTENT=" & vbNewLine 
macroMain = macroMain & "DS CMD=CLICK X=670 Y=513 CONTENT=" & vbNewLine 
macroMain = macroMain & "ONDIALOG POS=1 BUTTON=YES" & vbNewLine 
macroMain = macroMain & "DS CMD=CLICK X=691 Y=511 CONTENT=" & vbNewLine 
macroMain = macroMain & "ONDIALOG POS=1 BUTTON=YES" & vbNewLine 

Dim im, ret
Set im = CreateObject("iMacros")

im.iimInit

Dim line, found
line = 1

Do
	im.iimSet "line", line
	ret = im.iimPlay(macroFindRow)
	
	If ret = END_OF_FILE Then
		MsgBox "Reached end of file without finding value!"
		Exit Do
	End If
	
	If im.iimGetLastExtract(1) = 127 Then
		found = True
		Exit Do
	End IF
	
	line = line + 1
Loop

If found then
	im.iimSet "line", line
	im.iimPlay(macroMain)
End If
Note: I typed this up rather quickly and did not actually test it.
Regards,

Tom, iMacros Support
mikeeam
Posts: 10
Joined: Thu Jul 07, 2011 4:54 pm

Re: [Help] C# + Excel Extraction

Post by mikeeam » Mon Jul 25, 2011 5:40 pm

Sorry, I couldnt answer before. Traveling.

Thank you so much. I'll try it here and I let you know!
Post Reply