1. NOTICE: If you are using Fabrik and update to Joomla 3.10, you will need to update to Fabrik 3.10. And, if you are using Fabrik, do not upgrade to Joomla 4, we do not have a supported version ready for release. More information on a release date coming soon. Also, please note that Fabrik 3.10 will not install on any Joomla sites less than 3.8.
    Dismiss Notice

CSV import

Discussion in 'Community' started by migsby, Aug 13, 2009.

  1. migsby

    migsby New Member

    Level: Standard
    Working on a CSV import of Invoices and Invoice Details.

    A sample of the Invoices.CSV is attached.
    From admin fabrik (Backend), When I select the import button, all the elements are created as they should be. The correct number of records is also created, but all the element data is blank except for one element. I used the demo.fabrikar.com site to test and it happens there as well. This is probably a data issue, but Im not sure. The raw data is attached.

    Also - from the admin fabrik (backend demo site again)- select MIGsby_Testinvoices "View Data" and I dont have the option of importing data here. EDIT: I just fixed this on the demo site by changing the arther to admin)
    My problem on my site is that importing data, even after the table is created, is that fabrik thinks the elements are all new, eventhough Im using the same CSV file.

    Please advise - is this data related or Fabrik related.
     

    Attached Files:

  2. migsby

    migsby New Member

    Level: Standard
    Tried importing the records from the front end and I got a message that was a bit clearer - stating "sorry the following fields are not found in the database"

    Looking at the data, it looks like the CSV import is comparing the column headings from the CSV to the element names in the database. IF THE CSV headings has a space in the column name, it will not find the elements.
    I am going to submit a feature request that when importing after a table is already created, that if the column headings contain a space, fill that space with an underscore "_" the same way the "Import" in the backend does by default.
     
  3. felixkat

    felixkat Senior Member

    Level: Community
  4. migsby

    migsby New Member

    Level: Standard
    Oh man - I just did not get to it after reading the first page of search results on CSV IMPORT . . .
    But yes, really, the import on the backend when creating a new table needs to have the same rules on the column headings as importing into the table directly...
     
  5. migsby

    migsby New Member

    Level: Standard
    After reading your reference - they are now already inserting the _ to replace spaces. Cool

    However, other characters that are not allowed seem to be being replaced with nothing. For example, "Mfg/Dist" will import as mfgdist when first creating the table. After the table is created, and an import is attempted using the same structure as the original (cause the data changes everyday with adds/edits etc) from both front end and backend, fabrik cannot link the two together becuase it is searching for Mfg/Dist, while it was originally saved as mfgdist. I dont care which way to do it, just as long as the import rules are the same
     
  6. felixkat

    felixkat Senior Member

    Level: Community
    There are a few rewrite rules throughout Fabrik. These are being implemented as we have had a few threads raised on various issues revolving around "naming conventions".

    It has already been implemented in places such as file upload, to remove spaces, hashes, hyphens etc.

    Data should really be sanitized before it's imported but this isn't something that's common knowledge to people who are new to databases. It certainly caught me out.

    ... but yes some form of clean up could be implemented in the CSV import function.


    Maybe you could close this thread and bump the other link.

    http://fabrikar.com/forums/showthread.php?p=61776

    It will probably still be low priority but something possibly to implement before the final release.
     
  7. migsby

    migsby New Member

    Level: Standard
    OK: Using the sample file in the first post I
    From the Back End:
    1. Import a table:
    Settings Are:
    a. site database
    b. file above.csv
    c. IV_test2
    d. IV_test2
    e. No (to create PK)
    I pick "InvoiceNumber" as the PK
    I change the date fields (Invoice Date, Purchase Date, FFP Date) to Type DATE.

    2. The file is UPLOADED and 32 records are created, HOWEVER, on data for LOC (only field with NO SPACES) is filled with data.

    <<<<NEXT>>>>
    3. From "View Data" link on my new table I
    "Import from CSV"
    a. Select SAMVE FILE as ABOVE.
    b. keep all defaults (no checks, not changing field or text delimiters)
    c. system states "New Elements Fould"
    This is most likely becuase there are SPACES in the column field headers.

    My suggestions: (in order of importance)
    1. Ensure that when importing AFTER a table has been created with a CSV import, to compare the column headers AS IF IT WAS creating the headers IE: replace ALL invalid characters with a "_" BEFORE comparing the two files,
    2. Ensure that when creating a table via IMPORTING CSV that ALL (ALL) invalid characters are replaced with a "_" IE: I had a "/" in a field header and it was replaced with a "" (NO space)
     
  8. rob

    rob Administrator Staff Member

    Level: Community
    think this is sorted now - can you update from the SVN and check if I got it right please?

    Thanks
    Rob
     
  9. migsby

    migsby New Member

    Level: Standard
    I updated to the latest SVN and all the data imported - HOWEVER, there is still an issue with the PK - I really hate to be a squeeky wheel here . . .
    Using the sample file provided: Invoice Number will be the PK. InvoiceNumber CONTAINS data from the "Exporting system" (not in anyway controled by me). During the Table Creation, if I pick InvoiceNumber to be the PK, then AUTOINC INT(6) is Assigned and all the data from the Import is REPLACED with an AUTOINC INT(6).

    The issue is this: One can only have ONE AUTOINC field in a table, therefore "picking a field" to be the PK in this case is not correct.
    To solve MY Issue: just let Fabrik create the PK, and reassign in the backend later.
     
  10. rob

    rob Administrator Staff Member

    Level: Community
    fair point, the issue is more about the UI than other things as most people will start to get confused when you ask them to specify the field type for the pk. Hence we just presume that its going to be an autoincrementing key.
     

Share This Page