Link to home
Start Free TrialLog in
Avatar of Eduardo Fuerte
Eduardo FuerteFlag for Brazil

asked on

Could you point a tool to read .CSV files and insert on a MSSQLServer table?

Hi Experts

Could you point a tool to read .CSV files and insert on a MSSQLServer table?

The SQL Management Studio goes correctly but at certain point abends with the data.

Thanks in advance.
Avatar of David Favor
David Favor
Flag of United States of America image

This likely means your .csv file really is corrupt.

Simple way to fix this is to use a module like PERL's Text::CSV_XS, to quickly import a .csv file + then export it with all problems corrected.

And, keep in mind, this will likely gloss over all real problems with your data.

If data is corrupt, likely best to actually find + fix all broken records.

PERL's Text::CSV_XS can help with this, by reporting errors for any broken records.

Just ingest your .csv file + hand edit/fix any problem records, then likely your MSSQL import will work.
Avatar of Eduardo Fuerte



It seens to be a Perl's class to manipulate CSV(s), so it's needed to construct an Perl's app to could use it.
Isn't it?
Excel can read and write CSV files, so use it to inspect your data.
No need for any programming language / skills.
I assume you 7sed the database import wizard of MSSQL - didn't it tell you about issues it found in the file?

SSIS Packages can help you to load data from a CSV file to Database.


Yes, MSSQLServer give me some informations about column dimensions that doesn't make too much sense to me, so I changed the column dimensions,  in a hurry I didn't save that messages, something about nvarchar(100) is not a compatible dimension for the data, when the proccess had been running for some time. At the beggining the preview - the sample data is showed OK.

Some lines presents  deconfigurated characters - that I guess is corruption - that maybe is causing the error.
The preview only looks at the first n lines of the file, so it didn't see the error line(s).
You said, "Yes, MSSQLServer give me some informations about column dimensions"

1) Drop your entire dataset.

2) Reimport your .csv file.

3) Record line numbers of problems.

4) Hand edit your .csv file, fixing all records by line number produced in #3.

5) Once all records are fixed, repeat #1-#4 till you get a clean import.

3) Record line numbers of problems.
4) Hand edit your .csv file, fixing all records by line number produced in #3.

There is millions of lines, that would be impraticable

Could you give me more details on how to complete this task by using SSIS?
SSIS is (in its simplest form) what the Database Import/Export Wizard generates. If a file is the source, you can set up an error file containing the lines the import failed for.
If there is no general file corruption, you should be able to import the "ok" lines, and only have to reprocess those with a failure.
Hi Eduardo,

SSIS can import the CSV file into the database table. SSIS is SQL Server Integration Services, wherein you can create a package having your CSV file as a Flat File Source and the destination can be an OLE DB Destination where you can mention the table. You need to set the connection to the database using the Connection Manager. This is a simple process, you just need to drag and drop the source, destination and set the configurations and you are done!

You can also set an error log if any issue occurs while transferring the data.

You can find how to import a CSV into database table in below link:

Import CSV File to DB using SSIS

You said, "There is millions of lines, that would be impractical".

In this case you will...

1) Ingest your .csv data.

2) For every record imported which throws an error, you'll have a test to catch + repair the error.

3) Then have your script die on any uncaught error.

4) Each time you run your script, you'll find the next uncaught error + add in code to fix the error.

5) Eventually, you'll have a script which will import all your data, repair your millions of record errors, then output a valid/repaired .csv file.

Extremely simple code to write + might take a while to get all errors handled.

Or... you might find there are only a handful of common errors, which cover all your millions of records with the errors.

You'll just have to... get into it... to determine amount of code to write...

If you've never done this type of coding before, likely you can hire someone off Fiverr to write this code for you.

My solution is:

1. Open the .CSV at Notepad++ and configure it  with UTF-8
User generated image
The deconfigurated characters are imediatelly corrected.

2. Then I used dbForge for MSSQLServer to import
(even without correcting pass 1) everithing goes fine...
User generated image
Avatar of Eduardo Fuerte
Eduardo Fuerte
Flag of Brazil image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial