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.
Eduardo FuerteDeveloper and AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
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 AnalystAuthor 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 LambertConsultingCommented:
Excel can read and write CSV files, so use it to inspect your data.
No need for any programming language / skills.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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 AssociateCommented:
Hi,

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

Regards,
Pratik
Eduardo FuerteDeveloper and AnalystAuthor 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 AdvisorCommented:
The preview only looks at the first n lines of the file, so it didn't see the error line(s).
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
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 AnalystAuthor 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 AdvisorCommented:
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 AssociateCommented:
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 FavorLinux/LXD/WordPress/Hosting SavantCommented:
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 AnalystAuthor 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
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Accordingly to what I posted. I get everithing Ok.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.