1

I connect excel to pgsql

1.install driver at:

https://www.postgresql.org/ftp/odbc/versions/msi/

2.Add a System DSN:

enter image description here

This is my code get database:

Public Function setDBConnectionPgsql() As Object
    
    Set setDBConnectionPgsql = CreateObject("ADODB.Connection")
    setDBConnectionPgsql.Open "DSN=PostgreSQL;Server=192.168.1.10;Port=5434;UserId=postgres;Password=123456;Database=test;"
End Function

Private Sub CommandButton1_Click()
    Dim adoCn As Object
    Dim dbRes As Object
    
    Set adoCn = setDBConnectionPgsql()
    Set dbRes = CreateObject("ADODB.Recordset")
   
    dbRes.Open "SELECT * FROM mst_user", adoCn, 1, 2
    Dim iRow As Integer
    iRow = 1
    Do While dbRes.EOF = False
      ActiveSheet.Rows(iRow).Cells(1).Value = dbRes("user_id")
      ActiveSheet.Rows(iRow).Cells(2).Value = dbRes("user_name")
      iRow = iRow + 1
      dbRes.moveNext
    Loop
End Sub

Database is: "理宏"

But Result Excel display :逅・ｮ・

If Text is English, it is ok.

How can get String UTF8 when connect from Excel to Postgresql?

Community
  • 1
  • 1
D T
  • 3,522
  • 7
  • 45
  • 89
  • First, VBA always used Unicode for strings, just like VB6. If you have problems either the data *isn't* stored using the UTF8 encoding or/and the driver fails to translate the data to Unicode. – Panagiotis Kanavos May 08 '18 at 07:43
  • Second, why are you using such code at all? Excel already allows you to load data from the `Data` tab and transform it. There's even a menu choice to load from PostgresSQL, at least in Excel 2016 – Panagiotis Kanavos May 08 '18 at 07:44
  • refer https://stackoverflow.com/questions/13230456/connecting-excel-to-postgresql-via-vba – D T May 08 '18 at 07:46
  • The last comment doesn't explain anything. Again, why write any code when you can go `Data > New Query > From Database > From PostgresSQL Database` ? You don't need VBA to read data from a database. You can't even *use* VBA in an `xlsx` file, while you can use a data connection just fine – Panagiotis Kanavos May 08 '18 at 07:47
  • Check [Connect to a PostgreSQL database](https://support.office.com/en-us/article/connect-to-a-postgresql-database-power-query-bf941e52-066f-4911-a41f-2493c39e69e4?ui=en-US&rs=en-US&ad=US#ID0EAADAAA=2016) in Excel's documentation. As the docs explain, you need to install [Npgsql](https://github.com/npgsql/Npgsql/releases) first – Panagiotis Kanavos May 08 '18 at 07:51
  • Win 10: Excel 64bit .i had install https://github.com/npgsql/npgsql/releases/tag/v3.2.7, but it still show message [This connector requires...] – D T May 08 '18 at 08:09
  • i try on Win 7: Excel 32 bit: it requires IE 9 – D T May 08 '18 at 08:11
  • Excel doesn't require IE. The OS doesn't matter, the *Excel* version matters. 2010, 2013, 2016? – Panagiotis Kanavos May 08 '18 at 08:13
  • i using excel 2016, – D T May 08 '18 at 08:15
  • Did you select to install in the GAC? I just installed the same Npgsql version in the GAC and the connection dialog in Excel 2016 opens just fine. The installer's warning that Npgsql should be added through NuGet is for *development* projects. Excel will look for any assemblies it needs in the GAC – Panagiotis Kanavos May 08 '18 at 08:16
  • What is the GAC? – D T May 08 '18 at 08:17
  • Just install npgsql with all options checked – Panagiotis Kanavos May 08 '18 at 08:20
  • Win 10 had ok, Win 7 & IE8 can't: https://social.technet.microsoft.com/Forums/ie/en-US/526eac45-af1b-4ebe-a819-560f83999e6e/need-to-install-power-query-with-internet-explorer-8?forum=excel – D T May 08 '18 at 08:32
  • Which doesn't matter. Not only because Windows 7 and IE 8 are no longer supported but because you should have nothing less than IE 11 to get security patches, the latest TLS version etc. *Especially* in a corporate environment (just in case some manager says you can't upgrade). In fact, even on Windows 7 you can't have IE 8 unless you disable updates – Panagiotis Kanavos May 08 '18 at 09:04

1 Answers1

1

The cause: i had install psqlodbc_07. i had fixed by install psqlodbc_10. it is ok.

D T
  • 3,522
  • 7
  • 45
  • 89