Avatar of Eduardo Fuerte
Eduardo Fuerte
Flag 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.
Microsoft OfficeMicrosoft SQL ServerMicrosoft Excel

Avatar of undefined
Last Comment
Eduardo Fuerte

8/22/2022 - Mon
David Favor

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 Fuerte

ASKER
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 Lambert

Excel can read and write CSV files, so use it to inspect your data.
No need for any programming language / skills.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Qlemo

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

Hi,

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

Regards,
Pratik
Eduardo Fuerte

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Qlemo

The preview only looks at the first n lines of the file, so it didn't see the error line(s).
David Favor

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 Fuerte

ASKER
@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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Qlemo

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 Somaiya

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 Favor

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Eduardo Fuerte

ASKER
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
ASKER CERTIFIED SOLUTION
Eduardo Fuerte

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question