Import - General Usage

1. What should column headers be, database field names, aliases or labels?- Database Field Names
   
2. Should / can a csv file be qualified by anything (' or ")?
   
3. What is the date format dependent on, mysql, phprunner, other? (e.g., mysql date w/phprunner date field formatted mm/dd/yyyy vs. mysql varchar w/phprunner date field)
   
4. Are phprunner 'Required fields' required? Do all columns need to be in the import file?
   
5. What are the requirements on an xls file? 2003 compatible or xlsx support?
   
6. Are there any plans for updated error logging? Error in the line: "....." is helpful, but less helpful when there are 25 columns
   Thanks for this great information!!
   

If you prefill a table and export it as a csv in phprunner, you can see the format. Thats what we did.

Hi,
Please see my comments below:

1. What should column headers be, database field names, aliases or labels? - Database Field Names or field labels
   
2. Should / can a csv file be qualified by anything (' or ")? - You can use one type of quote in your CSV file. To change it open generated ..._import.php file and edit following line:
   

   $fields[$j] = trim(str_replace('"', "", $fields[$j]));
   

3) What is the date format dependent on, mysql, phprunner, other? (e.g., mysql date w/phprunner date field formatted mm/dd/yyyy vs. mysql varchar w/phprunner date field)A mysql date field w/phprunner date field formatted mm/dd/yyyy can be imported in the standard mysql format yyyy-mm-dd. Times can be imported in the standard mysql hh:mm:ss format (24 hr)

You can also change date or time format of imported values using "Import page: Before Insert" event on the Events tab.

4) Are phprunner 'Required fields' required? No. Do all columns need to be in the import file? No, only columns which are set up as required in the database directly.

5) What are the requirements on an xls file? 2003 compatible or xlsx support? - PHPRunner supports 2003 compatible Excel files only.

6) Are there any plans for updated error logging? Error in the line: "....." is helpful, but less helpful when there are 25 columns. You can expand error message and see full SQL query. To troubleshoot error run this query in the database.

Co-incidental, I'm also trying to get import to work today <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=49655&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
There are 6 fields in my table: field a to field_f.

This table is a detail table, with field a as the master linking key (no nulls).

My primary key is concatentated, field a + field c (note: PHPR supports up to 3 concatentated key fields)

All works fine with usual lists and adds.
But on Import I'm getting an error message for every line in my xls (2003) import file.
Error:

SQL query: insert into my_table (field c,field_b,field_d,field_f) values (data_c, 'data_a', data_b, 'data_d', 'data_e', 'data_f');
DB error: Column count doesn't match value count at row 1;
The insert is trying to insert 6 fields.

But the field list for the insert only contains 4 fields instead of the 6 it should (excludes field a and field_e)
The order of the fields in the SQL query does not match the order of any fields in the Fields Tab in PHPR, although there is not a tab specifically for the import page. There is a tab for the export page, however I have no export page.
Not sure if its relevant, but the excel I'm importing from only contains one field, that's field c (part of the key). The first row of the excel table contains label "field c". The event import: before_insert_record uses session variables and looks-up data in other tables and gets the data for all 6 fields. The $values[] array is populated accordingly includes $rawvalues["field c"].
$values["field a"] = $_SESSION["field a"];

$values["field_b"] = $_SESSION["field_b"];

$values["field c"] = $rawvalues["field c"];

$values["field_d"] = $data["field_d"];

$values["field_e"] = $data["field_e"];

$values["field_f"] = $data["field_f"];
Its apparent that its reading the excel ok and grabbing the data. All the data is getting into the query ok. But the SQL query is getting mangled (dropping a & e). Incidentally note: Fields a & c names have embedded space, but its quoted correctly.
So my first question is, where does the import get the fields names to use and order from?

And second, why is there a mismatch in the SQL?
[Update] As an experiment, I have fully populated the excel table with all 6 columns corresponding to my field names, with dummy data (except for field c). The import works. Only throws an error when there is a duplicate key. Since I can't add IGNORE to the insert statement, I must first lookup the key and if hit, then I return a false. When I get time I'll continue experiments to see how much of a subset I can get away with.
If it turns out I am stuck with having to define all the fields needed in the excel, I'll have to split the import into two stages with a dummy temp table with only one field defined, and then a post import event which takes that temp table and updates the real table. And solve the concurrency issue...

Hi,
Unfortunately, it is not possible to add additional fields to imported ones in PHPRunner.

The only thing I can advise you is to add that fields to the list of imported fields.

Correct me if I'm wrong, but are you saying:

• That its the columns headers in excel table that defines the arrays values[] and rawvalues[], and also the generation of the SQL insert statement used by the import event function?
  
• Field names (columns headers) in the excel table, if included, must match the field names in the database table, otherwise there will be runtime errors?
  
• You better at least include all the primary key and NOT NULL fields in the excel table, as there can be no default values assigned, otherwise there will be runtime errors?
  Any other import usage guidelines?

Hi,
please see my comments below:

• That its the columns headers in excel table that defines the arrays values[] and rawvalues[], and also the generation of the SQL insert statement used by the import event function?
  
  All fields and values from $values array from BeforeInsert event are imported.
  
• Field names (columns headers) in the excel table, if included, must match the field names in the database table, otherwise there will be runtime errors?
  
  Yes.
  
• You better at least include all the primary key and NOT NULL fields in the excel table, as there can be no default values assigned, otherwise there will be runtime errors?
  
  Yes. You need to include all not null fields and primary keys (except auto-increment fields and fields with default value) to your imported file.