0 rows from the .csv (semi colon) are imported into MySQL Workbench

Question

I am trying to import the .csv below (columns and 3 first records in txt in differenet blockquotes) into MySQL Workbench using the Table Data Import Wizard. I have tried many ways and realized that the issue lies with this particular .csv. I even used the LOAD DATA LOCAL INFILE statement with no success using the terminal but things get complicated dipslaying different errors.

LOAD DATA LOCAL INFILE "C:\Users\user\Documents\Edu_research\SQL\Project_TED\talks_final_effort_new1.csv" INTO TABLE talks FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '/n' IGNORE 1 ROWS;

At first I used delimiter ',' and strings enclosed with '"' but everything was messed up, probably because of a column with long text with commas and double quotes etc., and it could not identify the columns. So I changed the delimiter to ';' but the same happened.

Then I kept the ';'. I tried for the 1st record and it was imported but for the rest of the records it didn't. I broke it down to 3 records (displayed below) used the settings below

and it showed this message again

I believe the "enclosed by" setting with '"' is the problematic beacuse there are some """ in the description column. I am not sure if I can deal with it not manually neither using TABLE IMPORT WIZARD or LOAD statement.

talk_id;title;speaker_1;all_speakers;occupations;about_speakers;views;recorded_date;published_date;event;native_lang;available_lang;comments;duration;topics;related_talks;url;description

1;Averting the climate crisis;Al Gore;{0: 'Al Gore'};{0: ['climate advocate']};{0: 'Nobel Laureate Al Gore focused the world’s attention on the global climate crisis. Now he’s showing us how we’re moving towards real solutions.\r\n'};3523392;2/25/2006;6/27/2006;TED2006;en;"['ar', 'bg', 'cs', 'de', 'el', 'en', 'es', 'fa', 'fr', 'fr-ca', 'gl', 'gu', 'he', 'hi', 'hr', 'hu', 'id', 'it', 'ja', 'ko', 'lt', 'lv', 'mk', 'nl', 'pl', 'pt', 'pt-br', 'ro', 'ru', 'sk', 'sl', 'sq', 'sr', 'sv', 'sw', 'th', 'tl', 'tr', 'uk', 'ur', 'vi', 'zh-cn', 'zh-tw']";272;977;"['alternative energy', 'cars', 'climate change', 'culture', 'environment', 'global issues', 'science', 'sustainability', 'technology']";"{243: 'New thinking on the climate crisis', 547: 'The business logic of sustainability', 2093: 'The state of the climate — and what we might do about it', 54715: 'How we can turn the tide on climate', 29968: 'The most important thing you can do to fight climate change: talk about it', 2339: ""Climate change is happening. Here's how we adapt""}";https://www.ted.com/talks/al_gore_averting_the_climate_crisis/;"With the same humor and humanity he exuded in ""An Inconvenient Truth,"" Al Gore spells out 15 ways that individuals can address climate change immediately, from buying a hybrid to inventing a new, hotter brand name for global warming." 92;The best stats you've ever seen;Hans Rosling;{0: 'Hans Rosling'};{0: ['global health expert; data visionary']};"{0: 'In Hans Rosling’s hands, data sings. Global trends in health and economics come to vivid life. And the big picture of global development -- with some surprisingly good news -- snaps into sharp focus.'}";14501685;2/22/2006;6/27/2006;TED2006;en;"['ar', 'az', 'bg', 'bn', 'bs', 'cs', 'da', 'de', 'el', 'en', 'es', 'et', 'fa', 'fi', 'fr', 'he', 'hi', 'hr', 'hu', 'id', 'is', 'it', 'ja', 'kn', 'ko', 'ku', 'lv', 'mk', 'ml', 'mn', 'nl', 'pl', 'pt', 'pt-br', 'ro', 'ru', 'sk', 'sr', 'sv', 'sw', 'ta', 'te', 'th', 'tr', 'uk', 'ur', 'vi', 'zh-cn', 'zh-tw']";628;1190;"['Africa', 'Asia', 'Google', 'demo', 'economics', 'global issues', 'health', 'statistics', 'global development', 'visualizations', 'math']";"{2056: ""Own your body's data"", 2296: 'A visual history of human knowledge', 620: 'Let my dataset change your mindset', 2806: ""Doesn't everyone deserve a chance at a good life?"", 2560: 'How Africa can keep rising', 1418: ""Let's put birth control back on the agenda""}";https://www.ted.com/talks/hans_rosling_the_best_stats_you_ve_ever_seen/;"You've never seen data presented like this. With the drama and urgency of a sportscaster, statistics guru Hans Rosling debunks myths about the so-called ""developing world.""" 7;Simplicity sells;David Pogue;{0: 'David Pogue'};{0: ['technology columnist']};"{0: 'David Pogue is the personal technology columnist for the New York Times and a tech correspondent for CBS News. He's also one of the world's bestselling how-to authors, with titles in the For Dummies series and his own line of ""Missing Manual"" books. '}";1920832;2/24/2006;6/27/2006;TED2006;en;"['ar', 'bg', 'de', 'el', 'en', 'es', 'fa', 'fr', 'he', 'hr', 'hu', 'it', 'ja', 'ko', 'nb', 'nl', 'pl', 'pt', 'pt-br', 'ro', 'ru', 'sr', 'tr', 'vi', 'zh-cn', 'zh-tw']";124;1286;"['computers', 'entertainment', 'interface design', 'media', 'music', 'performance', 'simplicity', 'software', 'technology']";"{1725: '10 top time-saving tech tips', 2274: 'The first secret of design is ... noticing', 172: 'Designing for simplicity', 2664: 'Meet the inventor of the electronic spreadsheet', 2464: 'The mind behind Linux', 1347: 'The secret structure of great talks'}";https://www.ted.com/talks/david_pogue_simplicity_sells/;"New York Times columnist David Pogue takes aim at technology’s worst interface-design offenders, and provides encouraging examples of products that get it right. To funny things up, he bursts into song."

This .csv is a real trouble. I believe I have searched any possible link on the Internet to figure it out but still no success. What could be the problem here?

I

The only method i haven't used is via phpmyadmin but I don't think that the issue lies with the method/tool.

Appreciate your help

Thanks

Answer 1

Scraping data requires programming.

That looks like a mixture of CSV and JSON. I think you will have to write code to parse the lines and do INSERTs.

While writing the code, decide which things should be left in JSON (complex and/or slow) to search on versus things that should be broken out into other columns or (more likely) other tables.

My request for SHOW CREATE TABLE was so I could see what column definitions you had for the data. I now see that it is not a simple CSV.

Also, what version of MySQL/MariaDB are you using? Are you sure about just \n for line termination? It might be \r\n.

The ignored first row might be interesting; it contains the names they have for the columns. (They don't have to match your column names in the CREATE TABLE.)