If Delimiter is Space?

[DataStageCnu]

Hi to all,
Here is Cnu. Dont think that i am asking silly question, but i did more with sequencial stage but i don't know how to get data when the data is seperated with Space. I tried with ASCII value 045. But it is giving "Too many columns" or "Required col is missing". Can any one help me plz...

regards
cnu

[kcbland]

A space is ASCII 32

[ray.wurlod]

Try importing the sequential file's table definition. Stay on the Format tab until you have it correct.
Put 032 into the column delimiter field and click Preview. Experiment with other combinations (maybe 000 in the quote character field), clicking Preview each time until you're happy with what's displayed.
Only then switch to the Columns tab and fix up the column definitions.

Load this set of column definitions into a Sequential File stage in a job, and also load the format (click the Load button on the Format tab of the stage's Output tab). Now try View Data; you should see the data OK.

If there are likely to be short rows, embedded EOR characters, and so on, adjsut the rules for these in the Columns grid (you will need to scroll right to do so).

[richdhan]

Hi,

Load this set of column definitions into a Sequential File stage in a job, and also load the format (click the Load button on the Format tab of the stage's Output tab). Now try View Data; you should see the data OK.

If you load the format you will get get a single space but if you manually type the space it will give an error message stating that the delimiter will only accept ascii or hexa values.

In the Delimiter set the value to 032. It works.

--Rich

[DataStageCnu]

Thanks buddy,
I tried by using ASCII value 032. Its working. Can u give the value of "Tab" so that i can pull data when it is seperated by Tab Space.

And one more, i am trying to pull data from Excel sheet. Suppose the data is in the following format.

~~~~~ dealer1 dealer2 dealer3
month01 01d1 01d2 01d3
month02 02d1 NULL 02d3
month03 03d1 03d2 NULL
month04 04d1 04d2 04d3
month05 05d1 05d2 05d3

1.) Can u tell me how to pull the data of four columns from Excel sheet to Flat file or DB?

2.) The next task is --- from excel or seq, the data should arrange in the following order
month01 dealer1 01d1
month01 dealer2 01d2
month01 dealer3 01d3
month02 dealer1 02d1
month02 dealer2 NULL
month02 dealer3 02d3
month03 dealer1 03d1
month03 dealer2 03d2
month03 dealer3 NULL
and so on.....

I tried Pivot Stage, but i am not able to get column headers as columns (Dealer1, dealer2 and dealer3). Remember number of columns are not constant.

Hope you understand my questions.

with regards,
Cnu

Code: Select all

Code: Select all

Code: Select all

[chulett]

Do yourself a favor and Google up an ASCII chart as a reference. There are a ton out there, one here for example.

[ogmios]

Do yourself a favor and Google up an ASCII chart as a reference. There are a ton out there, one here for example.

Or use "man ascii" on a UNIX box

Ogmios

[chulett]

Or that! I tend to forget about that.

[ray.wurlod]

I could have answered that but it wasn't addressed to me; my name is not "u".

("U" is a valid name, the only one of which I know is U Thant (secretary general of the UN some years back) but he is not in any position to answer this question, on a number of grounds.)

[vmcburney]

Thank goodness you live in Australia, I can just picture you going into road rage on American highways every time you see a U-HAUL truck! Not to mention Toys-R-Us and all those quaint antique stores that insist on being a Shoppe and an Olde one at that.

[DataStageCnu]

hi all,

In excel sheet my source data is in the following format

--------------A--------B-----C-----D----
========================
|Headers----------> Cust1 Cust2 Cust3
|
|Row 1--->month01 01d1 01d2 01d3
|Row 2--->month02 02d1 NULL 02d3
|Row 3--->month03 03d1 03d2 NULL
|Row 4--->month04 04d1 04d2 04d3
|Row 5--->month05 05d1 05d2 05d3
========================
The header of Months col is empty. It is like a 2D matrix.

Now i need to process and arrange the data in follwing format in seq file
======================
|Header-->Months--Dealers--Sales
|
|Row 1--->month01 dealer1 01d1
|Row 2--->month01 dealer2 01d2
|Row 3--->month01 dealer3 01d3
|Row 4--->month02 dealer1 02d1
|Row 5--->month02 dealer2 NULL
|Row 6--->month02 dealer3 02d3
|Row 7--->month03 dealer1 03d1
|Row 8--->month03 dealer2 03d2
|Row 9--->month03 dealer3 NULL
|and so on.....
======================

By using pivot i can able to get data in following order

Row 1--->month01 01d1
Row 2--->month01 01d2
Row 3--->month01 01d3
Row 4--->month02 02d1
Row 5--->month02 NULL
Row 6--->month02 02d3
Row 7--->month03 03d1
Row 8--->month03 03d2
Row 9--->month03 NULL
and so on.....

Can u give some suggestion how to get corresponding header value to respective row.

Thanks
cnu

[vmcburney]

You could do it with a transformer followed by a pivot stage followed by a transformer stage.

The transformer would replace NULL values with zeros to make things easier later on, going from this:
Headers----------> Cust1 Cust2 Cust3
|
|Row 1--->month01 01d1 01d2 01d3
|Row 2--->month02 02d1 NULL 02d3
|Row 3--->month03 03d1 03d2 NULL

To this:
Headers----------> Cust1 Cust2 Cust3
|
|Row 1--->month01 01d1 01d2 01d3
|Row 2--->month02 02d1 00d2 02d3
|Row 3--->month03 03d1 03d2 00d3

The pivot stage would have month as the key field and Cust1, Cust2 and Cust3 a derivation for an output SalesDealer field:

Month SalesDealer
Row 1--->month01 01d1
Row 2--->month01 01d2
Row 3--->month01 01d3
Row 4--->month02 02d1
Row 5--->month02 NULL

The transformer would then read the last two characters of the salesdealer figure to determine the dealer number and the first two characters for the sales amount and split them into two columns:

Deriviations:
Month = Month
Sales = input.salesdealer[1, 2]
Dealer = "dealer" : input.salesdealer[4, 1]

Month Sales Dealer
Row 1--->month01 01 dealer1
Row 2--->month01 01 dealer2
Row 3--->month01 01 dealer3
Row 4--->month02 02 dealer1
Row 5--->month02 00 dealer2
etc