SAP SQL Anywhere

Question

Or, in another form, How to achieve the same type of thing that Load Into Table ... (ColA,ColB,filler(),ColC) would do and still fire triggers.

Say you have a table:

CREATE TABLE AAmytable (
TID INTEGER NULL,
TName VARCHAR(40) NULL,
TAmount DECIMAL(10,4) NULL);

You have a CSV file import.csv:

TID,TTname,Tdesc,Tamount,TRep,
2,Morning,Second Trans,20,Matt,
3,Noon,Third Trans,30,Adam,
4,MidAft,Fourth Trans,40,Adam, 
5,Closing,Fifth Trans,50,Matt\

I have tried using the input, but cannot figure a way for it to skip a column.

I have tried using the load table, with filler(), and this correctly skips the column, but does not fire the triggers.

Moving the data into a temporary table and adjusting it from there is probably the way we will go, but I wanted to make sure I was not missing anything.

Any help would be appreciated.

Accepted Answer

4

Try using OPENSTRING.

For your example, use something like:

insert into AAmytable( TID, TName, TAmount )
select Tid, Tname, Tamount
  from openstring( FILE 'import.csv' )
  with ( Tid integer,
         Tname varchar(40),
         Tdesc varchar(100),
         Tamount decimal(10,4),
         TRep varchar(100) )
  option ( delimited by ',' )
  as imported

See the FROM clause in the docs for more info.

permanent link

answered 19 Apr '11, 15:24

Mark Culp
24.9k●10●141●297
accept rate: 41%

edited 20 Apr '11, 15:09

Worked great. Thanks Mark. I did have to add an "As Imported" alias at the end, otherwise it gave me an error at end of line, but that does exactly what I need.

(20 Apr '11, 14:58) Siger Matt

Replies hidden

Glad to hear it worked for you, and reporting the error - I've corrected the statement.

(20 Apr '11, 15:11) Mark Culp

flat view

Answer 2

3

Mark's suggestion for using OPENSTRING is probably the way to go, but if you want an alternate suggestion, you could also consider using a proxy table. Assuming you are on Windows, the Microsoft Access Text Driver is a decent ODBC driver for accessing CSV files. You could then map a proxy table to your CSV file and use it like any other base table.

For some additional information/discussion have a look here

permanent link

answered 20 Apr '11, 07:40

Karim Khamis
5.7k●5●38●70
accept rate: 40%

edited 20 Apr '11, 07:52

Thanks Karim, I will give that a try next time.

(20 Apr '11, 14:58) Siger Matt

flat view

SAP SQL Anywhere

Ignore a column in an imported file

Follow this question

Related questions