Solved

Post questions from excel to my site

Posted on 2012-12-24
41
674 Views
Last Modified: 2012-12-30
Hello friends,

I have a Urgent request.

My friend ended up deleting 100's of questions from this site.

http://tinyurl.com/d57ffgs

Please note i have made this URL tiny so the site name is not shown open

Please use this account to login
User name : EE-Test
Pass : EE-Test


We have the excel with these details

Title | Category | body of the question

I want help with a script that gets posted when run macro into the logged in user account

We need to get this done as easily as possible and as soon as possible and would need help please.

regards
0
Comment
Question by:bsharath
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 19
  • 16
  • 2
  • +2
41 Comments
 
LVL 11

Author Comment

by:bsharath
ID: 38719800
Website is drupal and Mysql is the database

This link is fine

http://tinyurl.com/d57ffgs
Need to login to get to the page

Its a secured user account so will disable it after some time


We can do it with excel i guess but not sure

thanks for your help
0
 
LVL 16

Expert Comment

by:HagayMandel
ID: 38719952
I guess your final solution is to create a simple module, that only has admin setting page, and in which, you'll be able to run a script that will read the Excel file row by row, and insert the row to the database according to the database schema.
Optionally, you can write the same script in a file external to Drupal, but then, you'll have to make sure that you take care on ALL related data, such as additional columns  that Drupal generates, the revision table etc.

Which Drupal version?
0
 
LVL 11

Author Comment

by:bsharath
ID: 38719953
Thanks
We have 6.27 version

Module i dont know but if we can do with a imacro/excel macro or some vb script would be fine
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 110

Expert Comment

by:Ray Paseur
ID: 38720051
If MySQL is the data base and the Excel spreadsheet contains the information you want to load into the data base, you can save the Excel spreadsheet in csv format, and you can run a PHP script to read the csv file with fgetcsv().  This will give you an array of data elements that can be used in a MySQL query.  Each time your script calls fgetcsv() you will get one line of the csv file, which usually corresponds to one row of the data base table.
0
 
LVL 11

Author Comment

by:bsharath
ID: 38720053
Thanks but any way to do from the front end?
Rather than database?
0
 
LVL 12

Expert Comment

by:junipllc
ID: 38720087
Use Feeds http://drupal.org/project/feeds. It is a fantastic module that works in both 6 and 7. If you run into issues mapping some of the fields, look up the modules that work with Feeds, such as Feeds Tamper.

Be sure the data is CSV with LF line endings, UTF-8. You'll get the best results that way.

Cheers,

Mike
0
 
LVL 11

Author Comment

by:bsharath
ID: 38720096
Thanks as mentioned anything from front end?
0
 
LVL 12

Expert Comment

by:junipllc
ID: 38720115
Feeds is front end.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38720427
There have been some suggestions that are probably better but you seem to really want to do this as if you were typing it in on the website. This should be possible, I have made a first attempt at this. Tested up until the point where a question would be submitted. Before I do that I want to ask you to clarify some things:
- the ckeditor seemed harder to manipulate than the plain text body, would it be ok to use the latter? (I already coded the switch-over)
- does the list you have contain an id or text value for category?
- when a category is selected, a sub-category appears. Can this stay empty?
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38720436
Here is the code so far, I took out the website url. If you want to test it use stepping through or put in some breakpoints, like I said it's only tested up to the submit code near the end of the while-wend loop.
Option Explicit

Const C_URL = "http://www.**************.com"
Const C_URL1 = C_URL & "/user"
Const C_URL2 = C_URL & "/create/question"

Const C_USER = "EE-Test"
Const C_PASS = "EE-Test"

Dim objIE As InternetExplorer

