Import a CSV file into the Postgres database using the Copy command

1. Save Excel as a CSV

2. Open CSV format with TXT

3. Encoded in UTF-8 format, save as Blacklist20141231.csv

4. Create a table

Create table backlist_20141231 (date varchar (20), mobile number nvarchar (20), channel nvarchar (50));

5. Using the Copy command, copy backlist_20141231 from ' D:/blacklist20141231.csv ' delimiter as ', ' csv quote as ';

This error, is not carefully caused, from ' D:/blacklist20141231.cs ' should be from ' d:/blacklist20141231.csv '; change it.

6. Now the encoding format or GBK Oh, query under the imported data

The place of Chinese is blank oh.

7. Now set the client_encoding to Unicode is OK.

8. This proves that the blacklist20141231.csv can be imported to Postgres using the Copy command. I've had a lot of mistakes before, and I'm going to simulate a few of them right now.

9. We re-build the table backlist20141231_er1 and reproduce a copy of blacklist20141231.csv for Blacklist20141231_1.csv

Create table Backlist20141231_er1 (date dates, cell phone number varchar (20), channel varchar (50));

Copy backlist20141231_er1 from ' D:/blacklist20141231_1.csv ' delimiter as ', ' csv quote as ' ";

SQL Status: 22007

What's the situation?

You can see from the 7th step that the first line of records is "date", "Mobile number", "channel", but the date type of my created table backlist20141231_er1 is dated, which cannot be implicitly converted. Let's get the first line out of the CSV and try again.

Set Client_encoding=unicode;

SELECT * from Backlist20141231_er1

Limit 10;

10. We still take blacklist20141231_1.csv (after deleting the first record). The phone number should be 11 bits, and we'll create a table and set the phone number to varchar (11) to try.

Create table Backlist20141231_er2 (date dates, cell phone number varchar (11), Channel varchar (50));

Or 22007, the length of the varchar type of mobile phone number can be extended, my Excel mobile phone number is not regular.

11. Most of the day, in fact encountered a lot of errors, many times have no clue, occasionally see a little light, slowly toward the dawn that close.

There's one more question you don't know why? Client_encoding is sometimes Unicode, sometimes UTF-8, clearly set is Unicode, inadvertently again see unexpectedly became UTF-8.

Import a CSV file into the Postgres database using the Copy command