-2

I have a csv file which has 13 columns, each of these columns has lots and lots of data and what I need is to be able to extract this data from the .csv file into mysql database.

Firstly I need help with creating specific tables for each of the columns of the csv file, as I'm quite new with mysql and I wasn't too sure on what attributes to assign to each table.

Here is the structure of the csv file...

Columns pid, start_time, end_time, epoch_start, epoch_end, complete_title, media_type, masterband, service, brand_pid, is_clip, categories, tags

Data under columns p00547jm (pid), 1003394820 (start_time), 1003999620 (end_time), 2001-10-18T08:47:00 (epoch_start), 2001-10-25T08:47:00 (epoch_end), in_our_time:_democracy (complete_title), audio (media_type), bbc_radio_four (masterband), bbc_radio_four (service), b006qykl (brand_pid),0 (is_clip), [9100005:1:factual.9200041:2:arts_culture_and_the_media.9200055:2:history] (categories), [democracy.history.philosophy.plato.ancient_greece] (tags)

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Jason-D
  • 3
  • 1

2 Answers2

0

This will generate the create table query $1 will become the table name

[Create mysql table directly from CSV file using the CSV Storage engine?

#!/bin/sh
# pass in the file name as an argument: ./mktable filename.csv
echo "create table $1 ( "
head -1 $1 | sed -e 's/,/ varchar(255),\n/g'
echo " varchar(255) );"

Then you can read the file and put your insert logic in the while loop

[Import CSV into MYSQL but ignore header row //open the csv file for reading $handle = fopen($file_path, 'r');

// read the first line and ignore it
fgets($handle); 

while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) {
// do your thing
}
Community
  • 1
  • 1
James Buchert
  • 33
  • 5
0

Create your table first and then use

LOAD DATA LOCAL INFILE '/home/dummy/dummy.csv' INTO TABLE tablename  
FIELDS TERMINATED BY ',';

to insert the data from the file into your new table.

Also, make sure when you login into MySQL you use

mysql -uusername -ppassword --local-infile

As for creating a table:

We can give the syntax but we cannot write the whole query for you. You can try it yourself as you have the necessary data with you.

create table table_name (col1 datatype(size) NOT NULL primary key, 
col1......);
Krishnakumar
  • 725
  • 1
  • 6
  • 11
  • Hi Krishna, thank you for your reply! Just wondering, would only 1 table need to be created as I have 13 fields so I was assuming I would need a separate table for each of these...or would only 1 table need to be created, this is where I am struggling... – Jason-D Feb 28 '17 at 14:05
  • Does this solve your question? if so, you can close it by accepting this as your answer – Krishnakumar Feb 28 '17 at 14:07
  • @Jason-D Depends on what you need inside your DB. For this, you can create a single table with 13 column names and then do the insert – Krishnakumar Feb 28 '17 at 14:08
  • Thanks, I will try creating a single table first with 13 columns and then inserting the data – Jason-D Feb 28 '17 at 14:11
  • Great!!! once done, close this topic by accepting this as your answer – Krishnakumar Feb 28 '17 at 14:13
  • Would you be allowed to provide me with some tips and not the whole query? As some of the columns I'm insure what type of data is is for example I know pid is a primary key, and it is an INT, however there is certain columns I dont know... could you define the type for me please? – Jason-D Feb 28 '17 at 14:15
  • @Jason-D > There's no rule that primary key should be an INT. Your example says p00547jm (pid) > which is not an INT. It's usually difficult to create table without knowing what your actual requirement is. If you really don't care about the datatype and you just want whatever value you have in your file to be inserted into the table, then better declare all your columns as text or varchar. That should do the job. – Krishnakumar Feb 28 '17 at 14:18
  • @Jason-D Example: `create table abc (pid text NOT NULL primary key, start_time text, end_time text....)` – Krishnakumar Feb 28 '17 at 14:23