# Automating imports **URL:** \ **Category:** Classic Servoy\ **Created:** [August 28, 2007, 10:54pm UTC](https://forum.servoy.com/t/automating-imports/8071 "2007-08-28T22:54:08Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Westy](https://avatars.discourse-cdn.com/v4/letter/w/9de053/32.png) [@Westy](https://forum.servoy.com/u/Westy)\ **Post date:** [August 28, 2007, 10:54pm UTC](https://forum.servoy.com/t/automating-imports/8071/1 "2007-08-28T22:54:08Z")
For those using Sybase ASA with Windows, the dbisql command-line utility makes it possible to automate imports and other actions with SQL statements without having to open and view Sybase Central. For example, when the following bat file is executed it will automatically import records from a csv file into the contacts table of the Servoy sample udm database: ```auto dbisql -c "eng=servoy_repository;dbn=udm;uid=dba;pwd=sql" -host localhost c:\temp\import_contacts_data.sql ``` It calls the following sql text file: ```auto INPUT INTO "DBA"."contacts" FROM c:\\temp\\contacts_data.csv FORMAT ASCII DELIMITED BY ',' (name_first,name_last) ``` Executing the following bat file deletes all the records: ```auto dbisql -c "eng=servoy_repository;dbn=udm;uid=dba;pwd=sql" -host localhost c:\temp\delete_contacts_data.sql ``` It calls the following sql file: ```auto DELETE "DBA"."contacts" WHERE contact_id > 0 ``` We wanted our users to be able to import records while at the host computer by simply double-clicking a bat file. The dbisql utility makes this possible. Note: In the above example the contact\_id needs to be set to autoincrement within Sybase Central. More information on the dbisql command-line utility can be found at: [http://www.ianywhere.com/developer/prod … 000590.htm](http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbdaen9/00000590.htm) Dean Westover ---
**Author:** ![Jan\_Aleman](https://avatars.discourse-cdn.com/v4/letter/j/4af34b/32.png) [@Jan\_Aleman](https://forum.servoy.com/u/Jan_Aleman)\ **Post date:** [August 29, 2007, 7:50am UTC](https://forum.servoy.com/t/automating-imports/8071/2 "2007-08-29T07:50:28Z")
Excellent tip! You could probably also execute that sql code without isql straight with the rawsql plugin… ---
**Author:** ![IT2Be](https://avatars.discourse-cdn.com/v4/letter/i/dc4da7/32.png) [@IT2Be](https://forum.servoy.com/u/IT2Be)\ **Post date:** [August 29, 2007, 8:25am UTC](https://forum.servoy.com/t/automating-imports/8071/3 "2007-08-29T08:25:16Z")
Or you could do that without creating a server for servoy using sql through the data plug-in. ---
**Author:** ![Westy](https://avatars.discourse-cdn.com/v4/letter/w/9de053/32.png) [@Westy](https://forum.servoy.com/u/Westy)\ **Post date:** [August 29, 2007, 4:00pm UTC](https://forum.servoy.com/t/automating-imports/8071/4 "2007-08-29T16:00:03Z")
Same approach works for automating exports, with the bat file looking like this: ```auto dbisql -c "eng=servoy_repository;dbn=udm;uid=dba;pwd=sql" -host localhost C:\\temp\\export_contacts_data.sql ``` and the sql file looking like this: ```auto SELECT * FROM contacts; OUTPUT TO c:\\temp\\exported_contacts_data.csv QUOTE '"' ALL FORMAT ASCII ``` Remove the “ALL” if you do not want quotes around numbers in the csv file. Dean ---
**Author:** ![Robert\_Huber](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@Robert\_Huber](https://forum.servoy.com/u/Robert_Huber)\ **Post date:** [August 30, 2007, 8:53am UTC](https://forum.servoy.com/t/automating-imports/8071/5 "2007-08-30T08:53:24Z")
Yes, we use this method to import data into the db. Regards, Robert > jaleman: > Excellent tip! > > You could probably also execute that sql code without isql straight with the rawsql plugin… ---
**Author:** ![Westy](https://avatars.discourse-cdn.com/v4/letter/w/9de053/32.png) [@Westy](https://forum.servoy.com/u/Westy)\ **Post date:** [August 30, 2007, 1:34pm UTC](https://forum.servoy.com/t/automating-imports/8071/6 "2007-08-30T13:34:09Z")
> jaleman: > Excellent tip! > > You could probably also execute that sql code without isql straight with the rawsql plugin… I have not been able to do this with the rawsql plugin. I suspect that I am not using the proper syntax. If someone can provide an example of syntax that will work with the rawsql plugin based on the above import example it would be greatly appreciated. Dean ---
**Author:** ![Robert\_Huber](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@Robert\_Huber](https://forum.servoy.com/u/Robert_Huber)\ **Post date:** [August 30, 2007, 2:18pm UTC](https://forum.servoy.com/t/automating-imports/8071/7 "2007-08-30T14:18:50Z")
Hello Dean Following an example including the file read in. In this example the SQL command is an “insert into …” statement, change it to what you need. It’s in var sql1. I also use Java to read the file as buffered character-input stream as it can be a big file and you are limited with the Servoy JavaScript plugins.file.readTXTFile(dataFile) read as this cmd tries to read in everything into the memory. But you can try, the code is the 3 lines (comments in the code). Best regards, Robert > Code: > /\* > Title: Inserts data into a table defined by the filename. > > Author: Birgit Rieder, Robert Huber > Created: 28.06.2007 > Modified: - > > Arguments: - > Returns: - > Notes: - > \*/ > > var dataFile = plugins.file.showFileOpenDialog(); > if (!dataFile) return 0; > > var successes = 0; > var failedRows = 0; > var failedString = “”; > > var filename = dataFile.getName(); > var tablename = (filename.split(“.”))[0]; > > //var fileContent = plugins.file.readTXTFile(dataFile); > //var fileRows = fileContent.split(“\n”); > //var columns = fileRows[0]; > > // Open input file and read a buffered character-input stream > var fr = new Packages.java.io.FileReader(dataFile); > var br = new Packages.java.io.BufferedReader(fr); > > globals.line = br.readLine(); > if ( globals.line == null ) > { > application.output(“File " + dataFile + " is empty”); > return false; > } > > var lineLength = globals.line.length; > > // Set date format to ddmmyyyy > var statement1 = “set option date\_order = ‘dmy’”; > var success = plugins.rawSQL.executeSQL(“TrackIT”, tablename, statement1); > > var sql1 = “insert into " + tablename + " (” + globals.line + “) values (”; > var sql2 = “);”; > > var i = 1; // Initialize row counter > while ( globals.line != null ) > { > globals.line = br.readLine(); > if ( globals.line ) > { > var statement = sql1 + globals.line + sql2; > var success = plugins.rawSQL.executeSQL(“TrackIT”, tablename, statement); > if ( success ) > { > successes += 1; > } > else > { > failedRows += 1; > if (failedRows \< 10) > { > var rowNumber = i + 1; > failedString += ’ ’ + rowNumber; > } > else if (failedRows == 10) > { > failedString += ‘…’; > } > } > } > i++ > } > > br.close(); > > plugins.dialogs.showInfoDialog( > “Import finished”, > "rows inserted: " + successes + “, rows failed: " + failedRows + " (” + failedString + “)”, > “OK”); > Westy: > > > jaleman: > > Excellent tip! > > > > You could probably also execute that sql code without isql straight with the rawsql plugin… > > I have not been able to do this with the rawsql plugin. I suspect that I am not using the proper syntax. If someone can provide an example of syntax that will work with the rawsql plugin based on the above import example it would be greatly appreciated. > > Dean ---
**Author:** ![Harjo](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/harjo/32/4873_2.png) [@Harjo](https://forum.servoy.com/u/Harjo)\ **Post date:** [August 30, 2007, 5:58pm UTC](https://forum.servoy.com/t/automating-imports/8071/8 "2007-08-30T17:58:06Z")
I like the rawSQL tip, thanks! because this one also works in a client. The code of Westy, only works on the db-server itself, or else you have to open a port also, to access the db outside, your server, which brings security risks also… ---
**Author:** ![Westy](https://avatars.discourse-cdn.com/v4/letter/w/9de053/32.png) [@Westy](https://forum.servoy.com/u/Westy)\ **Post date:** [August 30, 2007, 7:09pm UTC](https://forum.servoy.com/t/automating-imports/8071/9 "2007-08-30T19:09:29Z")
Yes, I agree. Thank you Robert! Dean ---
**Author:** ![Robert\_Huber](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@Robert\_Huber](https://forum.servoy.com/u/Robert_Huber)\ **Post date:** [August 30, 2007, 7:15pm UTC](https://forum.servoy.com/t/automating-imports/8071/10 "2007-08-30T19:15:37Z")
Glad I could help, did you already try it? Does it work in your environment? Best regards, Robert > Westy: > Yes, I agree. Thank you Robert! > > Dean ---
**Author:** ![Westy](https://avatars.discourse-cdn.com/v4/letter/w/9de053/32.png) [@Westy](https://forum.servoy.com/u/Westy)\ **Post date:** [August 30, 2007, 7:29pm UTC](https://forum.servoy.com/t/automating-imports/8071/11 "2007-08-30T19:29:33Z")
We’ve tried it, but do not have it working yet. Could you explain: ```auto // Open input file and read a buffered character-input stream var fr = new Packages.java.io.FileReader(dataFile); var br = new Packages.java.io.BufferedReader(fr); globals.line = br.readLine(); ``` ---
**Author:** ![IT2Be](https://avatars.discourse-cdn.com/v4/letter/i/dc4da7/32.png) [@IT2Be](https://forum.servoy.com/u/IT2Be)\ **Post date:** [August 31, 2007, 12:41am UTC](https://forum.servoy.com/t/automating-imports/8071/12 "2007-08-31T00:41:43Z")
Hi Dean, The mentioned methods are pure and plain java. Basically, what happens there, is that a file is opened and streamed into Servoy (line per line). ---
**Author:** ![Robert\_Huber](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@Robert\_Huber](https://forum.servoy.com/u/Robert_Huber)\ **Post date:** [August 31, 2007, 5:49am UTC](https://forum.servoy.com/t/automating-imports/8071/13 "2007-08-31T05:49:46Z")
Thanks you Marcel, you are quicker than me in replying ![:-)]( "Smile") As Marcel said, instead of reading the whole file into the memory, what the readTXTFile does and to memory overflow leads (as there is no possibility to assign more memory to this cmd or at least nobody could tell me how to), the file is read line by line. Therefor the fr (file reference, i. e. pointer to the file and buffer reference). The global.line just holds a line at a given time. It MUST be global! Hope you get a step further. Regards, Robert > IT2Be: > Hi Dean, > > The mentioned methods are pure and plain java. > Basically, what happens there, is that a file is opened and streamed into Servoy (line per line). ---
**Author:** ![Harjo](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/harjo/32/4873_2.png) [@Harjo](https://forum.servoy.com/u/Harjo)\ **Post date:** [August 31, 2007, 6:35am UTC](https://forum.servoy.com/t/automating-imports/8071/14 "2007-08-31T06:35:45Z")
why must it be a global? I’m using a variable, which works also. ---
**Author:** ![Robert\_Huber](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@Robert\_Huber](https://forum.servoy.com/u/Robert_Huber)\ **Post date:** [August 31, 2007, 7:53am UTC](https://forum.servoy.com/t/automating-imports/8071/15 "2007-08-31T07:53:41Z")
As far as I remember, it sais so somewhere (although I can’t find the reference at the moment, I may be wrong, I think it’s on SUN’s Java Doc site). But if you say it works with a local variable, that would be even better. Best regards, Robert > HJK: > why must it be a global? > I’m using a variable, which works also. ---
**Author:** ![pbakker](https://avatars.discourse-cdn.com/v4/letter/p/a4c791/32.png) [@pbakker](https://forum.servoy.com/u/pbakker)\ **Post date:** [August 31, 2007, 11:37am UTC](https://forum.servoy.com/t/automating-imports/8071/16 "2007-08-31T11:37:12Z")
From a javascript point of view, a variable is just a variable, whether it’s a global, a local or a variable generated on the fly… Paul ---
**Author:** ![Westy](https://avatars.discourse-cdn.com/v4/letter/w/9de053/32.png) [@Westy](https://forum.servoy.com/u/Westy)\ **Post date:** [August 31, 2007, 1:41pm UTC](https://forum.servoy.com/t/automating-imports/8071/17 "2007-08-31T13:41:04Z")
> Robert Huber: > …Does it work in your environment? Still not working here. I am probably missing something that should be obvious. Is the following correct? To bring comma separated values into a “contacts” table, name the source file “contacts.csv”. Then replace “TrackIT” with our database name. Then the resulting SQL statement should end up looking like: insert into contacts (“John”,“Doe”) values (“John”,“Doe”); and that should work? Dean ---
**Author:** ![Jan\_Aleman](https://avatars.discourse-cdn.com/v4/letter/j/4af34b/32.png) [@Jan\_Aleman](https://forum.servoy.com/u/Jan_Aleman)\ **Post date:** [August 31, 2007, 1:45pm UTC](https://forum.servoy.com/t/automating-imports/8071/18 "2007-08-31T13:45:20Z")
In the first bit you have to spec the column names, not the values insert into contacts (first,last) values (“John”,“Doe”); ---
**Author:** ![Westy](https://avatars.discourse-cdn.com/v4/letter/w/9de053/32.png) [@Westy](https://forum.servoy.com/u/Westy)\ **Post date:** [August 31, 2007, 2:10pm UTC](https://forum.servoy.com/t/automating-imports/8071/19 "2007-08-31T14:10:04Z")
Okay, so the first line of the csv file must contain the column headers. That makes sense. I will go back and try again. ---
**Author:** ![Westy](https://avatars.discourse-cdn.com/v4/letter/w/9de053/32.png) [@Westy](https://forum.servoy.com/u/Westy)\ **Post date:** [August 31, 2007, 4:39pm UTC](https://forum.servoy.com/t/automating-imports/8071/20 "2007-08-31T16:39:19Z")
I have it working now. Sybase ASA appears to require single quotes around the values, so I inserted a line to replace the quotes: ```auto utils.stringReplace(globals.line,'"',"'") ``` and added a flushAllClientsCache at the end to redisplay the results within Servoy. For a speed test I imported 111,000 records with a first name and a last name column. The import took about five minutes. During the import the javaw.exe and dbsrv9.exe CPU processes were in the 48 to 52 range. What is a good way to give the user feedback on the progress of the import without slowing the import down too much? Thank you again Robert for providing this import solution! Dean [Next page](https://forum.servoy.com/t/automating-imports/8071.md?page=2)