Sub DoProcess()
    Set objIE = New InternetExplorer
    objIE.Visible = True

    Dim h As HTMLDocument
    Dim f As HTMLFormElement
    Dim fe As HTMLFormElement
    Dim fsb As HTMLInputButtonElement

    ' login
    objIE.navigate C_URL1
    DoWait
    Set h = objIE.document
    Set f = h.forms("user-login")
    If Not f Is Nothing Then
        For Each fe In f.elements
            If fe.Name = "name" Then fe.innerText = C_USER
            If fe.Name = "pass" Then fe.innerText = C_PASS
        Next
        f.submit
        DoWait
        Set f = Nothing
    End If
    Set h = Nothing

    Dim rw As Integer ' row
    rw = 2
    ' now post questions
    While ActiveSheet.Cells(rw, 1) <> ""
        Debug.Print "row: " & rw
        objIE.navigate C_URL2
        DoWait
        Set h = objIE.document
        h.getElementById("switch_edit-body").Click
        DoWait
        Set fsb = Nothing
        Set f = h.forms("question-form")(0) ' there are 2 forms with this ID/name (1 hidden), we need the 1st one
        For Each fe In f.elements
            Dim feName As String, feID As String
            feName = ""
            feID = ""
            On Error Resume Next
            feName = fe.Name
            feID = fe.ID
            If Err.Number <> 0 Then Err.Clear
            On Error GoTo 0
            Debug.Print "*Name:" & feName & "*ID:" & feID & "*"
            If feName = "title" Then fe.innerText = ActiveSheet.Cells(rw, 1)
            If feID = "edit-taxonomy-3_1" Then
                fe.selectedIndex = CLng(ActiveSheet.Cells(rw, 2))
                fe.FireEvent "onchange", fe
                DoWait
            End If
            If feID = "edit-taxonomy-3_2" Then ' subcategory is hidden
                fe.selectedIndex = 1 'CLng(ActiveSheet.Cells(rw, 2))
            End If
            If feName = "body" Then fe.innerText = ActiveSheet.Cells(rw, 3)
            If feID = "edit-submit" Then Set fsb = fe ' Save button
        Next
        If fsb Is Nothing Then
            Debug.Print "* form submit"
            f.submit
        Else
            Debug.Print "* Save click"
            fsb.Click
        End If
        DoWait
        Set fsb = Nothing
        Set f = Nothing
        Set h = Nothing
        rw = rw + 1
    Wend
    ' done
    objIE.Quit
    Set objIE = Nothing
End Sub

Sub DoWait()
    While objIE.Busy Or objIE.readyState <> READYSTATE_COMPLETE
        DoEvents
    Wend
End Sub

Open in new window

PS: to use this code you need to add 2 references to your Excel VBA project: "Microsoft HTML Object Library" and "Microsoft Internet Controls".

PPS: to keep it simple for now I assumed you have a category ID in the second column of the Excel sheet.
0
 
LVL 11

Author Comment

by:bsharath
ID: 38720560
Thanks
Seems promising

Category name would be better but ID where can i find that?

Can i have success or failure mentioned back in column "D"
0
 
LVL 11

Author Comment

by:bsharath
ID: 38720567
I did check the source and add the category id's but it was failing to add the category

Can we skip the one that failed for some reason and try next in such cases ?
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38720790
Ah, I wasn't thinking straight. I used selectedIndex which is not the same as the value of the category. When I look at the source there is a different list. The category selections are dynamically added to the page. Maybe the easiest would be to use a direct translation so Hardware=2, Linux=3 etc. If it's not in the list for some reason use 1 for Anonymous. This could be done either in code or in an Excel lookup. For example:
Function GetCatIndex(sCatName As String)
    Select Case sCatName
        Case "Hardware":
            GetCatIndex = 2
        Case "Linux":
            GetCatIndex = 3
        Case "Apple":
            GetCatIndex = 4
        ' ...
        Case "Windows OS":
            GetCatIndex = 18
        ' unknown -> Anonymous?
        Case Else:
            GetCatIndex = 1
    End Select
End Function

Open in new window

and then use that on line 61 of the posted code:
fe.selectedIndex = GetCatIndex(ActiveSheet.Cells(rw, 2))

Open in new window


If there are other reasons it could fail it should be possible to use "on error" to determine success or failure and put that in column D. Although you probably mean an error that's reported on the page itself. That might be more difficult, would have to scan the source of the page after submit to see if a popup is being shown?

Does the body text contain html? I'm not sure how it will end up. Maybe just a matter of testing.
0
 
LVL 11

Author Comment

