The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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.1k486493
accept rate: 13%


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
22.5k9129264
accept rate: 40%

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.6k53870
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:

×14
×5
×5

question asked: 19 Apr '11, 14:12

question was seen: 1,036 times

last updated: 20 Apr '11, 15:11