Importing Data

[rochard]

Is there any way that you could store the import order so that new imports can be done without having to line up all the columns. For me, at least, it would be a tremendous time saver.

[bcusick]

Hi Michael,

You can ALWAYS "import" data into servoy without using the import dialog. What you do is:

1) Locate file on disk (prompting the user - or using a pre-defined location);

2) Read the file into a variable;

3) Parse the variable and put each of the pieces of data into the correct field using

Code: Select all

field = value

Just curious - why are you importing through the Servoy interface on a regular basis (i.e. why do you need to store the import orders)?

Bob Cusick

[rochard]

You can ALWAYS "import" data into servoy without using the import dialog. What you do is:

1) Locate file on disk (prompting the user - or using a pre-defined location);

2) Read the file into a variable;

Sorry but I don't understand how you do this! Can you please explain this process in more detail?

3) Parse the variable and put each of the pieces of data into the correct field using

Code: Select all

field = value

How do you parse the variable?

Just curious - why are you importing through the Servoy interface on a regular basis (i.e. why do you need to store the import orders)?

Two reasons; firstly for transferring a lot of records from an existing FM database and having to go through much trial and error to get it all right; secondly to bring in a data file collected from an external source.

[bcusick]

Michael,

Unfortunately, I don't have the time to write a fully functional example - but hopefully the below will point you in the right direction:

Code: Select all

//Show a file open dialog and retrieve file name
var filename = application.showFileOpenDialog();

//Read binary file, shows file selectdialog and reads all data
var rawData = application.readFile(filename);

//count lines
var max = utils.stringPatternCount(rawData, '\n')

//loop and parse
for ( var i = 1 ; i < max ; i++ )
{
   //get the first line up to the carriage return
   
   //do another for loop here to loop through the columns
   for ( var x = 0 ; x < numColumns ; i++ )
   {
      //set temp to first column
      var temp = (PARSE CODE HERE)
      
      //if x = 1 - set a field
      if(x == 1)
      {
         field1 = temp
      } else if(x ==2) {
         field2 = temp
      }
   }
}


You would parse text string just like you do now in FileMaker.

Bob

[rochard]

Many thanks, Bob. That's a great help.

[Harjo]

Hi All,
I still prefer a way of doing this thru the import of Servoy itself.
The dev-guys have improved the import-speed.

The solution as described above is an option, but not a very fast one!.
And, if I am correct, this feauture is planned for the 2.5 release.

[antonio]

Coming back to this, could there be (or is there) a utils.stringMiddleLine (like MiddleWords, LeftWords etc, but it returns an entire line, similar to the FMP function MiddleValues.

[ROCLASI]

Coming back to this, could there be (or is there) a utils.stringMiddleLine (like MiddleWords, LeftWords etc, but it returns an entire line, similar to the FMP function MiddleValues.

There is no MiddleLine function in Servoy. But you could make your own global method for this and call that where you need it.
Or even stick it in a (core) module and load that in all your projects you need so you can reuse it.

Hope this helps.

[antonio]

Thanks, I'll do just that.

It does seem like a useful function to have in the utils.

Tony

[sbutler]

FYI..just read this. I have a funciton in a free plugin which allows you to specify a file, table and field names, and it returns a SQL import statment, which can run with the Raw SQL plugin...if your interested. You can see it at http://it.dnacenter.com/servoy/plugin_ddc_utils.html It basically does what Bob described above, but inside of a plugin.

THe functions is...

Code: Select all

getSQLImportString (String sqlStatement, String tableName, String[] fieldNames, String filePath, String fileDelimiter, int isFirstLineData)

An import script would look like..

Code: Select all

     
     //get the user to select the file
      var myFile = plugins.file.showFileOpenDialog(1);
     //specify the order of the column names
      var columnNames = new Array("field1","field2","field3","field4");
     //get the SQL statement to run
      var sqlString = plugins.DDC_Utils.getSQLImportString("INSERT", controller.getTableName(), columnNames, myFile.getAbsolutePath(), "\\t", 1);"
      //run the SQL statement
      var exec = plugins.rawSQL.executeSQL(controller.getServerName(), controller.getTableName(), sqlString)
      //flush the client's cache
      plugins.rawSQL.flushAllClientsCache(controller.getServerName(), controller.getTableName())
      //show the status of the import after completion
      plugins.dialogs.showInfoDialog( "", "Import Status: " + exec, "ok")

This is the actual Java code behind the plugin...nothing to exciting. If you are using MySQL, you have the option of calling and INSERT, or REPLACE. A replace is like the FileMaker equivalent of updating matching records, and creating non-matching records.

Code: Select all

     public String js_getSQLImportString (String sqlStatement, String tableName, String[] fieldNames, String filePath, String fileDelimiter, int isFirstLineData)
     {   
         String myReturn = "";
         try{
            BufferedReader br = new BufferedReader(new FileReader(filePath));
            String s;
           
            String sql = sqlStatement + " INTO " + tableName + " (";
            for(int i = 0; i< fieldNames.length; i++){
                sql += fieldNames[i] + ",";
            }
            sql = sql.substring(0, sql.length()-1); //remove last ,
            sql += ") VALUES ";

            int j = 0;
            while ((s=br.readLine())!=null){
                j++;
                if(j>1){
                    String line = s.replaceAll(fileDelimiter,"','");
                    sql += "('" + line + "'),";
                }
            }
            br.close();
            sql = sql.substring(0, sql.length()-1); //remove last ,
            sql += ";";
            myReturn = sql;
        }catch(Exception e){
            myReturn = "false: " + e;
        }
        return myReturn;
     }

[Harjo]

cool! did you do allready a performance test? servoy import and your import? is it faster?

Your link does'nt seem to work here.

[sbutler]

The subdomain "it" is pretty new, so the link may not work everywhere yet.

I didn't do any speed test again's Servoy's import, but for what we we're using it for, it was pretty fast. One of the main differences vs bob's code is that it does not set the fields through servoy...instead it returns back a SQL string to be run through the raw SQL plugin...so I would think it would be a bit faster. The only downside is that you have to flush the client's cache since the inserts are done in the raw SQL plugin.

Attached is the plugin..

[swingman]

Hi,
a thought/tip.

One problem I have come across when importing using my own import logic is that Servoy does not like to read very large text files. I had problems with a 22MB merge file from FileMaker. If you are on Mac OS or Linux, you can split the file into 1MB chunks using the following:

Code: Select all

application.executeProgram('split','-b','1m','-a','3',data_file_path,'temp_');


This command is very fast and creates a bunch of files named temp_aaa, temp_aab etc

This does not split by lines, so you need to keep the last line of each chunk and attach it to the front of the next chunk before processing the data with the rawSQL plugin.

[rafig]

Just trying out the

Code: Select all

plugins.DDC_Utils.getSQLImportString(sqlStatement, tableName, String[] fieldNames, filePath, fileDelimiter, number isFirstLineData)


in a method (to try and automate imports) and there seems to be an issue with the last parameter, isFirstLineData.
I assume the options are
0 - for no
1 - for yes
Whatever I set it to, it ignores the first line. So if I only have one record/line to import, it doesn't work. And if I have more, but no header line, it misses the first record/line.

Thanks,
Rafi