load data infile '/home/desktop/Documents/overseas. Sequel Ace can be started directly from MAMP PRO. Please enter this command to import the csv data to mysql tables. Sequel Ace (previously Sequel Pro) is a macOS application that helps you to administer MySQL databases. Need to connect mysql database in terminal. SQL Query: ( id int not null primary key auto_increment, Need to create columns related to csv file. Need to create table for overseas_trade_index. I added some information to the LOAD DATA statement that OpenOffice was smart enough to infer, and now it loads the correct number of records: LOAD DATA INFILE "/home/paul/clientdata.csv"īut still there are lots of completely NULL records, and none of the data that got loaded seems to be in the right place.Įxample file: Overseas_trade_index data CSV File I think it is comma delimited, and text is apparently delimited with doublequote. The clientdata.csv file contains 2593 lines, and 570 records. Since it is a native Mac OS X application, it can easily streamline your workflow, making it the ideal web. It offers direct, easy access to your MySQL Databases on both local and remote servers. I can load the file into OpenOffice without a problem. Sequel Pro is a fast, open source, easy-to-use database management application for macOS that allows you to work with MySQL and MariaDB databases. I think the problem is that the text blurbs contain more than one line, and MySQL is parsing the file as if each new line would correspond to one databazse row. Records: 2023 Deleted: 0 Skipped: 0 Warnings: 198256 Query OK, 2023 rows affected, 65535 warnings (0.08 sec) I tried to load data into the file: LOAD DATA INFILE '/home/paul/clientdata.csv' INTO TABLE CSVImport No constraints are on the table, and all the fields hold VARCHAR(256) values, except the columns which contain counts (represented by INT), yes/no (represented by BIT), prices (represented by DECIMAL), and text blurbs (represented by TEXT). The CSV contains 99 columns, so this was a hard enough task in itself: CREATE TABLE 'CSVImport' (id INT) ĪLTER TABLE CSVImport ADD COLUMN Title VARCHAR(256) ĪLTER TABLE CSVImport ADD COLUMN Company VARCHAR(256) ĪLTER TABLE CSVImport ADD COLUMN NumTickets VARCHAR(256) ĪLTER TABLE CSVImport Date49 ADD COLUMN Date49 VARCHAR(256) ĪLTER TABLE CSVImport Date50 ADD COLUMN Date50 VARCHAR(256) I created a table called 'CSVImport' that has one field for every column of the CSV file. I have an unnormalized events-diary CSV from a client that I'm trying to load into a MySQL table so that I can refactor into a sane format.
0 Comments
Leave a Reply. |