Importing XLS/CSV to mysql DB on SC

Scriptcase 9 General
#1

I need to import XLS/CSV files to my mysql databases with Scriptcase/or any suggestion. So I can use for reporting on SC. How can I do that? Is there any options to do?

#2

With PHP you can read a csv file (there are functions for that). Then per record you can use your own sql with sc macro’s to get the data stored in the db. I use a control application with an upload field for this. In this form I have a run button. The user first uploads the file and then hit the runbutton.

#3

https://github.com/PHPOffice/PHPExcel that is one you can use, it can read various formats.

#4

where can I find a step by step procedure to create this type of form?

#5

Create a form with a file upload (document filename if I recall correctly) in it. Then on the upload you can simply run your own php script reading your file. After that you can (if needed) delete the file and the record.

#6

ok, create a form with a file upload is simple, is what comes after that is not simple. For example, the uploaded file, where does it go? I can not specify an absolute path to use in load data file syntax, because it depends on where the application is installed, could be c: … or d: …

#7

Assuming you nicely uploaded the file (and thus saved the record) then it comes in the dir where your documents should be. So if you use typical deployment then this should be in your _lib/file/doc subdirectory of your application.
You can use an event OnValidateSuccess for example to handle the rest. Be aware that it takes a small moment of time for a file to move from the tmp dir to your _lib/file/doc dir.
If you want to specify an absolute path, then in your deployment simply choose advanced deployment create a new deployment setting and fill in your paths. Then deploy with those created settings.

#8

I’m not able to create the necessary form. Where can I found a template already done to import csv file in mysql database?

#9

File upload are in the examples. See http://www.scriptcase.net/scriptcase-samples/ just change the image a normal documetn file name.

#10

??? where? I never found an example of import csv file in mysql database to update an existing table

#11

@rr

The SC IDE can import excel and csv files into DB tables, so SC must have a PHP excel\csv library already installed somewhere.
So i wonder,
[LIST=1]

  • does SC expose programatically any function of that library?
  • If the answer to the above question is no, which PHP library are they using and where it's located, so that I can use it without having to "install" anything. [/LIST]
    • #12

    Actually you need to make a file upload form and write your own import code. This is basic php and specially using mysql you can use this:
    https://dev.mysql.com/doc/refman/5.1/en/load-data.html
    Someone did it before here on the forum so you may find the post back…

    #13

    /sc8/prod/third/phpexcel :slight_smile:

    #14

    Yep, that what I meant by “using it” in my list item 2 above.
    I was just wondering which\where the PHP library used by SC is so that I can use the same one.

    Thanks Giorgio,
    on my CenotOS i see it under /opt/NetMake/v8/wwwroot/scriptcase/prod/third/phpexcel/PHPExcel/