excel

• Best-330874

  SSCrazy

  Points: 2350

  More actions

  February 21, 2007 at 9:22 am

  #369606

   

  Hi,

     i need help on this.

  I have a worksheet in which i have the following fields.How can i do the import IN sql server 2005

  so that every field (District, School Name , e.t.c)in the excel worksheet must be a seperate column in the database table.What i am trying to ask here is can somebody guide me how to resolve this issue.

  Thanks

  Excel worksheet is as follows with the following fields

  Column A                       Column B                                                  Column C

                  

                  

  District:                        School Name:                                             Project #    

                  

  Architect:                     Gen. Contractor:                                         Project Mgr:    

                  

  MSBA Audit Firm #           Project Start Date:                                      Project End Date:       

                  

  Approved Square Feet      Allowed Cost per Sq. Ft.                               Actual Cost per Sq. Ft      

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 21, 2007 at 10:02 am

  #690968

  it looks like you have 12 fields you want to grab from 3 columns, is that right? so this is really ONE record with 12 fields?

  importing from excel , by default,expects a column for each field, so columns A thru I, and all values in  subsequent rows (ie comumnnames in row 1, data in row 2 and beyond;

  my suggestions: 1: redesign the spreadhseet and import it normally.

  2; import the same sheet 4 times, once for row1 to populate District: School Name: Project # ,

   a second time to get Architect: Gen. Contractor: Project Mgr:  but start at row 2, etc.

  3; import the whole thing into a staging table, and then extract the rows you want into a final table;

  obviously, if it's just 12 values and a single sheet, you could throw the data in manually, but I figured your importing this multiple times or have multiple spreadsheets with the same data?

  HTH

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• luciano barretta

  Mr or Mrs. 500

  Points: 563

  More actions

  February 22, 2007 at 1:28 am

  #691079

  mmhhh.... or you could try to solve your problem in this way:

  import all the sheet adding 2 columns: the first for record id (so there will be 3 records with the same id) and the second with a rownum (1, 2, 3) and then using a cartesian product on this table generate the final result, all this doing only one import.... but may be i'm wrong...

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  February 22, 2007 at 6:55 am

  #691136

  If the data is as suggested by the others, ie each database row = 4 rows in spreadsheet and the 1st row = header then you will find it difficult to import the data with DTS as the microsoft ISAM driver will not process the data correctly. The drivier will produce null for numeric data in a char column and vice versa, there are several posts on this forum regarding settings you can use to overcome this but I have never got them to work.

  My suggestion would be:

  Save the spreadsheet as csv

  Create an Import Table

  CREATE ImportTable (RowID int IDENTITY(1,1), ColumnA varchar(100), ColumnB varchar(100), ColumnC varchar(100))

  BCP the csv file to this table using a format file

  Select the data

  SELECT a.ColumnA  AS [District],a.ColumnB AS [School Name],a.ColumnC AS [Project],

  b.ColumnA AS [Architect],b.ColumnB AS [Gen. Contractor],b.ColumnC AS [Project Mgr],

  c.ColumnA AS [MSBA Audit Firm],c.ColumnB AS [Project Start Date],c.ColumnC AS [Project End Date],

  d.ColumnA AS [Approved Square Feet],d.ColumnB AS [Allowed Cost per Sq Ft],d.ColumnC AS [Actual Cost per Sq Ft]

  FROM ImportTable a

  INNER JOIN ImportTable b ON b.RowID = a.RowID+1

  INNER JOIN ImportTable c ON c.RowID = a.RowID+2

  INNER JOIN ImportTable c ON d.RowID = a.RowID+3

  WHERE a.RowID > 4 -- Ignore the Header row

  AND (a.RowID % 4) = 1

  Far away is close at hand in the images of elsewhere.
  Anon.