Error in importing large excel files

Toad Data Point
1

Hi all,

I am trying to connect to an .xlsx file using toad data point 3.3 version. but it is throwing an different kinds of error for files above 60mb. I have attached the images of the erros it is showing. can anyone please help

2

any suggestions people??

3

I apologize for the wait. Can you try converting the file to csv then connect to the csv file?

4

I can’t import huge files in .csv as well. Also, I want to use aggregate functions on the data which is not possible if the file is in .csv format

5

Hm, there is a resource issue here, but we do not really know what resource limit is being exceeded.

Could you provide us with a couple of sample rows from your data? Just enough to see what columns we are working with. Also, within that Excel file, are there multiple datasets, graphics, pivot tables, etc?

You also mention that you ‘can’t import huge files’. Can you clarify this? Did you mean that when you opened the file in Excel and tried to save as csv, it errored? Or did it save to csv but give the same errors when you tried to connect?

6

I saved the same files in csv(I didn’t faced any problem in that) and I tried connecting but still it was throwing the same errors.

And by huge I meam files with sizes exceeding 70MB (irrespective of number of rows)

I don’t have any multiple datasets, graphics or pivot tables or anything else within that excel. it is just a plain excel file

Some sample data -

Total_Rev_Disc_USD
Total_Margin_USD
Transaction_Date
System_Qty
Local_Channel
0.16
0.16
25-Apr-13
0
5
-15765.68
-477.82
26-Jun-12
-28
8
131.25
131.25
30-Nov-12
0
5
4400
1897.695
18-Mar-13
0
5
1832.97
441.6
7-Nov-12
3
5
1347.74
402.456
30-Oct-12
1
5
1362.74
308.377
24-Jan-13
1
8
119.62
119.62
23-Oct-12
0
8
69.62
69.62
19-Jun-12
0
5
7629.18
1520.976
12-Mar-13
6
5

7

We have confirmed that there is an issue with connecting to large Excel files, and created issue QAT-668 to look into it.

8

Is the issue just with large Excel files? I get a corrupt file error when just trying to write to an Excel file worksheet that has multiple worksheets, but not over 2mb?

9

Hi,

Did this ever get resolved? I too am having issue with large data sets in excel in combination with using the import wizard. Please advise if you have an update to the issue QAT-668

10

This is still open but let me have someone look at it this week.

11

We looked into this. Seems this an issue in the OCBC driver with Excel 2013. There are many posts of other apps/developers running into the issue. We tried all of the suggestions and none solve the issue. Looks like we will have to wait for Microsoft to fix.

http://answers.microsoft.com/en-us/office/forum/office_2007-customize/system-resource-exceeded-while-opening-ado/ffea9d80-36b3-421f-bcc1-e117724b757c

http://sqlanywhere-forum.sap.com/questions/1694/microsoftodbc-excel-driver-system-resource-exceeded

As a work around covert you file to csv. You can connect and use aggregate functions with this type of file. We had no issues using this method.