getting columnames from another table

[Machiaveli]

Hello,

I've posted this item before, but no really solutions to solve this.

I've some textfiles which i would like to import. In one textfile there are over 1024 columns when separated. This exceeds sql server's maximum columns. Let call this the resultfile.
In the other texfile i've got a descriptionfile about the resultfile. The description contains the columnnames of the resultfile (see below for example). The problem with the resultfile is that it doesn't contain any columnnames, but the columns appear in order of the descriptionfile.

The tricky part is that there are columns which really represents 1 column (those columns has the same name but with an indexnumber). Those columns need to be parsed or stored in another way.

For example a resultfile with no columnames(never mind the columnames and values):

field1_1 | field1_2 | field2 | field3
empty yes 1 2003

Above should be:
field1 | field2 | field3
yes 1 2003

or

variablename | answer
field1_1 | null
field1_2 | 2 (value is recoded, it's the 2nd index)
field2 | 1
field3 | 2003

Descriptionfile example:

formid fieldnr fieldname idx length
1 1 field0 0 2
1 2 field1 1 1
1 3 field1 2 1
1 4 field2 0 2
1 5 field3 0 4

Now there should be a way to transform the data so that the columns where no value is specified are parsed.

Can someone help me on this? This bustin my brains for weeks.

 

[NoCoolHandle]

Some information that might make it easier to help you...

1. Is the textfile comma seperated or fixed width fields?
2. What are you using to get the data into SQL Server?
a. BCP
b. DTS
c. Bulk Insert
d. Some custom software writen in some dev language
3. How many rows are you expecting?
4. What is the number of Actual Columns (not pesudo cols)
5. What would the final result table look like?

It seems from your description that some colsm (within a row) have rows, would this be better represented as a second child table?

 

[SQLSister]

Have you considered parsing the data outside of SQL Server using regular expressions? Inthis way you might be able to get the data into a form that SQLServer can deal with.

Another possibilty is to bring all the data in to one field in a holding table and then parse from there with specific sql Statements.

A third possibility is wroking with the people who provide this file to give it to you in a better format. Perhaps two files broken into the number of columns you can import easily and then pasre from within SQL Server.





Questions about posting. See faq183-874