Question about ETL Work on Python/in General

user_18311331 0
Viewed 59

Right now I have a challenge where I have to upload a CSV file into SQL but before I have to check if data is ok, and other stuff with Python. But watching by my own hand there are some rows that are wrong, so...

QUESTION:

How do you deal with, again, "wrong rows", but also well written and respecting the syntax of the SQL table? See this for example:

This is the CSV:

Jonathan; (empty, but is valid)
Nancy; She is a student in the MIT
Juan; Juan is a software developer in –
"Oracle since five years ago""; (also empty and valid, and yes it does have two "" at the end)
Gisselle; she works in Microsoft since last year etc...

And this is how it is persisted:

id (auto generated)   name (varchar, not null)             description (optional)
1                     Juan                                 Juan is a software developer in
2                     Oracle since five years ago          null

See? That part of the "Oracle since five years ago" is a continuation of Juan description which for some reason was in another row, but this technically for SQL is okay, but, as you can imagine, is not useful and totally wrong.

In this type of cases, you have to delete (or fix) the row by you own hand (or writing an specific code for that specific CSV)? There isn't any test or something than you can write in code for at least detect this issues or similar? And not only for Python, but with any other tool where you can do ETL work?

etl csv sql python
1 Answers

First of all, check and check again that your software is reading your .csv file correctly.

It is really easy to misread CSV files; they have all sorts of variant formats.

In your example I guess the string Juan is a software developer in Oracle since five years ago has, in your CSV file, a newline between in and Oracle. And, I guess that whole string is enclosed in " quotes.

The thing about CSV " quotes and newlines is this: A newline that appears within a quoted string is not the end of the CSV record. Rather it is part of that string.

Use a fully debugged CSV parsing package. Writing this code yourself will make you suffer. Don't try to reinvent this wheel. You will, instead, invent another flat tire. Seriously. Ask me how I know this sometime.

Here's how to track this down.

  • Load it into MS Excel, Libre Office Calc, or some other spreadsheet program and cast your eyeballs upon it.
  • Then cast your eyeballs upon the Python data that results from reading it into your program.
  • Are the rows and columns split up the same way in your program and the spreadsheet?
  • If not, you must get to the bottom of that problem.

If you use a good CSV reading package, its documentation will tell you how to configure it to use "Excel-compatible options." Do that. And I think your question will be answered.

For what it's worth the various database client programs usually offer "Import from CSV" tools. They will get this right, and you may not need a Python program at all.

user_2056080
Related