How to import to Sybase

[Westy]

I have a simple three column Sybase table to which I need to import 78,000 records from a file in csv format. When attempting to do this within Servoy the import slows down dramatically when it reaches about 50,000 records and then quits completely at about 53,000 records.

How can imports be done directly into a Sybase table? I have opened Sybase Central and connected to the table that I want to bring the data into, however, I cannot find any way to import. When I enter "import" into the Help search screen it comes up empty.

If anyone can help explain the proper way to do imports of large record sets, I would greatly appreciate it.

Dean

[david]

You will need to use InteractiveSQL, another program in your Sybase directory. In Sybase Central, right click on the table you want to import into and choose "View Data in Interactive SQL".

Under the Data menu there is an import wizard. 78,000 records should take only a few seconds to load. Also a good way to export records (with the export wizard in the same place) as you can run a query and then export the returned data set to a file.

- David

[david]

One more thing: I can't remember but after importing you may have to run a commit statement (just type "commit;" in the SQL statements window and hit the run button).

- David

[Westy]

I did the import in 15 seconds. Amazingly fast. That's really great! Thanks for the tip.

There is still one other major problem though, the multi-line fields that had carriage returns only bring in the first line of each multi-line field. In other words, everything within a field following the first carriage return character is dropped. I am using the csv format in the source file.

I first tried substituting a line break for each carriage return in the source file prior to importing and that did not work. I then tried substituting a paragraph character for each carriage return, but that also did not work.

Using some odd characters and then looping through to replace them later in Servoy is not an option because loops appear not to make it all the way through large record sets (ie. I get a Java memory overrun error).

How can multi-line text fields be imported, when the import is being done through Sybase Central?

[Westy]

After some trial and error it appears that replacing the carriage return characters that I had been using with \x0A is the answer. Note that the character before the A is a zero.

Dean

[ve3cnu]

Also have a look at the LOAD TABLE statement

--------------------------------------------------------------------------------

Description
Use this statement to import bulk data into a database table from an external ASCII-format file

[faheemhameed]

Can I incorporate this import/export at server level using servoy interface in a servoy client.

The steps could be like the following:

Upload the tab seperated file to the server using an ftp bean

Use any stored procedure which does the import at server level (not sure how)

Then refresh the foundset in the servoy client

Is this doable?

Otherwise how could I import a large foundset through servoy client??

Thanks
Hameed