by:bsharath
ID: 38720796
Thanks
To start with i placed 1 in excel column "B" and ran the macro but still did not post and the IE window closed before i could see where it failed

The body can have html but to start with we can go with plain text
0
 
LVL 11

Author Comment

by:bsharath
ID: 38720797
I could see the category was changed to 1
 thats anoynomus and the browser closed
0
 
LVL 11

Author Comment

by:bsharath
ID: 38720800
It even clicked the save button and then the category was not accepted and turned red and exitted
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38720801
Here is a slightly nicer approach, replace lines 60-64 with:
            If feID = "edit-taxonomy-3_1" Then
                Dim fse As HTMLSelectElement, idx As Long
                Set fse = fe
                For idx = 1 To fse.Options.Length - 1
                    Debug.Print "** check option: " & idx & " - " & fse.Options(idx).Text
                    If fse.Options(idx).Text = ActiveSheet.Cells(rw, 2) Then
                        fse.selectedIndex = idx
                        Exit For
                    End If
                Next
                ' set default category?
                If fse.selectedIndex = 0 Then fse.selectedIndex = 1
                Set fse = Nothing
                'fe.selectedIndex = GetCatIndex(ActiveSheet.Cells(rw, 2))
                'fe.FireEvent "onchange", fe
                'DoWait
            End If

Open in new window

0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38720803
Ah, just saw your previous posts. Maybe subcategory is required as well?
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38720806
If you don't want the browser to close put a breakpoint on the line "objIE.Quit"
0
 
LVL 11

Author Comment

by:bsharath
ID: 38720808
Can you give me the full code in one snippet please i get a code error message

sub category is not mandate
0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 38720813
Ok, I just posted 2 test questions using this code:
Option Explicit

Const C_URL = "http://www.**************.com"
Const C_URL1 = C_URL & "/user"
Const C_URL2 = C_URL & "/create/question"

Const C_USER = "EE-Test"
Const C_PASS = "EE-Test"

Dim objIE As InternetExplorer

Sub DoProcess()
    Set objIE = New InternetExplorer
    objIE.Visible = True

    Dim h As HTMLDocument
    Dim f As HTMLFormElement
    Dim fe As HTMLFormElement
    Dim fsb As HTMLInputButtonElement

    ' login
    objIE.navigate C_URL1
    DoWait
    Set h = objIE.document
    Set f = h.forms("user-login")
    If Not f Is Nothing Then
        For Each fe In f.elements
            If fe.Name = "name" Then fe.innerText = C_USER
            If fe.Name = "pass" Then fe.innerText = C_PASS
        Next
        f.submit
        DoWait
        Set f = Nothing
    End If
    Set h = Nothing

    Dim rw As Integer ' row
    rw = 2
    ' now post questions
    While ActiveSheet.Cells(rw, 1) <> ""
        Debug.Print "row: " & rw
        objIE.navigate C_URL2
        DoWait
        Set h = objIE.document
        h.getElementById("switch_edit-body").Click
        DoWait
        Set fsb = Nothing
        Set f = h.forms("question-form")(0) ' there are 2 forms with this ID/name (1 hidden), we need the 1st one
        For Each fe In f.elements
            Dim feName As String, feID As String
            feName = ""
            feID = ""
            On Error Resume Next
            feName = fe.Name
            feID = fe.ID
            If Err.Number <> 0 Then Err.Clear
            On Error GoTo 0
            Debug.Print "*Name:" & feName & "*ID:" & feID & "*"
            If feName = "title" Then fe.innerText = ActiveSheet.Cells(rw, 1)
            If feID = "edit-taxonomy-3_1" Then
                Dim fse As HTMLSelectElement, idx As Long
                Set fse = fe
                For idx = 1 To fse.Options.Length - 1
                    Debug.Print "** check option: " & idx & " - " & fse.Options(idx).Text
                    If fse.Options(idx).Text = ActiveSheet.Cells(rw, 2) Then
                        fse.selectedIndex = idx
                        Exit For
                    End If
                Next
                ' set default category
                If fse.selectedIndex = 0 Then fse.selectedIndex = 1
                Set fse = Nothing
                fe.FireEvent "onchange", fe
                DoWait
            End If
            If feID = "edit-taxonomy-3_2" Then ' subcategory is hidden initially
                fe.selectedIndex = 1
            End If
            If feName = "body" Then fe.innerText = ActiveSheet.Cells(rw, 3)
            If feID = "edit-submit" Then Set fsb = fe ' Save button
        Next
        If fsb Is Nothing Then
            Debug.Print "* form submit"
            f.submit
        Else
            Debug.Print "* Save click"
            fsb.Click
        End If
        DoWait
        Set fsb = Nothing
        Set f = Nothing
        Set h = Nothing
        rw = rw + 1
    Wend
    ' done
    objIE.Quit
    Set objIE = Nothing
