Intergration file from SQL to Oracle for QlikView

[SBSP]

I'm currently creating an integration flat file from SQL 2008.

In the spec file.
Fields must be delimited with a ;
text strings must be qualified with " I.E "South Africa" and a number must not have a qualifier.

I.E
Country;Product;Sales;Country_Code
"South Africa";"Camera";1234.76;"ZA"

But when you are not able to supply string value the field must be left blank.

I.E "South Africa";;1234.76;"ZA"

So i created a query in a view, so that i can then use a SQL integration services to dump the data to a file,
and i am able to select ; as the delimiter and tell it to use "" for text and nothing for numbers,
but!
It will dump the data as "South Africa";"";1234.76;"ZA" to my knowledge ;""; is the "right" way of doing it, but anyway cant argue with them.
I would hate to run a tool over that that finds and replaces ;""; with ;; that would be very retarded or to create a SQL stored Proc that will dump the whole file into a temp table into one field and as it dumps check for blank fields then add ;; instead of ;"";

Any Help ?

 

[hyperian]

It might be easier to just write a SQL query and use that as the data source instead of referencing the table directly:

Something like this:

SELECT
    ISNULL('"' + Country + '";', ';') +
    ISNULL('"' + Product + '";', ';') +
    CONVERT(VARCHAR, Sales) + ';' +
    ISNULL('"' + Country_Code + '";', '')
FROM
    dbo.Table

 

[guest2013-1]

I'm currently creating an integration flat file from SQL 2008.

In the spec file.
Fields must be delimited with a ;
text strings must be qualified with " I.E "South Africa" and a number must not have a qualifier.

I.E
Country;Product;Sales;Country_Code
"South Africa";"Camera";1234.76;"ZA"

But when you are not able to supply string value the field must be left blank.

I.E "South Africa";;1234.76;"ZA"

So i created a query in a view, so that i can then use a SQL integration services to dump the data to a file,
and i am able to select ; as the delimiter and tell it to use "" for text and nothing for numbers,
but!
It will dump the data as "South Africa";"";1234.76;"ZA" to my knowledge ;""; is the "right" way of doing it, but anyway cant argue with them.
I would hate to run a tool over that that finds and replaces ;""; with ;; that would be very retarded or to create a SQL stored Proc that will dump the whole file into a temp table into one field and as it dumps check for blank fields then add ;; instead of ;"";

Any Help ?

Who are "they" that you mention?

Qlikview doesn't care if it's ;""; (which is the right way) or ;;

 

[SBSP]

Who are "they" that you mention?

Qlikview doesn't care if it's ;""; (which is the right way) or ;;

My Dutch "Friends" in the Netherlands (Integration team in Europe)
You cant argue with them cus they are superior human beings.

So i'm just going to do it their way and get it over

 

[guest2013-1]

Do they know their way is wrong?

 

[SBSP]

I have sent them a mail, saying there is something "I" dont understand, to try and be nice.
I said that if i supply a field as ;""; then it means I'm giving a blank TEXT field But if I give a blank text field a ;; then you are actually converting a blank text field into a blank number field.

 

[SBSP]

I have sent them a mail, saying there is something "I" dont understand, to try and be nice.
I said that if i supply a field as ;""; then it means I'm giving a blank TEXT field But if I give a blank text field as ;; then you are actually converting a blank text field into a blank number field.

 

[zippy]

No idea what QlikView is. Are you or they trying to load the data from a file into Oracle tables? Because Oracle tools can be configured to recognise " as an enclosing character and not as part of the field value.

 

[guest2013-1]

QlikView is a reporting tool that makes pivoting/charting quite easy

 

[zippy]

I have sent them a mail, saying there is something "I" dont understand, to try and be nice.
I said that if i supply a field as ;""; then it means I'm giving a blank TEXT field But if I give a blank text field as ;; then you are actually converting a blank text field into a blank number field.

Oracle doesn't use the contents of a field to determine the data type. If they are using SQL*Loader then the data type is specified in a control file, which has to correspond to the data type of the column in the destination table.

They are probably loading this using a generic tool which is configured in a specific way for a host of existing interfaces and don't want to modify for one situation.