Import data from csv file

Question

Import data from csv file

Anita Lakhani

SSC Veteran

Points: 229
More actions
June 29, 2017 at 10:38 am

#402230

I'm trying to import data from a csv file and having difficulty. The first 34 rows of the file have 2 columns of data I do NOT want. Rows 35 onward have 10 columns of data I do want. I've tried using SSIS and having the package start at column 35 but although it does start at row 35, it only recognizes 2 columns of data. Is there an easier way to do this? I'm attaching a sample pic of the file for reference. Thanks for any help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Two options.
1.) Take the unwanted rows out of the spreadsheet.
2.) Let the SSIS package bring in all the columns and rows, but add a row number to each row using a script task, and later in the package use a Conditional Split that will only accept row numbers > 34. You can always not include columns in mapping to your destination table, but that's always going to be for ALL ROWS, so keep that in mind. However, once the row number is part of the data stream, a Derived Column transformation can use the row number as a way to decide if a value for a given field should be used or not for a new field you add to the data stream.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

Eirikur Eiriksson

SSC Guru

Points: 182912

June 29, 2017 at 11:04 am

#1948792

Is the dara rfc-4180 compliant?
😎

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

You could try BULK INSERT using the FIRSTROW qualifier. Looks promising, but I've not tried using it myself.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Anita Lakhani - Thursday, June 29, 2017 10:38 AM
I'm trying to import data from a csv file and having difficulty. The first 34 rows of the file have 2 columns of data I do NOT want. Rows 35 onward have 10 columns of data I do want. I've tried using SSIS and having the package start at column 35 but although it does start at row 35, it only recognizes 2 columns of data. Is there an easier way to do this? I'm attaching a sample pic of the file for reference. Thanks for any help.

This is what "staging tables" are for. Import everything, validate, include and exclude, insert into final resting area. Never import into the final table directly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)