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$];
Improve this question

1 Answer 1

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

seems like your is helpful for reporting. but i wish to import data into SQL Server.
OK, try exporting the table to .csv and importing it to SQL Server then. You can do it programatically (stackoverflow.com/questions/425379/…) or you can use the GUI (msdn.microsoft.com/en-us/library/ms140052.aspx)
or is it feasible to use rownumber(), and update cells with previous row data if it is null?
If you know what to put in null rows, sure.
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?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.