1
votes

Hello Stack Overflowers! I'm currently exporting a Postgres table as a .csv using a C# application I developed. I'm able to export them no problem with the following command...

set PGPASSWORD=password
psql -U USERNAME Database_Name
\copy (SELECT * FROM table1) TO C:\xyz\exportfile.csv CSV DELIMITER ',' HEADER;

The problem I am running into is the .csv is meant to be used with Tableau, however, when importing to excel I run into the same issue. It turns text fields into integers in both Tableau and Excel. This causes issues specifically on joining serial numbers on the Tableau side.

I know I can change these fields in Tableau/Excel manually but I am trying to find a way to make sure the end-user wouldn't need to do this. I'd like for them to just drag and drop the updated .csv postgresql data extracts and be able to start Tableau no problem. They don't seem real tech-savvy. I know you can connect Tableau directly to Postgres but in this particular case, I am not allowed to due to limitations beyond my control.

I'm using PostgreSQL 12 and Tableau v2019.4.0

EDIT: As request providing example data! Both of the fields are TEXT inside of PostgreSQL but the export doesn't specify.

Excel Formatting
ASSETNUM,ITEMNUM
1834,8.11234E+12
1835,8.11234E+12

Notepad Formatting
ASSETNUM,ITEMNUM
1834,8112345673294
1835,8112345673295

Note: If you select the specific cell in Excel it shows the full number.

1
Can you show a sample line of your CSV, no need for it to have actual real data, just make sure the data TYPES are accurate. You may need to make your query to output varchar fields that contains only numbers to be exported as text - Jorge Campos
Side note: maybe the title isn't so gud. - Stockster
Yeah should only take a few minutes. - Stockster
Check here superuser.com/q/318420 this thread may share some light into your problem. - Jorge Campos
@JorgeCampos that does clear things up. But, my understanding is I'd have to change the record values to ="number" for it to export correctly? - Stockster

1 Answers

0
votes

CSV files don't have any type information, so programs like Excel/Tableau are free to interpret the data how they like.

However, @JorgeCampos's link provides useful information. For example

"=""123""","=""123"""

gets interpreted differently than

123,123

when you load it into Excel.

If you want to add quotes to your data, the easiest way is to use PostgreSQL's string functions, e.g.

SELECT '"=""' || my_column || '"""' FROM my_database