I am attempting to load many CSV files of historical data to a PGSQL server with PHP. I noticed PHP provides a pg_copy_from()
function for loading local data into a remote server, but the exact format of the input is vague.
The documentation says it expects an array of rows, each row being a delimited string of the values in that row. PHP’s file()
function returns an array in a format as described, but feeding it into pg_copy_from() returns an error of:
$rows = file('path/to/data.csv', FILE_IGNORE_NEW_LINES); // Same result without ignore_new_lines pg_copy_from($db, 'dest_table', $rows, ','); 'PHP Warning: pg_copy_from(): Copy command failed: ERROR: invalid input syntax for integer: ""col_2""'
I checked the array, $rows
, and it’s of a format like this:
[index]=>""foo",3,7,"bar",500,"z""
Where all string values and the string itself are in double quotes. I assume it’s trying to load the entire string into the 1st column of my table, hence the error that col_2 is unable to load (cause there’s nothing to load).
Are my assumptions about the array wrong or does the delimited string need a different format (if so, how)?
Thanks for any help/tips!
Advertisement
Answer
As noted in comments by @Wolph – just remove first element of your $rows
array, because it contains headers. To remove first element of array array_shift
can be used without reversing the array:
$rows = file('path/to/data.csv', FILE_IGNORE_NEW_LINES); array_shift($rows); pg_copy_from($db, 'dest_table', $rows, ',');