End Sub

Sub DoWait()
    While objIE.Busy Or objIE.readyState <> READYSTATE_COMPLETE
        DoEvents
    Wend
End Sub

Open in new window

The way errors are shown (like question is too short) may be nice for the users but not so nice in this case. Here's a screenshot of my test data and breakpoints:capture
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38720815
sub category is not mandate
ok I did put the code for that back in the previous post but either way shouldn't matter I guess. Probably for testing (like I did at first) using a question that contains too few words is what went wrong earlier?
0
 
LVL 11

Author Comment

by:bsharath
ID: 38720820
Can you please attach the excel as for me the categories is where its failing to post
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38720822
Sure, is it ok if the site name is in the excel?
0
 
LVL 11

Author Comment

by:bsharath
ID: 38720824
Please remove it and upload
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38720827
Ok here it is.
tsta.xls
0
 
LVL 11

Author Comment

by:bsharath
ID: 38720831
Even now its the same as mine

Question category is required is the message i get

After the category is set and the content in the body and while submitting the category gets resetting to default and the site does not accept it as no category is selected
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38720842
How odd. I assume you have seen the questions I posted there? I'm using IE8 and an old Excel (2002), maybe that makes a difference? You could try fiddling with the code like take out the FireEvent.
0
 
LVL 11

Author Comment

by:bsharath
ID: 38720853
I have IE9 and Office 2007
I commented the FireEvent line but no change
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38720858
Anything that you can find out what's happening maybe from setting a Watch in the code or looking at the html source could help.

Obviously it's not happening on my system or those posts would not have gone through either.

I think I can try it out on a configuration similar to yours tomorrow but will not have much time during the day.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38720860
Actually one more thing you could try: set IE8 compatibility. After the IE window has opened, activate it and press F12, then change the browser mode (in the menu bar of the Dev Tools). It could change the way the category selection works. If it's your friend's website he may know what's going on?
0
 
LVL 11

Author Comment

by:bsharath
ID: 38722636
I just tried on IE7/IE8/IE9 all 3 systems i have the same issue

For some reacon the categories first time is set fine and while saving the categories change back
Attached screenshot
Category.JPG
0
 
LVL 11

Author Comment

by:bsharath
ID: 38722642
Also would be great if you can help with this
http://www.experts-exchange.com/Programming/Misc/Q_27975089.html

Very similar front end help but when free
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38723129
Looks like the ".FireEvent" is necessary (copies the value to a hidden input) and not working in Excel 2007 (and/or in combination with IE9). Did you test with that line commented out or with the FireEvent active?

Maybe it's possible to fill the hidden field directly, could be much easier than finding out what's wrong with the current code, I'll have another look later on.

I did see that other question when you posted it but frankly didn't really understand what you were after, so I skipped it at the time...
0
 
LVL 11

Author Comment

by:bsharath
ID: 38723154
I have placed more info here
http://www.experts-exchange.com/Programming/Misc/Q_27975089.html#a38723147

FireEvent it was commented while i checked in machine IE8 and now i have removed the comment and it works perfectly

Thanks a lot i can survive with IE8 and current functionality

thanks a lot for your help much appretiated...
:-)

Please have a look at the other post when possible
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38723238
Great!
0
 
LVL 11

Author Comment

by:bsharath
ID: 38730908
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38731285
I had a look but no ideas so far, it's a completely different approach and I can't see a direct way to integrate the login from my code with that way of downloading data from the website.
0
 
LVL 11

Author Comment

by:bsharath
ID: 38731779
Ok Thanks
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question