|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Complex data extract from excelI have the following data from Excel spreadsheet. LST125A LST075A LST040A PBL125A LST125A 60 240 240 360 LST075A 240 60 240 360 LST040A 240 240 60 360 PBL125A 360 360 360 60 I need to extract the data in the following manner. ColNumber RowNumber Value 1 1 60 1 2 240 1 3 240 .. .. 2 1 240 Could anyone please suggest me how to extract data? Thanks in advance ad This can be done via a VBA procedure: loop through the rows and columns of
UsedRange and copy normalized rows to another worksheet. Perhaps someone in an Excel-related newsgroup already has an appropriate solution. ML --- http://milambda.blogspot.com/ Is this a one-time import, or something that will run repeatedly with
different spreadsheets? Is the number of columns that same in every spreadsheet, or does it vary? Do you have any need to preserve the idea that 1 = LST125A, 2 = LST075A, etc? Are those column headings / first column values always the same, or different with each spreadsheet? The approach I would probably take (assuming SQL Server 2000) is to define a table. Make sure all columns (except the identity column) allow NULLs. CREATE TABLE FromExcel (sequence int IDENTITY NOT NULL, colA varchar(20) NULL, col1 int NULL, col2 int NULL, col3 int NULL, col4 int NULL) Then I would use DTS to import the spreadsheet into that table, but controlling the column mapping so that the sequence column is not referenced, and the spreadsheet columns go into the other columns. The first line with the column headers will not be imported. There are a lot of assumptions in the above. That the data in the spreadsheet starts in the upper left corner the first sheet, for example. One you have that table loaded, the rest of it is to swing the data around: SELECT C.i as ColNumber, X.sequence as RowNumber, CASE C.i WHEN 1 THEN col1 WHEN 2 THEN col2 WHEN 3 THEN col3 WHEN 4 THEN col4 END as Value FROM FromExcel as X CROSS JOIN (select 1 as i UNION ALL select 2 UNION ALL select 3 UNION ALL select 4) as C WHERE X.colA IS NOT NULL The NOT NULL test is there because I have often ended up with questionable data coming in from a spreadsheet. Roy Harvey Beacon Falls, Ct On Wed, 16 Aug 2006 13:30:02 -0700, ad <a*@discussions.microsoft.com> wrote: Show quote >Hi, >I have the following data from Excel spreadsheet. > > LST125A LST075A LST040A PBL125A >LST125A 60 240 240 360 >LST075A 240 60 240 360 >LST040A 240 240 60 360 >PBL125A 360 360 360 60 > > >I need to extract the data in the following manner. >ColNumber RowNumber Value >1 1 60 >1 2 240 >1 3 240 >. >. >2 1 240 > >Could anyone please suggest me how to extract data? > >Thanks in advance >ad Hi Roy,
Thank you so much for your reply. This is not a one time import and there are around 20 spreadsheets. The data in each spreadsheet is different and the column information is variable. I will prepare the data according to your explanation. Is there any way to automate this process? Thanks so much, ad Show quote "Roy Harvey" wrote: > Is this a one-time import, or something that will run repeatedly with > different spreadsheets? Is the number of columns that same in every > spreadsheet, or does it vary? Do you have any need to preserve the > idea that 1 = LST125A, 2 = LST075A, etc? Are those column headings / > first column values always the same, or different with each > spreadsheet? > > The approach I would probably take (assuming SQL Server 2000) is to > define a table. Make sure all columns (except the identity column) > allow NULLs. > > CREATE TABLE FromExcel > (sequence int IDENTITY NOT NULL, > colA varchar(20) NULL, > col1 int NULL, > col2 int NULL, > col3 int NULL, > col4 int NULL) > > Then I would use DTS to import the spreadsheet into that table, but > controlling the column mapping so that the sequence column is not > referenced, and the spreadsheet columns go into the other columns. The > first line with the column headers will not be imported. > > There are a lot of assumptions in the above. That the data in the > spreadsheet starts in the upper left corner the first sheet, for > example. > > One you have that table loaded, the rest of it is to swing the data > around: > > SELECT C.i as ColNumber, > X.sequence as RowNumber, > CASE C.i > WHEN 1 THEN col1 > WHEN 2 THEN col2 > WHEN 3 THEN col3 > WHEN 4 THEN col4 > END as Value > FROM FromExcel as X > CROSS JOIN > (select 1 as i UNION ALL > select 2 UNION ALL > select 3 UNION ALL > select 4) as C > WHERE X.colA IS NOT NULL > > The NOT NULL test is there because I have often ended up with > questionable data coming in from a spreadsheet. > > Roy Harvey > Beacon Falls, Ct > > On Wed, 16 Aug 2006 13:30:02 -0700, ad <a*@discussions.microsoft.com> > wrote: > > >Hi, > >I have the following data from Excel spreadsheet. > > > > LST125A LST075A LST040A PBL125A > >LST125A 60 240 240 360 > >LST075A 240 60 240 360 > >LST040A 240 240 60 360 > >PBL125A 360 360 360 60 > > > > > >I need to extract the data in the following manner. > >ColNumber RowNumber Value > >1 1 60 > >1 2 240 > >1 3 240 > >. > >. > >2 1 240 > > > >Could anyone please suggest me how to extract data? > > > >Thanks in advance > >ad > You MIGHT be able to create one table wide enough for your widest
spreadsheet, if the column arrangement is the same except for the number of repeated columns. That might let you write one DTS package and just change the excel file name. The SQL would have to change to skipp NULLs. Roy Harvey Beacon Falls, CT On Thu, 17 Aug 2006 18:49:01 -0700, ad <a*@discussions.microsoft.com> wrote: Show quote >Hi Roy, >Thank you so much for your reply. >This is not a one time import and there are around 20 spreadsheets. >The data in each spreadsheet is different and the column information is >variable. > >I will prepare the data according to your explanation. > >Is there any way to automate this process? > >Thanks so much, >ad > >"Roy Harvey" wrote: > >> Is this a one-time import, or something that will run repeatedly with >> different spreadsheets? Is the number of columns that same in every >> spreadsheet, or does it vary? Do you have any need to preserve the >> idea that 1 = LST125A, 2 = LST075A, etc? Are those column headings / >> first column values always the same, or different with each >> spreadsheet? >> >> The approach I would probably take (assuming SQL Server 2000) is to >> define a table. Make sure all columns (except the identity column) >> allow NULLs. >> >> CREATE TABLE FromExcel >> (sequence int IDENTITY NOT NULL, >> colA varchar(20) NULL, >> col1 int NULL, >> col2 int NULL, >> col3 int NULL, >> col4 int NULL) >> >> Then I would use DTS to import the spreadsheet into that table, but >> controlling the column mapping so that the sequence column is not >> referenced, and the spreadsheet columns go into the other columns. The >> first line with the column headers will not be imported. >> >> There are a lot of assumptions in the above. That the data in the >> spreadsheet starts in the upper left corner the first sheet, for >> example. >> >> One you have that table loaded, the rest of it is to swing the data >> around: >> >> SELECT C.i as ColNumber, >> X.sequence as RowNumber, >> CASE C.i >> WHEN 1 THEN col1 >> WHEN 2 THEN col2 >> WHEN 3 THEN col3 >> WHEN 4 THEN col4 >> END as Value >> FROM FromExcel as X >> CROSS JOIN >> (select 1 as i UNION ALL >> select 2 UNION ALL >> select 3 UNION ALL >> select 4) as C >> WHERE X.colA IS NOT NULL >> >> The NOT NULL test is there because I have often ended up with >> questionable data coming in from a spreadsheet. >> >> Roy Harvey >> Beacon Falls, Ct >> >> On Wed, 16 Aug 2006 13:30:02 -0700, ad <a*@discussions.microsoft.com> >> wrote: >> >> >Hi, >> >I have the following data from Excel spreadsheet. >> > >> > LST125A LST075A LST040A PBL125A >> >LST125A 60 240 240 360 >> >LST075A 240 60 240 360 >> >LST040A 240 240 60 360 >> >PBL125A 360 360 360 60 >> > >> > >> >I need to extract the data in the following manner. >> >ColNumber RowNumber Value >> >1 1 60 >> >1 2 240 >> >1 3 240 >> >. >> >. >> >2 1 240 >> > >> >Could anyone please suggest me how to extract data? >> > >> >Thanks in advance >> >ad >> I have implemented according to your suggestions. It is working fine. thanks
so much. Right now I have 23 columns in the spreadsheet. I have created a table with 30 columns. How to eliminate the null values for columns 24 to 30. Thanks, ad Show quote "Roy Harvey" wrote: > You MIGHT be able to create one table wide enough for your widest > spreadsheet, if the column arrangement is the same except for the > number of repeated columns. That might let you write one DTS package > and just change the excel file name. The SQL would have to change to > skipp NULLs. > > Roy Harvey > Beacon Falls, CT > > On Thu, 17 Aug 2006 18:49:01 -0700, ad <a*@discussions.microsoft.com> > wrote: > > >Hi Roy, > >Thank you so much for your reply. > >This is not a one time import and there are around 20 spreadsheets. > >The data in each spreadsheet is different and the column information is > >variable. > > > >I will prepare the data according to your explanation. > > > >Is there any way to automate this process? > > > >Thanks so much, > >ad > > > >"Roy Harvey" wrote: > > > >> Is this a one-time import, or something that will run repeatedly with > >> different spreadsheets? Is the number of columns that same in every > >> spreadsheet, or does it vary? Do you have any need to preserve the > >> idea that 1 = LST125A, 2 = LST075A, etc? Are those column headings / > >> first column values always the same, or different with each > >> spreadsheet? > >> > >> The approach I would probably take (assuming SQL Server 2000) is to > >> define a table. Make sure all columns (except the identity column) > >> allow NULLs. > >> > >> CREATE TABLE FromExcel > >> (sequence int IDENTITY NOT NULL, > >> colA varchar(20) NULL, > >> col1 int NULL, > >> col2 int NULL, > >> col3 int NULL, > >> col4 int NULL) > >> > >> Then I would use DTS to import the spreadsheet into that table, but > >> controlling the column mapping so that the sequence column is not > >> referenced, and the spreadsheet columns go into the other columns. The > >> first line with the column headers will not be imported. > >> > >> There are a lot of assumptions in the above. That the data in the > >> spreadsheet starts in the upper left corner the first sheet, for > >> example. > >> > >> One you have that table loaded, the rest of it is to swing the data > >> around: > >> > >> SELECT C.i as ColNumber, > >> X.sequence as RowNumber, > >> CASE C.i > >> WHEN 1 THEN col1 > >> WHEN 2 THEN col2 > >> WHEN 3 THEN col3 > >> WHEN 4 THEN col4 > >> END as Value > >> FROM FromExcel as X > >> CROSS JOIN > >> (select 1 as i UNION ALL > >> select 2 UNION ALL > >> select 3 UNION ALL > >> select 4) as C > >> WHERE X.colA IS NOT NULL > >> > >> The NOT NULL test is there because I have often ended up with > >> questionable data coming in from a spreadsheet. > >> > >> Roy Harvey > >> Beacon Falls, Ct > >> > >> On Wed, 16 Aug 2006 13:30:02 -0700, ad <a*@discussions.microsoft.com> > >> wrote: > >> > >> >Hi, > >> >I have the following data from Excel spreadsheet. > >> > > >> > LST125A LST075A LST040A PBL125A > >> >LST125A 60 240 240 360 > >> >LST075A 240 60 240 360 > >> >LST040A 240 240 60 360 > >> >PBL125A 360 360 360 60 > >> > > >> > > >> >I need to extract the data in the following manner. > >> >ColNumber RowNumber Value > >> >1 1 60 > >> >1 2 240 > >> >1 3 240 > >> >. > >> >. > >> >2 1 240 > >> > > >> >Could anyone please suggest me how to extract data? > >> > > >> >Thanks in advance > >> >ad > >> > On Fri, 18 Aug 2006 06:27:02 -0700, ad <a*@discussions.microsoft.com>
wrote: >I have implemented according to your suggestions. It is working fine. thanks I think the addition to the WHERE clause will do it.>so much. >Right now I have 23 columns in the spreadsheet. I have created a table with >30 columns. How to eliminate the null values for columns 24 to 30. >Thanks, >ad SELECT C.i as ColNumber, X.sequence as RowNumber, CASE C.i WHEN 1 THEN col1 WHEN 2 THEN col2 WHEN 3 THEN col3 WHEN 4 THEN col4 END as Value FROM FromExcel as X CROSS JOIN (select 1 as i UNION ALL select 2 UNION ALL select 3 UNION ALL select 4) as C WHERE X.colA IS NOT NULL AND CASE C.i WHEN 1 THEN col1 WHEN 2 THEN col2 WHEN 3 THEN col3 WHEN 4 THEN col4 END IS NOT NULL Roy Harvey Beacon Falls, CT  |
|||||||||||||||||||||||
Other interesting topics