cancel
Showing results for 
Search instead for 
Did you mean: 

Ignore a column in an imported file

3,006

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 Solutions (1)

Accepted Solutions (1)

MarkCulp
Participant

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.

0 Kudos

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.

MarkCulp
Participant
0 Kudos

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

Answers (1)

Answers (1)

Former Member

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

0 Kudos

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