I tried creating a temp table, with one column, and then importing the CSV but it doesn't seem to import it. However, I can't find any information on if/how you import a CSV in to a temporary table (or, alternatively, some way of importing a dynamic CSV - the first row has column headers in the CSV). So my plan is to import it in to a temporary table, manipulate it, and then insert the data. I also need to manipulate the data before inserting it in to a permanent table. After executing the import statement I receive the values 4 and 2. | 13:54:37 | Unknown | outgoing call | 14772580369 | 1 | NULL | NULL | NULL | NULL | NULL | 0.0 | 0.0 |Īnd finally in php assigning a query string to $sql variable should look like this $sql = "LOAD DATA INFILE 'detection.I have a fairly sizable CSV file that can change from month-to-month, and has roughly 450 data points per row. csv-Data: intRow deciRow 4 2.43 The sql data fields are formatted as INT(11) and DECIMAL(11). | date | name | type | number | duration | addr | pin | city | state | country | lat | log | Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 > number = TRIM(BOTH '\'' FROM duration = 1 * TRIM(TRAILING 'Secs' FROM addr = 'null'), Mysql> LOAD DATA INFILE '/tmp/detection.csv' Number = TRIM(BOTH '\'' FROM = 1 * TRIM(TRAILING 'Secs' FROM = 'null'),īelow is the result of executing the query on my machine That being said a useful version of the statement should look something like this LOAD DATA INFILE 'detection.csv' If duration is always in seconds then you can extract an integer value of seconds and store it that way in your table to be able to easily aggregate duration values later. You most likely want to change "null" string literal to actual NULL for addr, pin, city, state, country columns You have to deal with single qoutes around values in Number field Incorrect datetime value: 'Sep-18-2013 01:53:45 PM' for column 'date' at row If date in your table is of datetime data type then it needs to be transformed, otherwise you'll get an error Now IMHO you need to transform quite a few fields while you load them: (date, name, type, number, duration, addr, pin, city, state, country, lat, log) That being said a syntacticly correct statement might look like this LOAD DATA INFILE 'detection.csv' But if you feel like you need it use it like this ESCAPED BY '\\'. Looking at your sample rows IMHO you don't need ESCAPED BY. The only thing Ive been able to get to work is stripping the time zone off in the csv file before importing and using. There are date fields in the import file that are in the format 02:49 PM America/New York. You have to deal with Number field in a different way. I’m loading data into a MySQL table with PHP using LOAD DATA LOCAL INFILE. You can't use ENCLOSED BY clause more than once. If you won't do that you'll skip not only first line but also second one that contains data. They only add clutter.Īt the end of the very first line of your CSV file you have to have, because you use them as part of a line delimiter. In fact in your case, since there are no reserved words used, you ditch them all. There is absolutely no need to call mysql_escape_string() to specify a delimiter in FIELDS TERMINATED BY and ENCLOSED BY and ESCAPED BY clauses. If you'd do echo($sql) before you execute it you'd see that syntax of your query is incorrect for following reasons:įilename should be enclosed in quotes rather than backticks because it's a string literal not an identifier. $res = nothing is inserted where is the mistake? "Sep-18-2013 01:54:37 PM","Unknown","outgoing call",'14772580369',"1 Secs","null","null","null","null","null",0.0,0.0,Īnd I'm using the following code to insert the data into database $sql = "LOAD DATA INFILE `detection.csv`įIELDS TERMINATED BY OPTIONALLY ENCLOSED BY OPTIONALLY ENCLOSED BY ESCAPED BY LINES TERMINATED BY `".",\\r But both your approaches are what I was looking for. I guess (as Greg said) you have a bit more power of the raw data from a temp table. "Sep-18-2013 01:54:14 PM","Unknown","outgoing call",'1234567890',"0 Secs","null","null","null","null","null",0.0,0.0, mysql> LOAD DATA LOCAL INFILE processed.csv mysql> INTO TABLE table mysql> FIELDS TERMINATED BY mysql> IGNORE 1 LINES Thanks Kevin This should work as well. At the moment I am using the following SQL command: LOAD DATA LOCAL INFILE filename. csv file data like that Date,Name,Call Type,Number,Duration,Address,PostalCode,City,State,Country,Latitude,Longitude I then need to skip the next two columns in the CSV file and then map column 11 of CSV file to column 9 of MySQL table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |