Location: The Official Oracle Wiki

Discussion: Loading data from excel sheetReported This is a featured thread

Category: Discussion Forum / General Discussion
Showing 6 posts

pthakar
Loading data from excel sheet
Jun 5 2009, 10:09 AM EDT | Post edited: Jun 5 2009, 10:09 AM EDT
trying to import data from excel sheet into a table for some reason table shows blank rows.
Any idea how to fix this problem or what could be the reason.
thanks Do you find this valuable?    

ChrisStuart
1. RE: Loading data from excel sheet
Jun 10 2009, 10:27 PM EDT | Post edited: Jun 10 2009, 10:27 PM EDT
Are you saving Excel file as comma separated values (.CSV)? Excel has a bug that drops trailing null values. For instance, a CSV file with 10 columns containing values in all 10 columns will export to CSV correctly, but if only the first 5 columns in a row have data, the file will export with only the five values and it will not include the 5 trailing commas (",,,,,") required at the end of the data to indicate blank column values. To make it more confusing, this only occurs on row 17 or greater; the first 17 rows will export to CSV correctly, while the remaining rows do not include the training null columns, so if you look at just the first few rows everything appears normal. Why MS continues to ignore this known defect is a mystery. 1  out of 1 found this valuable. Do you?    

ChrisStuart
2. RE: Loading data from excel sheet
Jun 10 2009, 10:30 PM EDT | Post edited: Jun 10 2009, 10:30 PM EDT
Forgot to post workaround; In Excel, ensure empty trailing columns contain a blank value formula (="") before saving as .CSV 1  out of 1 found this valuable. Do you?    

yuridm
3. RE: Loading data from excel sheet
Jul 1 2009, 10:09 AM EDT | Post edited: Jul 1 2009, 10:17 AM EDT
Hello ChrisStuart,

Excel 2003 exports to csv without this error.
 Do you find this valuable?    

yuridm
4. RE: Loading data from excel sheet
Jul 1 2009, 10:15 AM EDT | Post edited: Jul 1 2009, 10:15 AM EDT
"trying to import data from excel sheet into a table for some reason table shows blank rows.
Any idea how to fix this problem or what could be the reason.
thanks"
Hello pthakar,

It very depends of details. Do you find this valuable?    
luciofer
luciofer
5. RE: Loading data from excel sheet
Jul 1 2009, 11:32 AM EDT | Post edited: Jul 1 2009, 11:32 AM EDT
Hi guys,

I have Excel 2003 (SP3) and it doesn't suffer from the trailing nulls bug, at least doing a simple test.
Apart from that, pthakar mentions empty rows, not empty columns, so how is he importing the csv, using a script, external tables or any other tool? Maybe the problem is there. For example, Excel leaves line spaces when he finds empty rows in the spreadsheet.

I guess our friend needs to add some information about how he is doing this task otherwise we may be guessing.

Cheers 1  out of 1 found this valuable. Do you?