We help IT Professionals succeed at work.

creating a SSIS package that runs a comma delimited file. Issue with field spliting due to space.

Medium Priority
778 Views
Last Modified: 2012-05-12
I am tryng to create an automated process for a user. I have created a sql script and I have used the SSIS tool to create a package which runs and appends the data in the selected .csv file.
My isssue is that when the address or name has 3 spaces in a row in seperates the information and it is not in line anymore. Please see my code below.. there might just be something easy that I can do to keep these fields together somehow.

Your help is greatly appeciated
select DISTINCT 
       "LocationID" = 
				 CASE WHEN category = '6601' Then '10049636' 
				      WHEN category = '6650' Then '10049636' 
		     	      WHEN category = '8412' Then '10013190' 
				 END,
		'RSLR' AS 'Reporting Partner Type',
		CustomerName AS 'End Customer Ship To Name',
		InvoiceShipToAddress AS 'End Customer Ship To Address 1',
		' ' AS 'End Customer Ship To Address 2',
		CustomerCity  As 'End Customer Ship To City',
		CustomerState  As 'End Customer Ship To State',
		CustomerZip  As 'End Customer Ship to Zip',
		' ' AS 'End Customer Ship To Phone',
	    ' ' AS 'Bill To Name',
		' ' AS 'Bill To Address 1',
		' ' AS 'Bill To Address 2',
		' ' AS 'Bill To City',
		' ' AS 'Bill To State/Province',
		' ' AS 'Bill To Postal Cd',
		'39' AS 'Quantity Qualifier',
		'Quantity' = CAST(qty AS varchar(30)),
		Item AS 'HP Part Number',
		'3' AS 'Data Type Qualifier',
	   'Invoice Date' = Convert(char(10), Date, 101),
	    ' ' AS 'Reseller Assigned Part Number',
	    ' ' AS 'Unit Price', 
	    'S' AS 'Transaction Type',
	    'Invoice Number' = CAST(InvoiceID AS varchar(15)),
	    ' ' AS 'UPC Code',
	    ' ' AS 'Big Deal_Contract Number',
	    ' ' AS 'Serial Number' 
from vw_jbarman_HP_WeeklySales 
where category IN ('6650', '6601', '8412')

Open in new window

Comment
Watch Question

Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
sorry, do not understand what is the issue here.

Which field is having the issue ? what is the output you are getting currently for that field and what is the expected correct output  ?

Author

Commented:
The issue I am having is with the address... In the address there are sometime 3 spaces after St so for example the address will be 10112 East 59th St    Suite 223

So when the program or analzer that is looking at this see 3 spaces it transfers over to another cell when it is doing the export to excel. (this is a csv file) is there anyway to make it so that I can bracket in the fields as to tell the analyzer to keep this field together until the next field in the cell for the Excel transfer? Make sense?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Did you try using a replace? Replace("  ", " ")

Author

Commented:
I sure don't mean to seem dumb.. but could you show me an example?

InvoiceShipToAddress AS 'End Customer Ship To Address 1',


How would I use it in this line?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
This may not help but worth a try

Replace(InvoiceShipToAddress, "  ", " ") AS 'End Customer Ship To Address 1',

Author

Commented:
let me give it a try.. Thanks CodeCruiser..

Author

Commented:
Nope... no go.. still not working.. There has got to be an easy fix.. can I re-write this somehow?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Is your issue due to

' ' AS 'End Customer Ship To Address 2',

Its adding an empty field.

Author

Commented:
no.. its the  data in the fields..

not sure if you can see it as the data is a small example of the trouble I am having with my current script.
10013190	RSLR	RKD-IT-CompuSuite Customer	5700 Utica Ridge Rd	 	Davenport	IA	52807	 	 	 	 	 	 	 	39	1	470064-777	3	12/31/2008	 	 	S	111183	 	 	 	
10013190	RSLR	Illinois Cancer Care	8940 Wood Sage Road	 	Peoria	IL	61615	 	 	 	 	 	 	 	39	1	FL932UT#ABA	3	2/23/2010	 	 	S	324987	 	 	 	
10013190	RSLR	Sisters of St Benedict	St Mary Monastery																								
2200 88th Avenue W	 	Rock Island	IL	61201	 	 	 	 	 	 	 	39	1	487734-B21	3	3/7/2011	 	 	S	527888

Open in new window

Author

Commented:
It looks like the customer name is doing it too..

Commented:
can you post the file with some data?

Author

Commented:
I did.. that is enough.. I am not going to give out any more info on customers..
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Your data would not be inline because the fields contain different values.

Author

Commented:
Everything is coming over fine.. "except" when there is 3 spaces in a row in a field.. see the example below.. I am pushing this through SSIS.. as comma delimited... into a already created csv file..
fileexample.jpg

Commented:
My bad i din't see your last post there... probably because i had the page open for a while without refreshing it before posting my comment.. Anyways, if you are sure that the columns use 3 spaces as Delimeter then you should be able to use this

REPLACE ("   ",",")

You will need to import the data as single column without using any delimeters using a script task and then use this replace function to parse out the columns

Author

Commented:
I am kind of confused what you mean.. I need to set up the SQL statement in the SSIS Package to be able to dumb the data right into my csv file.. I am going to automate this process..

Can I add the Replace to the SQL Script like codecruser suggested above?

Author

Commented:
well.. that made a mess! Laugh out Loud!

