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

Eduardo Fuerte
Eduardo Fuerte used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
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.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Hi

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?
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Excel can read and write CSV files, so use it to inspect your data.
No need for any programming language / skills.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
I assume you 7sed the database import wizard of MSSQL - didn't it tell you about issues it found in the file?
Pratik SomaiyaApplication Development Associate

Commented:
Hi,

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

Regards,
Pratik
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Hi

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.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
The preview only looks at the first n lines of the file, so it didn't see the error line(s).
David FavorFractional CTO
Distinguished Expert 2018

Commented:
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.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
@David


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


@Pratik
Could you give me more details on how to complete this task by using SSIS?
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
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.
Pratik SomaiyaApplication Development Associate

Commented:
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

Regards,
Pratik
David FavorFractional CTO
Distinguished Expert 2018

Commented:
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.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Hi

My solution is:


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

2. Then I used dbForge for MSSQLServer to import
(even without correcting pass 1) everithing goes fine...
 
img002
Developer and Analyst
Commented:
Accordingly to what I posted. I get everithing Ok.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial