1

i have a set of excel data containing merge cells that needs to be imported into sql server. However, the results of import show null for cells that are merged.

i have tried using IMEX function, testing with values 0,1,2. but it still imports merge cells as null. is there a way to this? thank you

SELECT * INTO BenchmarkSurvey FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\Benchmark Survey\Received\Survey1009.xls; Extended Properties="Excel 8.0;IMEX=1"')...[Sheet1$];
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
marilyn
  • 545
  • 3
  • 12
  • 19

1 Answers1

1

See this. Moreover, you can also try to export the excel file to a CSV file and then import it to SQL Server

Edit: this link may be of use: merged cells behavior

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
João Pereira
  • 3,545
  • 7
  • 44
  • 53
  • seems like your is helpful for reporting. but i wish to import data into SQL Server. – marilyn Sep 01 '10 at 09:11
  • OK, try exporting the table to .csv and importing it to SQL Server then. You can do it programatically (http://stackoverflow.com/questions/425379/how-to-export-data-as-csv-format-from-sql-server-using-sqlcmd) or you can use the GUI (http://msdn.microsoft.com/en-us/library/ms140052.aspx) – João Pereira Sep 01 '10 at 09:27
  • or is it feasible to use rownumber(), and update cells with previous row data if it is null? – marilyn Sep 02 '10 at 03:12
  • If you know what to put in null rows, sure. – João Pereira Sep 02 '10 at 10:51
  • I have imported a set of data from excel with merge cells into sql server. in the sql table, it now contains: period | product ID | price | qty 2006 | 123 | 100 | 1 null | null | null | 2 null | null | null | 2 2007 | 321 | 20 | 1 null | null | null | 2 null | null | null | 2 null | null | null | 2 i wish to populate null values based on the previous row record. I attempted to assign row number, but can't do an 'over' syntax, being afraid it might change the order of raw data. So how do i go abt this? – marilyn Sep 07 '10 at 04:42
  • First of all, create a duplicate of that table to avoid losing data. – João Pereira Sep 07 '10 at 08:37
  • Secondly, i think you'll need to use a loop to run through all the records in the table and then use a query resembling this: – João Pereira Sep 07 '10 at 08:38