Importing a LARGE csv file. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Importing a LARGE csv file.

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2012-06-27 : 06:40:29

I have a LARGE csv file that I need to import, it has 1125 columns in it.

I have read up on and created a sparse table to accomodate it as it seems this is the way to go for tables with over the limit of 810 columns (I think it's around that figure, but I know i exceed the limit),

-------------------------
CREATE TABLE [dbo].[MySparsetable](
[field 1] [varchar](29) SPARSE NULL,
[field 2] [varchar](14) SPARSE NULL,
[field 3] [varchar](24) SPARSE NULL,
...........................
[field 1125] [varchar](14) SPARSE NULL
[cs] [xml] COLUMN_SET FOR ALL_SPARSE_COLUMNS NULL
) ON [PRIMARY]
------------------------

I have also created a format file for the import routine.

I have tried the following:-

-----------------------------------------------------------------

INSERT [mydb].[dbo].[MySparsetable]
(
[field 1],
[field 2],
[field 3], ..........[field 1025]
)
SELECT
field1,
field2
field3, ........... field1025
FROM OPENROWSET(BULK 'C:\DFiles\Imports\myimport.csv',
FORMATFILE='C:\DFiles\Imports\myformatfile.fmt' ) AS t1;

--------------------------------------------------------------------

But I get the following error:-

Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 12580 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.

-----------------------------------------------------

Now i have tried to import to a cut down version of the above by using just the first 200 columns of the csv and creating a table and format file to the same size and that imports the data OK, and i can read from the table (of 200 columns) OK as well.

But using a full size of 1125 columns as above fails!

Am I doing something wrong? If so, can someone point me in the right direction please?
Or is there some other way I can import a file that is this large (1125 columns)?

Please help as I have a deadline coming up next month to import this data, i have been working on this for weeks now (with no success on the full file).

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources