Solved

Importing  a verly large CSV file into RDBMS

Posted on 2011-09-29
4
364 Views
Last Modified: 2012-06-21
I have a 4gb data file that has 4 million rows and 314 colums/fields. I am trying to import this either into a MYSQL or SQL SERVER dbase. I have tried using the Import/Export and MIgration tool kits on SQL server. I encounter an error each time, when i amtrying to import it. Usually In the sql server it is truncation errosrs and In MYSQL the error is  the length of the row is too large.

Any help in this matter is greatly appreciated.
0
Comment
Question by:m3mdicl
4 Comments
 
LVL 17

Expert Comment

by:Garry-G
Comment Utility
Length of the row in reference to a certain column? Have you checked the char sets used, as e.g. a string with the length of 10 utf-8 chars might be something like 30 chars long ...
Also, on the import, unless you use transactions, you should end up with "n" rows inserted, so you could check the CSV after those n lines and see if the next line is in fact malformed/defective ... maybe delete it, try importing the rest of the data, than manually fix the corrupt lines ...
On Unix, splitting the csv up into smaller packets, like e.g. 100k lines a piece, might also help getting it in ...
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
0
 
LVL 7

Accepted Solution

by:
luani earned 500 total points
Comment Utility
The first thing that comes in mind is to split the file......
0
 
LVL 1

Expert Comment

by:ob2s
Comment Utility
Hi,

Assuming there aren't fixed length rows in your data file, check if your import tool's expectation of line terminators matches what is actually used in the data file (e.g. \n vs \r\n).

If you're using mysqlimport, see the --lines-terminated-by option.

Hope this helps,.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now