Importing certain columns from csv into mysql via command line

Question

I have a csv file and want to import part of it into my Mysql database. From this question, I tried this command in my Mysql command line

 load data local infile 'mycsv.csv’ into table mytable fields terminated by ',' enclosed by '"' lines terminated by '\n' (entity_ticker,harvested_at, entity_sector,entity_competitors,story_type,story_source,event,entity_sentiment,event_sentiment,event_relevance,entity_relevance,entity_industry)

Those in bracket is the columns I want to put in my table. The order is same to my table but not same to csv file.

And the mysql didn't do anything like this:

mysql>  load data local infile '2013first1000.csv’ into table news_dataset fields terminated by ',' enclosed by '"' lines terminated by '\n' (entity_ticker,harvested_at, entity_sector,entity_competitors,story_type,story_source,event,entity_sentiment,event_sentiment,event_relevance,entity_relevance,entity_industry)
    "> 
    "> 
    "> 
    ">

This is the first two row of my file opened in txt:

signal_id,story_id,story_group_id,new_story_group,story_group_count,source_id,author_id,story_type,story_source,templated_story_score,story_traffic,story_group_traffic_sum,story_group_exposure,entity_sentiment,event_sentiment,story_sentiment,story_group_sentiment_avg,story_group_sentiment_stdev,entity_name,entity_ticker,entity_exchange,entity_relevance,entity_country,entity_indices,entity_industry,entity_region,entity_sector,entity_competitors,entity_type,entity_composite_figi,entity_exch_code,entity_figi,entity_market_sector,entity_security_description,entity_security_type,entity_share_class_figi,entity_unique_id,entity_unique_id_fut_opt,entity_author_republish_score,entity_author_timeliness_score,entity_source_republish_score,entity_source_timeliness_score,event,event_group,event_relevance,event_author_republish_score,event_author_timeliness_score,event_source_republish_score,event_source_timeliness_score,event_impact_pct_change_avg,event_impact_pct_change_stdev,event_impact_pos,event_impact_neg,event_impact_gt_mu_add_sigma,event_impact_lt_mu_sub_sigma,event_impact_gt_mu_pos_add_sigma_pos,event_impact_lt_mu_neg_sub_sigma_neg,event_impact_gt_mu_pos_add_2sigma_pos,event_impact_lt_mu_neg_sub_2sigma_neg,event_impact_gt_1pct_pos,event_impact_lt_1pct_neg,overall_source_timeliness_score,overall_source_republish_score,overall_author_republish_score,overall_author_timeliness_score,harvested_at 5a431613914455535dd2f7e3,59a8c0cb631131661f7cca72,629355b4-5ef4-45ab-872a-9fd1809ca49d,f,552,hW9kPL6pMO0suREcwfIObD7SjwY=,A/mu8VqEc7agwge3hbjXgdFfDeA=,news,broadcastengineering.com,,11548,308325207322,high,31.2,33.35,31.2,36.9,38.9,Sony Corp Ord,SNE,NYSE,100,N/A,[],Consumer Electronics/Appliances,N/A,Consumer Non-Durables,"[""MSFT"",""IBM"",""INTC"",""4331"",""NOKIA"",""HPQ"",""DIS"",""MSI"",""AAPL"",""PHG""]",US_EQUITY,BBG000BT7ZK6,UN,BBG000BT81N7,Equity,SNE,ADR,BBG001S5W6H8,EQ0010136500009000,,0,70.2,0.9566,40.4852,Employment Actions - General,Employment Actions,50.8,,,0,32.5118,,,,,,,,,,,,,53.7511,0.53,0,64.2908,2013-08-01T00:00:03.000Z

Could you tell me how to do this?

Kedar Limaye · Accepted Answer · 2018-03-15T18:19:07.480

2

1) 'The order is same to my table but not same to csv file.'-Make the order same then whats the purpose of using csv import? use this-

load data local infile 'mycsv.csv' into table mytable fields terminated by ',' enclosed by ' ' lines terminated by '\n'

2) still want to ignore columns- From Mysql docs:

You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:

LOAD DATA INFILE 'file.txt'  
INTO TABLE t1 (column1, @dummy, column2, @dummy, column3);

3)to skip first row- use Ignore 1 lines

edited Mar 15 '18 at 18:19

answered Mar 15 '18 at 17:12

Kedar Limaye

1,041
8
15

What if I want to ignore some column in the csv? and should I ignore first row? – fourth Mar 15 '18 at 18:13
1

check the answer – Kedar Limaye Mar 15 '18 at 18:19

Paul Campbell · Answer 2 · 2018-03-15T18:58:02.113

1

From the documentation

You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match input fields with table columns.

In other words, your column list must match the order in the CSV file for this to work.

To ignore columns within the csv you would assign them to a variable in your column list and then not do any further processing on them. If your csv has 4 columns and you only want the first and third columns the column list would look like

(first_col, @dummy, third_col, @dummy)

In your case, you can copy the header line and change any column name you don't want into the word @dummy.

You'll also be wanting to add IGNORE 1 LINES directly before your column list so that you can skip over the first row with the column names.

edited Mar 15 '18 at 18:58

answered Mar 15 '18 at 17:51

Paul Campbell

1,906
2
12
19

What if I want to ignore some column in the csv? If the name in csv and table is different, which one should I use? – fourth Mar 15 '18 at 18:15
1

You should have followed the link I posted and you'd have seen that eventuality is covered directly below the part I quoted. You can skip over columns by assigning them to variables within your column list (the example gives @dummy as a suitable var name), you then just ignore them. I'll update my answer to make that clearer. – Paul Campbell Mar 15 '18 at 18:50

score 0 · Answer 3 · answered Dec 14 '20 at 09:06

0

Some times you may need to use 'COLUMNS TERMINATED BY' instead 'FIELDS TERMINATED BY'

answered Dec 14 '20 at 09:06

Jeyakeethan Geethan

79
2
7

Importing certain columns from csv into mysql via command line

3 Answers3