The Excel Spreadsheet has 100 columns in each row. Let say I have 3 rows in Excel sheet.
Each row has 100 columns as follows,
column1, column2, ...column100
Now I want to load it to a table which has only one column.
The first record will be the column1 of the first row from the excel spreadsheet
The second record will be the column2 of the first row from the excel spreadsheet
...
The 100th record will be the column100 of the first row from the excel spreadsheet
The 101th record will be the column1 of the second row from the excel spreadsheet
...
Can anyone give me a suggections? Thanks in advance,
PeterHmm, any way you can transpose the columns in Excel? I can't think of a simple way to create an SQL Statement to transpose the columns in DTS. It might be easier to manipulate Excel, rather than trying to do the transpose in DTS.|||I agree with the first post, but you could do it starting with DTS. Suck the excel sheet into a 'holding' table and then run a stored procedure that takes each cell and makes it a record by itself. You could use a cursor to step through the records and do 100 inserts to the final table for each row in the holding table.|||Is the order truyly that important?
You can get all the data in one column by using the following SQL statement instead of a table as your source:
SELECT Column1
FROM [Sheet1$]
UNION ALL
SELECT Column2
FROM [Sheet1$]
UNION ALL
SELECT Column3
FROM [Sheet1$]
...
You'll need a lot of cut and past (or a handy little script) to generate all the UNIONed selects.
If the order truly is important, then I'd add another column to your spreadsheet for the row number. Import the entire spreadsheet into the a temp table using something like this:
SELECT TheValue = Column1,
TheRow = RowNumber, -- your row number column
TheColumn = 1
FROM [Sheet1$]
UNION ALL
...
Increment the column number for each column. Again, a script will write this monster select for you. Then you can insert into your final table:
INSERT ...
SELECT TheValue
FROM MyTempImportTable
ORDER by TheRow ASC, TheColumn ASC
FYI - You may need to use UNION rather than UNION ALL. I don't recall the exact workings of EXCEL with respect to the ALL part. A little experimenting (or a syntax) error should clear it up pretty quickly.
