Import excel file with bad column order

Question

I've to import a file (probably .csv or .txt) via a SSIS-package. The problem is, that the file is an excel file at the moment and is written very human readable which means it looks something like this:

Name1; ColumnName1; Data; Data; Data;...

Name1; ColumnName2; Data; Data; Data;...

Name1; ColumnName3; Data; Data; Data;...

Name2; ColumnName1; Data; Data; Data;...

Name2; ColumnName2; Data; Data; Data;...

The thing is that I need a database compatible order for the import. I did try to import everything in a dummy table with columns like Value0, Value1, Value3,... just as the file looks like at the moment and then dynamically create a new Table with the column names which are the values contained in Value1 of my dummy table. But how can I insert the values in the right column? And also I don't think that this is a good way to import files... (I hope you understand what I mean)

Does anyone have any idea how to handle these files?

Thanks in advance!

thepiyush13 thepiyush13 · Accepted Answer · 2015-12-10T00:34:08

Assuming following structure :

col1;col2;col3.....
Name1; ColumnName1; Data; Data; Data;...

try to make the table like this:

colA:colB:colC
name1:colName1: all values together 
name1:colName2: all values together 
name2:colName1: all values together
name2:colName2: all values together

You can get the result like this:

SELECT 
    colA, group_concat(colC SEPARATOR ',') 
FROM 
    table 
GROUP BY 
    colA;

will give you following result:

name1:val1,val2,val3
name2:val1,val2,val3

you can then import it using SSIS

Import excel file with bad column order

1 Answers