0

I need to import a .csv file into a SQL table.

The input file looks like:

gru,212,BNP10H,bnp10h,1,01,Gestore 01
limGru,2012-09-12,17:21:05,kair103,,,,
limGru,2012-10-19,09:45:03,kair028,,,,
limGru,2012-10-19,09:45:06,kair028,,,,
limGru,2012-10-19,09:45:14,kair028,,,,
gru,207,BNP50E,bnp 50 equity,1,01,Gestore 01
limGru,2012-09-13,11:49:58,kair022,,,,
limGru,2012-10-05,15:20:11,kair048,,,,
limGru,2012-10-17,09:23:14,kair048,,,,

and i would import only the rows that begin with "limGru".

Any idea in how to implement my task?

EDITED: please note that the rows have different number of columns, depending on the first filed ("gru" or "limGru")

EDITED 2: Please also take a look here:

SSIS =- How to process an CSV input file with a variable number of columns based on record type (Header, Detail, Trailer)

Community
  • 1
  • 1
stexcec
  • 1,143
  • 1
  • 18
  • 34

2 Answers2

1

Import all data from the flat file and then use the conditional split transformation. If you're not familiar with the transformation follow the link below: http://technet.microsoft.com/en-us/library/ms137886(v=sql.90).aspx

Debura
  • 499
  • 4
  • 9
  • Thanks, but I have a problem. The input file has a different number of columns, depending on the record type. I must first import the whole file in a single-column table? – stexcec Oct 26 '12 at 14:04
  • Can you edit your original question with a very simple example of this, I'm not entirely sure what you mean and would need a better understanding of your process and what you are trying to achieve for the final output. – Debura Oct 26 '12 at 14:55
  • Hi Debura, what I would to achieve is to import in a SQL table, splitted field by filed, only the rows that begin with "limGru". – stexcec Oct 26 '12 at 17:07
  • OK I understand now, my original suggestion is correct you don't have a different number of columns, each row has the same number of columns but those with 'limgru' have null values for the final four columns, that's fine. You can drop those extra columns from the source at any time using the mappings tab in the transformation options. Almost all transformations have mappings so you can choose which columns you persist in the data flow. The other good thing about using the split is the rows which have only 'gru' can be easily funnelled to another destination if you ever need it :) – Debura Oct 26 '12 at 20:29
  • Hi @Debura, thanks for your comment. But I need the rows with the extra columns. My question is very simple: it's possible at run-time open the flat file and choose row-by-row if import or discard? – stexcec Oct 27 '12 at 08:11
  • OK, so you want to import the four empty columns from the flat file, that's not a problem just keep them in the data flow. And the answer is still yes, using the conditional split you can select only the rows that meet a condition, in this case the first column must contain 'limgru' – Debura Oct 27 '12 at 22:04
0

If limGru is fixed value? If so then you can do Conditional split with condition [NameOfYourFirstColumn] == "limGru"

Piotr Sobiegraj
  • 1,775
  • 16
  • 26