Loading data from .csv with blank cells

Question

Post reply

Loading data from .csv with blank cells

Amy.G

SSCrazy

Points: 2305
More actions
March 28, 2011 at 9:20 am

#147764

I have a .csv file downloaded from the federal government that needs to be loaded into SQL server. Using SSIS, a very strange thing is happening. The program is filling in cells that are blank with data from another column. Data looks like this:
title area monthemployment
MinnesotaST27000012711426
MinnesotaST27000022728138
MinnesotaST2700003
MinnesotaST2700004
MinnesotaST2700005
As you can see, the employment numbers for the months after February are not in yet, and therefore blank. Once the table was loaded into SQL, for some reason, the empty employment cells were filled in with the numbers from the month column, so it looked like this:
title area monthemployment
MinnesotaST27000012711426
MinnesotaST27000022728138
MinnesotaST27000033
MinnesotaST27000044
MinnesotaST27000055
Once in SQL, it is easy enough to delete these rows, but then the queries inside the package need to be run manually, which goes against the whole purpose of having an SSIS script to load the data.
Does anyone know what is happening?
Thank you, Amy

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

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

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

What does the actual file look like for those 5 rows you gave an example of?

--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)

Amy.G SSCrazy Points: 2305 More actions · Answer 2

Jeff Moden (3/28/2011)
What does the actual file look like for those 5 rows you gave an example of?

That is a copy and paste of the actual file above. Where there are blank cells in the csv file, the month column is duplicated and filled into the blank cells after the data load.

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

Amy.G (3/30/2011)
Jeff Moden (3/28/2011)
What does the actual file look like for those 5 rows you gave an example of?
That is a copy and paste of the actual file above. Where there are blank cells in the csv file, the month column is duplicated and filled into the blank cells after the data load.

Unfortunately, the copy'n'paste doesn't show me the missing delimiters at a hex level. Or is the file "fixed width fields"?

--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)

Amy.G SSCrazy Points: 2305 More actions · Answer 4

I opened the file in notepad to see the delimiters. Here are the top 5 rows:

title,area,month,lausemp,lausunem,lauslf,lausrate,laus_sw,lauscalc,curyear

Minnesota,ST270000,1,2711426,219041,2930467,7.5,0,7.70E+25,2011

Minnesota,ST270000,2,2728138,217404,2945542,7.4,0,7.70E+25,2011

Minnesota,ST270000,3,,,,,0,7.70E+25,2011

Minnesota,ST270000,4,,,,,0,7.70E+25,2011

Minnesota,ST270000,5,,,,,0,7.70E+25,2011

I hope this is what you were looking for.

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

Amy.G (3/30/2011)
I opened the file in notepad to see the delimiters. Here are the top 5 rows:
title,area,month,lausemp,lausunem,lauslf,lausrate,laus_sw,lauscalc,curyear
Minnesota,ST270000,1,2711426,219041,2930467,7.5,0,7.70E+25,2011
Minnesota,ST270000,2,2728138,217404,2945542,7.4,0,7.70E+25,2011
Minnesota,ST270000,3,,,,,0,7.70E+25,2011
Minnesota,ST270000,4,,,,,0,7.70E+25,2011
Minnesota,ST270000,5,,,,,0,7.70E+25,2011
I hope this is what you were looking for.

It is, indeed! Especially since the delimiters no longer appear to be spaces. 🙂

I don't know why SSIS is having a problem with the load as you say. It might even be something your package is doing but don't know and I'm not sure anyone else would be able to help with this problem without seeing all the stuff in your package or at least the node that does this load.

I will tell you, though, that BULK INSERT would have no problem loading this file with or without the missing months.

--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)