.csv file into mysql

Question

The server I'm using does not allow me to use LOAD DATA INFILE or even save a file as a .txt file. I have a .csv file that is just one column, with a name in each row. I want to insert this data into the name column of a table named people with name as one column and location as the other… where the locations will be updated at a later time.

It's a weird way to do this. But I need to do it this way and not using those previous commands.

Does anybody have any ideas? This has been giving me a problem for many hours. I can't figure out how to load the csv into my table column without using those previously mentioned methods that I can't use on my server.

http://dev.mysql.com/doc/refman/5.1/en/load-data.html May be help you http://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table — , Mar 18 '14 at 05:37
My server will not give me access to load data infile unfortunately. @Let'sCode — user3430837, Mar 18 '14 at 05:38
Build up individual INSERT commands? Use textpad will take all of about 60 seconds — Trent, Mar 18 '14 at 05:41
That was my goal but I can't access each element of the .csv individually… for some reason in my file manager when I go to edit the .csv file, all the entries appear in one line… however, when I view the .csv file from my browser, there appear to be line breaks after each item… yet I cannot run a loop to collect each item after the line breaks that appear to be there in the browser though not in the file editor @Trent — user3430837, Mar 18 '14 at 05:43
change the format of the file (either from UNIX -> DOS, or DOS -> UNIX)? — Trent, Mar 18 '14 at 05:45
$contents=preg_replace('/(\r\n|\r|\n)/s',"\n",$contents); -- this would generally change DOS files into unix files. Then you should be able to iterate over it - just make sure there is indeed dos carriage returns — Trent, Mar 18 '14 at 05:48
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/49915/discussion-between-trent-and-user3430837) — Trent, Mar 18 '14 at 05:52

score 1 · Accepted Answer · answered Mar 18 '14 at 06:06

Based on your issue and lack of general permissions you will have to do the following:

Replace the DOS carriage returns with unix new lines:

$contents=preg_replace('/(\r\n|\r|\n)/s',"\n",$contents);

Save the contents to the file, and then loop through each line, building an INSERT command that you execute to MySQL.

.csv file into mysql

1 Answers1