Help with data import project

Posted by JCOFFEY on April 28, 2011 at 9:39pm

Hi! My name is Justin Coffey and I'm really looking for some ideas/suggestions on how to complete a task on a project I'm working on.

The site I'm developing is acting as a "web portal" for independent sales representatives. At this time, I'm working independent from the company but with their go ahead. As a result, my goal is to not ever directly link up with their servers or databases at their corporate headquarters.

Here is a brief example of the real world workflow:

Independent Rep. ("IR") logs in to my site and submits an enrollment form. The form is NOT published and awaits moderation using the Mod8 module.
Designated corporate or master agents login and review the submitted forms to ensure they are formatted correctly or to inform the IR of any additional information or changes they need to make before the form is required. Once it is completed successfully, the moderator approves it and the content permission only allows the author and specific users (set by permissions) to view it.
Each week, a list will be generated using the "Views" module and exported (either manually, or in a .CSV, to become an Excel file.. Havn't figured this one out quite yet) to be sent to the Corporate HQ. Currently this is ALL being done manually (Read paper enrollment form and input the information into an Excel document).

Here's where the fun starts and I need a lot of help with.

Corporate HQ submits the information and after a week or two receives reports from the 3rd party suppliers/carriers if the enrollment was approved, requires more information, or cancelled. They combine all these reports into a single Excel file and send it to the Master Agents. The functionality they want from me is to be able to upload/import the file (which I said would be converted into a .CSV file) to my site and then update the IR's enrollment forms to show if they were Approved, etc, etc. and then have the ability to create a "Views" page to produce a Report for the individual reps on their accounts and status.

So.

My question is how I could import CSV data and have Drupal interpret it and update the forms and/or provide a report to show their statuses?

I'm thinking of using like Node References, Hidden CCK fields, etc but I havn't really done any research on this yet and am hoping to save a lot of time with your help on the research and "how to".

Thanks a ton for any input and ANY input would be extremely helpful!

Comments

I would look into Rules.

Posted by xamox on April 29, 2011 at 1:31am

I would look into Rules. (http://drupal.org/project/rules).

You can setup a rule or ruleset to be on a schedule.
The rule can change workflow, fields, perform an action, send notifications, etc.
Then just have a cron run at a certain time, you can setup conditions for the rule.
You basically can tell the rule to perform an action. You may have to manually write the action if it doesn't exist or there isn't some action module to do what you want, but writing actions is pretty simple.
If you need to perform that same action on say a list, like a view, then you want views bulk operations:
http://drupal.org/project/views_bulk_operations

You can then basically use the following process workflow:
If time elapsed, fire cron -> If conditions true for rule fire views bulk operation -> iterate through each item in view and perform action.

If you need to import CSV there is also the feeds module:
http://drupal.org/project/feeds

Custom module

Posted by mathieso on April 29, 2011 at 11:49am

Haven't done something exactly like this before, so can't be as useful as other people.

Seems like a job for a custom module. Rules for corporate data are sometimes so labyrinthine that only custom logic will do. Also, the data you get back from HQ can't be trusted 100%. Bad keys, etc., are not uncommon as data gets moved around.

Import the data into a temp table in your Drupal DB. Does it have to be CSV? There might be other exchange formats that are easier. E.g., how does a PHP CSV processor handle commas in fields? Don't know.

I'll assume your export includes a key like user id that is retained until the data gets back to Drupal. Also assume there is one record per IR. I'll also assume you're using node profile, so that each user's data is stored in a node, to make CCK easier.

Code would be something like this:

Put Drupal into maintenance mode
Create temp table
Import, with one record per IR
while there is more data in temp table
    Get next temp table record.
    IR_UID = key from that record.
    Lookup Drupal records with that key. (CCK scatters the data around.)
    Update Drupal data. May be lots o' if statements with strange conditions.
end while
Delete temp table
Put Drupal into production mode

You can do error checking along the way. E.g, check that a node for IR_UID exists. Accumulate error messages, and spit them out all at once at the end of the update.

If you want more sophisticated error handling, you could keep the imported records that had errors. Have someone correct them, then rerun the update. That's easier for people if the data is kept in Excel, and not migrated to CSV. Have your module read the spreadsheet directly, and then flag errors by writing back to the spreadsheet.

Hope this helps. But as I said, someone who's done this before could give you better advice.

Kieran

Kieran Mathieson
kieran@dolfinity.com

If the data set is large then

Posted by xamox on May 2, 2011 at 3:12pm

If the data set is large then you can still use rules and views bulk operations, but also roll it into job queue (http://drupal.org/project/job_queue) or it supports the batch API. Also if data needs to persist and appended to then a batch method could allow for additional items to be put into the queue.

Another advantage to using rules/VBO, etc. is that they are exportable as a feature (http://drupal.org/project/features). Then you could make this type of import a feature and modify it accordingly if your data set changes in the future or has to be configured differently for different types of data import.

Thanks for the input so far!

Posted by JCOFFEY on May 7, 2011 at 9:06pm

Thanks for the input so far! I've been working on another project and doing other things over the last few days. I'll check out everyone's suggestions and report back with how they've worked.

Thanks again for the help.

Trying to import as well using Feed

Posted by dotman on May 13, 2011 at 7:31pm

First of all, hello all from Northern Michigan.

I'm new to drupal, and I've been trying to import a csv exported from an Access DB using Feeds. I've tried everything and get nothing but errors.

Can anyone lead me in the right direction to get this working. The csv contains a lot html in the body mapping, and that seems to be causing the trouble. Here's my latest error. I've tried separator, text qualifier, etc. It's encoded utf-8:

SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'last_comment_uid' at row 1

Could anyone please give me some direction or suggest another method geared towards a designer?

Maybe if someone who is having success with any method that supports nodes, authorid, custom fields and taxonomy might try with my csv, I would be happy to send it along.

Really appreciate any help.

Thanks,
Doug

The first thing I would do is

Posted by xamox on May 15, 2011 at 2:06pm

The first thing I would do is actually look at that column on row one and see what the value is. Sounds like you are trying to map a value to a non-integer value.

So if you CSV looks like this:
blah, foo, bar, baz, last_comment_uid, bing, bash
"John","Smith","Kung Fu", "football", "WalkaWalka", 4, 12

Make sure that "WalkaWalka" would be a number and not a string, or it's not a huge number like 1.23232 as that is a float and not an integer.

Another option could be to try the node import module:
http://drupal.org/project/node_import

How far north?

In Traverse city, and another question

Posted by dotman on June 16, 2011 at 9:42pm

And I hope to get downstate fro one of your meetups.

Well I'm getting the basic text content in, but I cannot figure out how to do the following two things. i've posted at the Feeds issu page, but no luck so far.

I cannot figure out how to import a field that has comma separated list of terms into a Term Reference field for my Article Content type. I have two vocabularies setup, one for the Site Catagories and one for Keywords. Anyone knwo how to attach the the two fileds and update each node with the proper list of tags from the csv?
I have a folder of images, and I'd like to associate on file to one image field per node. Again, when I try and import the image name, I just get errors. Do I need to include the path?

Really appreciate any help getting this the rest of this content in.