There has to be an easy answer.. maybe it is how i am setting it up in SSIS?

Commented:
How did you setup your CSV File.. what format is it using

When you run the script in SSMS..do you see all the fields correctly separated as they should be (I'm sure they are but wanted to check) ?
check this - http://decipherinfosys.wordpress.com/2008/07/23/ssis-exporting-data-to-a-text-file-using-a-package/


There are other ways to do it without using SSIS..I'm sure you are aware of it..If not, check the below link
http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/


Lastly,there's an example package to handle similar situation,to ive you an idea..but i guess this has been used in a reverse scenario where the source is a text file
http://www.bimonkey.com/2009/09/flat-files-with-too-many-delimiters/
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
You say CSV but I dont see the commas.

Author

Commented:
Let me do some reading.. :)

Author

Commented:
well.. maybe that is the whole issue.. :)

Author

Commented:
Oh.. i remember.. I had comma's in here before, and it would export because it said I didnt have a column name for the comma fields.. I was doing it like this...

          ' ' AS 'Unit Price' + ',',

and it was treating it like Unit Price and then a comma.. like a seperate field.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Try

' ,' AS 'Unit Price'

or

UnitPrice + ',' As 'Unit Price'


but you will need to convert to varchar if column type is not varchar.

Author

Commented:
That still didn't work.. sometihing about the spaces..

I HAVE to get rid of the 3 spaces.. why it does this.. I dont know..
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
>sometihing about the spaces..
What's the exact error?

Author

Commented:
no error.. it just dosent import the file correctly.. When it runs into 3 spaces in a field.. it starts the rest of the data for that record on the next row...

Commented:
do you see the 3 spacing when you execute it in SSMS? I guess you don't and they appear only in your text file is bcz of the way you set it up.. Create a csv file in Delimited format rather than ragged right or  Fixed width.. (follow the link above)

Author

Commented:
I will take another look at the link above.. some of the information is a little more than i really need. I am setting up my file "IN" Delimited format.. not ragged right or fixed with...
David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi,

The simple answer as has been suggested above is that your format is NOT comma delimited.

SSIS has the option of a text qualifier, so fields with embedded spaces can be delimited with quotes, and use commas to separate the fields.

Regards
  David

Author

Commented:
so.. I have been trying to look at this and just get the file out .. I was going to worry about automation later.. I just simply saved it as a CSV file and I am getting the same issue.. This is so simple.. yet not really............ Is there something I can write for the two fields that are causing issues? I am not sure what function will work for that.... Thoughs?

Author

Commented:
Okay.. so let me show everyone in great detail what is going on.. I have re-written my SQL query to include commas.. Please see below

now I am  
select DISTINCT 
       "LocationID" = 
				 CASE WHEN category = '6601' Then '10049636,'
				      WHEN category = '6650' Then '10049636,'  
		     	      WHEN category = '8412' Then '10013190,'  
				 END,
		'RSLR,'  AS 'Reporting Partner Type',
		CustomerName + ','  AS 'End Customer Ship To Name',
		InvoiceShipToAddress + ',' AS 'End Customer Ship To Address 1',		
        ' ,' AS 'End Customer Ship To Address 2',
		CustomerCity + ','  As 'End Customer Ship To City',
		CustomerState + ','  As 'End Customer Ship To State',
		CustomerZip + ','  As 'End Customer Ship to Zip',
		' ,' AS 'End Customer Ship To Phone',
	    ' ,' AS 'Bill To Name',
		' ,' AS 'Bill To Address 1',
		' ,' AS 'Bill To Address 2',
		' ,' AS 'Bill To City',
		' ,' AS 'Bill To State/Province',
		' ,' AS 'Bill To Postal Cd',
		'39,' AS 'Quantity Qualifier',
		'Quantity' = CAST(qty AS varchar(30)) + ',',
		Item + ',' AS 'HP Part Number',
		'3,' AS 'Data Type Qualifier',
	   'Invoice Date' = Convert(char(10), Date, 101) + ',',
	    ' ,' AS 'Reseller Assigned Part Number',
	    ' ,' AS 'Unit Price', 
	    'S,' AS 'Transaction Type',
	    'Invoice Number' = CAST(InvoiceID AS varchar(15))+ ',',
	    ' ,' AS 'UPC Code',
	    ' ,' AS 'Big Deal_Contract Number',
	    ' ,' AS 'Serial Number' 
from vw_jbarman_HP_WeeklySales 
where category IN ('6650', '6601', '8412')

Open in new window

Screen shot of SSMS results SSIS - Choose Destiniation SSIS - Specify Table SSIS - Provide a Source Query SSIS - Configure Flat File Desc. SSIS - SAve and run package SSIS - Save SSIS Package SSIS - Execution was successful Results STILL NOT RIGHT!!

Commented:
All you need to do is add a Text qualifier ..so that it treats all the text in a column as single column

Commented:
number2.jpg --- Flat File Destination Properties

Author

Commented:
I will try that. I think I tried once before.. but I will do that and show my results.

Commented:
and also Make Sure columns are mapped correctly
http://decipherinfosys.files.wordpress.com/2008/07/ssis1_8.jpg

Author

Commented:
It wont let me do that.... Please see the message it is giving me...
number10.jpg
Commented:
Right, all its saying is they both cant be the same Use " (double quotes) as your qualifier and include the Check - columns Names in First Data Row

Author

Commented:
Very helpful to the very end.. :)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.