Concatenate columns with no column headers and different number of rows

Question

Concatenate columns with no column headers and different number of rows

mallybee

Mr or Mrs. 500

Points: 584
More actions
March 27, 2012 at 7:46 am

#253190

Apologies in advance if this is the incorrect forum as I am new to SQL server.
I’m SQL server 2005 and am using Enterprise Manager v8.
I want to concatenate the last five columns of a CSV file into one column (each data set separated by a comma or whichever symbol is easiest) before it is imported into the server. The reason for this is because I have six files, all of which may have a different number of columns as described below.
Column (A-BL) have columns headers but the next four (BM-BP) are blank. The data will be concatenated into column BL. Also there is not always data in each of the cells as shown below.
BL BM BN BO BP
1 Title
2 A
3 X E F
4
5 G B
The CSV files can have different number of rows.
Is there a way of doing the above? I’m guessing I would use an Active X script but correct me if I’m wrong.
Or would another solution be to add a column header onto the four blank columns before it is imported into the server?
Thank you in advance for any help.
Malcolm

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

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

I dint wuite understand what you are trying to achieve here. Can you please take a look at the following article and provide some extra information?

http://www.sqlservercentral.com/articles/Best+Practices/61537/

mallybee Mr or Mrs. 500 Points: 584 More actions · Answer 2

Hi ColdCoffee

Thank you for your reply and apologies that you didn't understand my question.

Basically i have six CSV files that i want to load into SQL server 2005. They all have a different number of rows of info and a different number of columns.

I want to concatenate the last five columns of each file (individually) into one column before they are imported into the server otherwise the transformations won't work as some of the columns have no column headers and don't contain any data.

Is this any clearer for you?

Thank you in advance.

Mally

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Some details would be helpful but why oh why do you want to concatenate values into a single column? This breaks the basic tenets of sql. Have you considered how you are going to retrieve this information? You will have to parse, or split, the values when you want to pull this data back out. The description of you process is going to create more work and worse performance when inserting your data and then again when you pull it back out. This just screams of a design that is not well suited to the task you are trying to accomplish.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mallybee Mr or Mrs. 500 Points: 584 More actions · Answer 4

Thank you again for your help.

You can tell im new to this. I may be overcomplicating things.

When i import my csv files into the server the transformations in the DTS dont work because some of the columns that don't have column headers are not pulled through e.g.

File 1 may only have 20 columns and file 2 may have 21 columns which causes the transformations to fail.

I hope this make sense.

Basically what i require is a way of importing six files into the server each of which has a different number of columns and rows.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Can you create 6 import processes? One for each type of import. That way you can import your data into a table that is appropriate for what you are importing. It will make things a lot easier in the long run.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mallybee Mr or Mrs. 500 Points: 584 More actions · Answer 6

Thank you Sean for your advice.

I've created the six processes for each file.

Maybe this will help to explain what i mean.

In order to get the transformations to work i have to manually go into csv file in notepad and add commas onto the end of the column headers row so each file has the same number of commas which wil be a maximum of four. Then my transformations work and the DTS runs.