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.

asked 19 Apr '11, 14:12

Siger%20Matt's gravatar image

Siger Matt
3.3k5672101
accept rate: 15%


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%20Culp's gravatar image

Mark Culp
24.9k10139297
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

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%20Khamis's gravatar image

Karim Khamis
5.7k53870
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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×18
×7
×6

question asked: 19 Apr '11, 14:12

question was seen: 2,587 times

last updated: 20 Apr '11, 15:11