BULK INSERT syntax MYSQL

Question

On MS SQL, I can do bulk insert using the sql command below:

BULK INSERT dbo.TweetJSON
    from ‘D:\file.txt’
    with (CODEPAGE=’65001’, FORMATFILE=’D:\BIFormatFile.txt’
    );

The BIFormatFile content is as follows:

9.0
1
1 SQLCHAR 0 8000 “\r\n” 1 [JSONData] “”

Now I want to do the same on MySQL but I can't seem to figure out how this works and what query to use..

So far the lines that I figured are:

LOAD DATA INFILE 'C:\File.txt'
INTO TABLE dbo.tweetjson
FIELDS TERMINATED BY '\r\n'

However i still have not figured out all the necessary codes to convert those MSSQL code to MYSQL. Please help..

The example of line from file.txt :

{"created_at":"Mon Sep 02 15:28:13 +0000 2013","id":374554364159225856,"id_str":"374554364159225856","text":"Smart watches: what are Apple, Samsung, Google and Microsoft up to? http:\/\/t.co\/bB3CS6xTDp","source":"\u003ca href=\"http:\/\/www.hootsuite.com\" rel=\"nofollow\"\u003eHootSuite\u003c\/a\u003e","truncated":false,"in_reply_to_status_id":null,"in_reply_to_status_id_str":null,"in_reply_to_user_id":null,"in_reply_to_user_id_str":null,"in_reply_to_screen_name":null,"user":{"id":707472469,"id_str":"707472469","name":"SoftWatchTeam","screen_name":"SoftWatchTeam","location":"Everywhere ","url":"http:\/\/softwatch.weebly.com","description":"All the latest technology news","protected":false,"followers_count":231,"friends_count":439,"listed_count":0,"created_at":"Fri Jul 20 17:04:18 +0000 2012","favourites_count":0,"utc_offset":7200,"time_zone":"Amsterdam","geo_enabled":false,"verified":false,"statuses_count":16798,"lang":"en","contributors_enabled":false,"is_translator":false,"profile_background_color":"C0DEED","profile_background_image_url":"http:\/\/a0.twimg.com\/images\/themes\/theme1\/bg.png","profile_background_image_url_https":"https:\/\/si0.twimg.com\/images\/themes\/theme1\/bg.png","profile_background_tile":false,"profile_image_url":"http:\/\/a0.twimg.com\/profile_images\/2624051650\/tyc2cafp0viuma2o8d5m_normal.png","profile_image_url_https":"https:\/\/si0.twimg.com\/profile_images\/2624051650\/tyc2cafp0viuma2o8d5m_normal.png","profile_link_color":"0084B4","profile_sidebar_border_color":"C0DEED","profile_sidebar_fill_color":"DDEEF6","profile_text_color":"333333","profile_use_background_image":true,"default_profile":true,"default_profile_image":false,"following":null,"follow_request_sent":null,"notifications":null},"geo":null,"coordinates":null,"place":null,"contributors":null,"retweet_count":0,"favorite_count":0,"entities":{"hashtags":[],"symbols":[],"urls":[{"url":"http:\/\/t.co\/bB3CS6xTDp","expanded_url":"http:\/\/htl.li\/2zA3iq","display_url":"htl.li\/2zA3iq","indices":[68,90]}],"user_mentions":[]},"favorited":false,"retweeted":false,"possibly_sensitive":false,"filter_level":"medium","lang":"en"}

If you using windows you will need to double backslash your directory separators, i.e 'C:\\File.txt'. Please elaborate on " I still haven't figured it all out" as it's unclear what your having issues with — AlexP
– AlexP, Commented Sep 10, 2013 at 14:44
Additionally an example of the data within File.txt may be helpful. The mention of JSONData indicates that it's not a straight forward CSV or something, in which case this may not be so straightforward — Simon at The Access Group
– Simon at The Access Group, Commented Sep 10, 2013 at 14:50
I mean, i havent found out all necessary codes to convert those MSSQL to MYSQL codes. I have updated the example line from file.txt... — elvirachrisanty
– elvirachrisanty, Commented Sep 10, 2013 at 14:55

Kermit · Accepted Answer · 2013-09-10 15:03:37Z

1

You need to first convert the JSON into a readable file format.

JSON to CSV

Then you can use LOAD DATA INFILE .

answered Sep 10, 2013 at 15:03

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

elvirachrisanty Over a year ago

The json file is considered of millions of tweets and has a nested array too. I am afraid converting it to CSV will not work.

Kermit Over a year ago

You need to come up with a different solution then.

Collectives™ on Stack Overflow

BULK INSERT syntax MYSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related