0

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

enter image description here

and it showed this message again

enter image description here

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

2
  • 1
    SHOW CREATE TABLE Show us the LOAD statement`. Check the syntax of the JSON string.
    – Rick James
    Apr 18 at 1:14
  • thank you @RickJames for your response. I have edited the question. SHOW CREATE TABLE does not show the rows Apr 24 at 11:21

1 Answer 1

0

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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.