• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1956
  • Last Modified:

Importing a large csv into sql

I have a csv file that has 6 million rows and around 330 columns.  I need to import these into an sql table.  I tried using bulk insert and I got an OLB file maxed error.  I also tried to populate a data table and insert the data table into sql and I ran out of memory.  Please help.

Thanks in advance.
0
rxresults
Asked:
rxresults
  • 5
  • 4
  • 3
  • +3
4 Solutions
 
AnujCommented:
use SSIS, or Import\Export wizard, they are faster although they really do a bulk insert.
0
 
AnujCommented:
Also, there other constraints like impact the performance like, the number of indexes in the table, because they also need to be updated.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
sfmnyCommented:
If you are using SQL Server Express your database size (not the CSV file size) cannot grow beyond 2gb. Given you've got a large amount of data, you should probably consider a setup where you can split the file and import them into multiple "databases" in Express or move to MySQL depending on your flexibility.

How large is your database file now?
0
 
rxresultsAuthor Commented:
I just created it so this is the first thing going into it.  I am trying some of the suggestions. Thank you all I will let you know as soon as I test everything.
0
 
AnujCommented:
@Sfmny Express edition 2008 supports up to 4GB and Express Edition 2008 R2 supports up to 10 GB,

But you can also do the following to improve performance, properly size before doing the data import, so that you can avoid auto growth. Also, enable instant file initialization for sql server service account to speedup auto file growth.
0
 
rxresultsAuthor Commented:
I am doing the business Intelligence suggestion but I am getting a "Cannot convert between unicode and non-unicode string data types" error  Any suggestions?
0
 
rxresultsAuthor Commented:
I have tried the Bulk insert and I am getting the following error:

Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes.

I also get the message I stated earlier when I use the business intelligence Development studio.  

Any clues?
0
 
ZberteocCommented:
I think you have LOB (BLOB) columns in your file that exceeds 2GB, the limit for SQL server for such columns. They can be documents, pictures etc. Check if you have that kind of columns.

You can also try to divide your file maybe you will have better success.
0
 
ZberteocCommented:
0
 
Scott PletcherSenior DBACommented:
>> "Cannot convert between unicode and non-unicode string data types" error  Any suggestions? <<

Change the columns in the table to nvarchar rather than varchar.

It's best to use a staging table to load data from the spreadsheet, then use a query to move data from the staging table into the real/final SQL table.

Naturally the staging table can be in tempdb (in fact, I would strongly recommend it, to save the primary db from needing twice as much space).



Btw, you don't want a LOB in this case.
0
 
rxresultsAuthor Commented:
I already have them set as nvarchar(500).  

I ran the package in the bussiness application and I get this messages as errors:


Error: 0xC02020A1 at Data Flow Task, Flat File Source [1]: Data conversion failed. The data conversion for column "Authorized Official Telephone Number" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task, Flat File Source [1]: The "output column "Authorized Official Telephone Number" (1967)" failed because truncation occurred, and the truncation row disposition on "output column "Authorized Official Telephone Number" (1967)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0202092 at Data Flow Task, Flat File Source [1]: An error occurred while processing file "C:\npidata.txt" on data row 2.
Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.
0
 
ZberteocCommented:
You will have to go into the properties of the columns for the destination in the database in the wizard and change them to match exactly the type and size in the file.

Starting with 2005 the import wizards do not accept maximum size and general type for the columns.
0
 
Scott PletcherSenior DBACommented:
Truncated means the table column was not long enough -- increase the length on the table (I'd just change them all to nvarchar(4000), the max w/o using max itself).

SQL doesn't know the exact length of all columns in the input so can't possibly require that you set that.
0
 
rxresultsAuthor Commented:
I changed everything to Nvarchar(4000) and still not working.  I am trying to upload the NPI file from the government. Maybe I should just write an app that copies it and puts it into my temp table.
0
 
ZberteocCommented:
You need to figure out why you have that LOB error. Is it because the file has LOB columns of because the format of the file is not read correctly by the importer. You need to figure that out. Making all the columns nvarchar(4000) won't solve the issue, as you saw. The column types and size has to match and the place where to do that is in the wizard where you can go to the columns property and change them. You have to find that as I can't tell you from the top of my head where is it.

I remember trying to import a simple and small file and I had to go back multiple times until I changed all the columns that were given as error at the execution step of the importer. The importer tells you what column and what the problem is. You don't kill that window but you go back and fix the columns.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 5
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now