You are not logged in.

Data file without column headers

Dear visitor, welcome to Palo Community Forum. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

Pommie

Sage

  • "Pommie" is male
  • "Pommie" started this thread

Posts: 411

Date of registration: Feb 21st 2007

Location: Lytham UK

Occupation: OLAP consultant specialising in retail

  • Send private message

1

Wednesday, June 9th 2010, 4:56pm

Data file without column headers

If I have file with no header can I refer to individual columns using some sort of reference like Col1?

If not is there an easy way to put a column heading line in to the file automagically using ETL?
Best wishes

John Hobson
The Planning Factory, Lytham, UK
www.planfact.co.uk

holger_b

Sage

  • "holger_b" is male

Posts: 271

Date of registration: Jun 1st 2010

Location: Freiburg, Germany

Occupation: BI Consultant

  • Send private message

2

Wednesday, June 9th 2010, 5:13pm

RE: Data file without column headers

Do you want to use that file as an ETL source? No problem, you can refer to those columns as column1, column2 and so on. Or what do you need that for?

Pommie

Sage

  • "Pommie" is male
  • "Pommie" started this thread

Posts: 411

Date of registration: Feb 21st 2007

Location: Lytham UK

Occupation: OLAP consultant specialising in retail

  • Send private message

3

Wednesday, June 9th 2010, 5:47pm

Thanks Holger

That's what I thought, but the query

Select column1, column2, column3, column4 from "Flow Data Week UTF"

throws an error saying

The component "Actuals FL Extract copy" is tested with errors. Error message: Failed to retrieve data from internal table: Select column1, column2,column3, column4 from "Data_Imports_cm636026350"."Flow Data Week UTF", Column 'COLUMN1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN1' is not a column in the target table.

I have also tried it in double quotes but I get the same message but with the words Column1 in lower case

The source file works fine when it HAS a header and the header columns are referred to explicitly
Best wishes

John Hobson
The Planning Factory, Lytham, UK
www.planfact.co.uk

This post has been edited 1 times, last edit by "Pommie" (Jun 9th 2010, 5:47pm)

holger_b

Sage

  • "holger_b" is male

Posts: 271

Date of registration: Jun 1st 2010

Location: Freiburg, Germany

Occupation: BI Consultant

  • Send private message

4

Wednesday, June 9th 2010, 6:06pm

I tried again, for me it works like this:

SELECT "column2", "column3" FROM "FlatFile"

"FlatFile" is the name of the extract, not the file, but you have that I guess.

Pommie

Sage

  • "Pommie" is male
  • "Pommie" started this thread

Posts: 411

Date of registration: Feb 21st 2007

Location: Lytham UK

Occupation: OLAP consultant specialising in retail

  • Send private message

5

Wednesday, June 9th 2010, 6:17pm

Holger - thanks for your help.

I thought that this should work before posting but it isn't happening here

SELECT "column1" FROM "SKU Commitment"
SELECT column1 FROM "SKU Commitment"

Throws the same error (SKU Commitment being the last of 4 different data connection I tried it on)

SELECT * FROM "SKU Commitment" works fine

I'm off to bang my head against something solid.
Best wishes

John Hobson
The Planning Factory, Lytham, UK
www.planfact.co.uk

holger_b

Sage

  • "holger_b" is male

Posts: 271

Date of registration: Jun 1st 2010

Location: Freiburg, Germany

Occupation: BI Consultant

  • Send private message

6

Wednesday, June 9th 2010, 8:02pm

Are you back? :-)

Can you post the ETL project plus an excerpt of the flat file? I do not want to believe we cannot solve it. I would love to look into it tomorrow, maybe I will discover something!

Cheers
Holger

Pommie

Sage

  • "Pommie" is male
  • "Pommie" started this thread

Posts: 411

Date of registration: Feb 21st 2007

Location: Lytham UK

Occupation: OLAP consultant specialising in retail

  • Send private message

7

Wednesday, June 9th 2010, 9:06pm

Hi Holger

Well my head is a bit sore now :-)

I am attaching a zip file with a datafile called fileforumtest.csv and an ETL Data_Imports_Forum.xml

It has two extracts

Actuals FL Extract Col 1 which does not work and Actuals FL Extract * which does.

Any suggestions gratefuly received
Best wishes

John Hobson
The Planning Factory, Lytham, UK
www.planfact.co.uk

Withnail

Professional

Posts: 34

Date of registration: Mar 30th 2009

Location: Australia

Occupation: BI Consultant

  • Send private message

8

Thursday, June 10th 2010, 12:16am

Hi John,

1. uncheck the tick box in the file component (ie no headers)
2. in your sql statement, Column1 must be the exact case, ie column1 (derby is case sensitive)
3. add double quotes around column1, ie "column1"


also, extracts in ETL appear to work a lot faster if you can get away with leaving the SQL box blank (leaving the sql box blank is the same as doing select * from file)

Obviously, not always preferable, but usually speed usually wins out over preference :)


HTH,


Withnail
Naked Data
Business Intelligence & Performance Management
Level 23 40 City Road Southbank 3006 VIC Australia
national: +61 1300 406 334
www.nakeddata.com

Pommie

Sage

  • "Pommie" is male
  • "Pommie" started this thread

Posts: 411

Date of registration: Feb 21st 2007

Location: Lytham UK

Occupation: OLAP consultant specialising in retail

  • Send private message

9

Thursday, June 10th 2010, 7:44am

Chris

Many thanks for that - it is now working!!!

Is there a way to do this WITHOUT using the SQL box then?
Best wishes

John Hobson
The Planning Factory, Lytham, UK
www.planfact.co.uk

holger_b

Sage

  • "holger_b" is male

Posts: 271

Date of registration: Jun 1st 2010

Location: Freiburg, Germany

Occupation: BI Consultant

  • Send private message

10

Thursday, June 10th 2010, 9:01am

Do not insert any statement at all, that will do, as Chris said, you will have column headers like "column1...". Or what else is missing?

Regards
Holger

Withnail

Professional

Posts: 34

Date of registration: Mar 30th 2009

Location: Australia

Occupation: BI Consultant

  • Send private message

11

Thursday, June 10th 2010, 12:11pm

Yes, that's right. You do not have to write select * sql in the box. Putting nothing will do it for you.
Naked Data
Business Intelligence & Performance Management
Level 23 40 City Road Southbank 3006 VIC Australia
national: +61 1300 406 334
www.nakeddata.com
Rate this thread