Flat File with some blank columns

Question

Post reply

Flat File with some blank columns

jyoti_bhatt

SSCommitted

Points: 1950
More actions
January 5, 2010 at 8:10 am

#141407

Hi,
I hope someone can help with my issue. I want to extract data from a flat file (CSV) that has column names but some columns are blank with no data, this is causing the data to be loaded into a table but the data is in incorrect columns as the blank columns with null value is messing the data.
Any ideas how I can get round this. I have already ticked the box to retain null values from the source.

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

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

wwei SSC Eights! Points: 961 More actions · Answer 1

take a look at this

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx

jyoti_bhatt SSCommitted Points: 1950 More actions · Answer 2

Thanks for that its quite helpful. But difficult to compose when have 60 plus columns on the flat file. Please let me know if you know of any other workarounds.

Many Thanks for your help.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

Can you provide an example - just 3 or 4 rows - showing what you mean exactly by "blank with no data"? Can a column be "blank with some data"? Is the column actually missing (ie, not even any place-holders)?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

jyoti_bhatt SSCommitted Points: 1950 More actions · Answer 4

E.g.

Name Address Phone Age

aaa bbb 20

aaa bbb 40

ccc ddd 50

As you can see example above, the phone column does exist with column name but has no data this then loads the age values in phone column when loading into a table. So the data is not being loaded into the correct column as it shows on the flat file.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 5

jyoti_bhatt (1/5/2010)
E.g.
Name Address Phone Age
aaa bbb 20
aaa bbb 40
ccc ddd 50
As you can see example above, the phone column does exist with column name but has no data this then loads the age values in phone column when loading into a table. So the data is not being loaded into the correct column as it shows on the flat file.

So that's the dodgy data. I imagine some of the data has phone numbers and that loads OK.

Is this data space-delimited, or tab-delimited? Is there an extra delimiter separating Address and Age when there is no Phone data?

Phil

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

jyoti_bhatt

Name Address Phone Age
aaa bbb 20
aaa bbb 40
ccc ddd 50

Is the above actually what is in your CSV file or is it:

Name Address Phone Age something something

aaa,bbb,20,,xxx,, -- where ,, is the blank field(s) which are giving me the trouble

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]