Thread: query tool csv/txt output file extension choice and encoding

Always naming the file as ".csv", regardless of the delimited used, was a bug that was fixed.

The problem with always naming a file as ".csv" is that Excel then blindly assumes that the file actually does contain comma-delimited fields, and bypasses the Import From Text Wizard, so if you use anything other than commas (e.g., you want TAB separators), then Excel won't split your file into multiple columns correctly.   Therefore, before the bug was fixed, you had to manually rename all of pgAdmin's "export to file" outputs to ".txt".  (Exactly what you don't want to have to do, too!)

Note, if your file is ends with ".txt", then simply doing a "File" | "Open" in Excel will automatically/correctly invoke the Import from Text Wizard, and let you pick the delimiter you want (and it does a good job of auto-detecting).  Easy!

And, if you don't want to use commas as your delimiter (for any number of reasons), then you can change that in pgAdmin by going to "File" | "Preferences" | "CVS/TXT Output" and choose your preferred delimiter (e.g., Tab, "|", etc.), and thereafter that's what pgAdmin will always use when you do an "export to file", but will give it an ".txt" extension so Excel won't screw up opening the file because it's expecting commas as delimiters.

Cheers,
Dave


On Fri, Feb 5, 2021 at 1:25 PM Alain Dupeyron <adupeyron@yahoo.fr> wrote:
>
> Hi,
>
> I'm using frequently the download file function in the query tool. In previous version of PgAdmin4, I select field separator ";"  and file was always generated with extension .csv and can be opened directly by Excel in one click with correct columns !
>
> Now I have always .txt extension (since 4.28 or before, I'm in PgAdmin 4.29 for now) but the .txt file do not open directly in excel
> I Have to launch excel and open it but colums are not detected in my French excel, I have to convert data...(idem with TAB separator)...fastidious !
> Or I have to save the file from the browser, then navigate to file, then I change its extension to .csv, confirm and then the file is opened correctly...fastidious !
>
> When I choose field separator "," in PgAdmin, then file is generated with extension .csv but colums are not detected in French Excel (comma is the decimal number separator in France...)
>
> => How can I "force" or choose in Pgadmin to always generate file with extension .csv independently of selected field separator ?
>
>
> 2nd point: Can we choose file encoding of generated file too ? it's in UTF-8 now (was plateform dependant in PgAdmin 3?)
> accentuated characters are not detected corretly by french excel (expecting by default my Windows-1252 plateform encoding), I have to import file in excel and to select the right utf8 encoding in a huge encoding list...
>
> Other approach : copy from query result then paste in excel and save the file myself...
> accent are ok but column header are missing, I need to select the option on each new query tab "copy with header" => there is no pgAdmin preferences for that point ? (was in PgAdmin 3)
>
>
> Yes we can blame Excel/Windows and csv format but it's the only download format proposed here  for spreadsheet tool and only theses 2-3 configurations are missing to parametrize it as expected by end user platform...
>
> Thanks

Dave, 

I concur with Alain.

On Fri, Feb 5, 2021 at 3:22 PM Dave Caughey <caugheyd@gmail.com> wrote:

Always naming the file as ".csv", regardless of the delimited used, was a bug that was fixed.

In my case it was a bug that was created.

 

The problem with always naming a file as ".csv" is that Excel then blindly assumes that the file actually does contain comma-delimited fields, and bypasses the Import From Text Wizard, so if you use anything other than commas (e.g., you want TAB separators), then Excel won't split your file into multiple columns correctly.   Therefore, before the bug was fixed, you had to manually rename all of pgAdmin's "export to file" outputs to ".txt".  (Exactly what you don't want to have to do, too!)

No, both Alain and myself *now* have to rename the .txt files to .csv files.  In my case it's because LibreOffice won't open it in Calc with a txt extension, but instead try to open it in Write.  If I leave it .txt I have to; manually start Calc, then select the file, then try to import it.  If it's named .csv a simple double-click opens the file in Calc and in the CSV import wizard that correctly handles files delimited by comma, tab, ~, |, ;, or anything else you like.

 

Note, if your file is ends with ".txt", then simply doing a "File" | "Open" in Excel will automatically/correctly invoke the Import from Text Wizard, and let you pick the delimiter you want (and it does a good job of auto-detecting).  Easy!

And, if you don't want to use commas as your delimiter (for any number of reasons), then you can change that in pgAdmin by going to "File" | "Preferences" | "CVS/TXT Output" and choose your preferred delimiter (e.g., Tab, "|", etc.), and thereafter that's what pgAdmin will always use when you do an "export to file", but will give it an ".txt" extension so Excel won't screw up opening the file because it's expecting commas as delimiters.

Currently the *only* options pgAdmin4 gives me are; semicolon, comma, vertical bar, or tab.  I can apparently just type whatever I want, but that's hardly obvious.  And comma is a very bad choice for European countries where sixteen hundred point fifty-two would be written as 1600,52 as opposed to 1600.52 on this side of the pond.  You might be suffering from just a touch of Americentric bias.

   

Cheers,
Dave


On Fri, Feb 5, 2021 at 1:25 PM Alain Dupeyron <adupeyron@yahoo.fr> wrote:
>
> Hi,
>
> I'm using frequently the download file function in the query tool. In previous version of PgAdmin4, I select field separator ";"  and file was always generated with extension .csv and can be opened directly by Excel in one click with correct columns !
>
> Now I have always .txt extension (since 4.28 or before, I'm in PgAdmin 4.29 for now) but the .txt file do not open directly in excel
> I Have to launch excel and open it but colums are not detected in my French excel, I have to convert data...(idem with TAB separator)...fastidious !
> Or I have to save the file from the browser, then navigate to file, then I change its extension to .csv, confirm and then the file is opened correctly...fastidious !
>
> When I choose field separator "," in PgAdmin, then file is generated with extension .csv but colums are not detected in French Excel (comma is the decimal number separator in France...)
>
> => How can I "force" or choose in Pgadmin to always generate file with extension .csv independently of selected field separator ?
>
>
> 2nd point: Can we choose file encoding of generated file too ? it's in UTF-8 now (was plateform dependant in PgAdmin 3?)
> accentuated characters are not detected corretly by french excel (expecting by default my Windows-1252 plateform encoding), I have to import file in excel and to select the right utf8 encoding in a huge encoding list...
>
> Other approach : copy from query result then paste in excel and save the file myself...
> accent are ok but column header are missing, I need to select the option on each new query tab "copy with header" => there is no pgAdmin preferences for that point ? (was in PgAdmin 3)
>
>
> Yes we can blame Excel/Windows and csv format but it's the only download format proposed here  for spreadsheet tool and only theses 2-3 configurations are missing to parametrize it as expected by end user platform...
>
> Thanks

It's why adding configuration parameter in pgadmin for file extension could solve the problem for most of people.

And encoding in same time would complete the feature...

Le 6 févr. 2021 00:17, Dave Caughey <caugheyd@gmail.com> a écrit :

On Fri, Feb 5, 2021 at 3:39 PM richard coleman <rcoleman.ascentgl@gmail.com> wrote:

Dave, 

I concur with Alain.

On Fri, Feb 5, 2021 at 3:22 PM Dave Caughey <caugheyd@gmail.com> wrote:

Always naming the file as ".csv", regardless of the delimited used, was a bug that was fixed.

In my case it was a bug that was created.

 

The problem with always naming a file as ".csv" is that Excel then blindly assumes that the file actually does contain comma-delimited fields, and bypasses the Import From Text Wizard, so if you use anything other than commas (e.g., you want TAB separators), then Excel won't split your file into multiple columns correctly.   Therefore, before the bug was fixed, you had to manually rename all of pgAdmin's "export to file" outputs to ".txt".  (Exactly what you don't want to have to do, too!)

No, both Alain and myself *now* have to rename the .txt files to .csv files.  In my case it's because LibreOffice won't open it in Calc with a txt extension, but instead try to open it in Write.  If I leave it .txt I have to; manually start Calc, then select the file, then try to import it.  If it's named .csv a simple double-click opens the file in Calc and in the CSV import wizard that correctly handles files delimited by comma, tab, ~, |, ;, or anything else you like.

Yeah, that's definitely unfortunate, but I think accommodating Excel's lame behaviour far outweighs accommodating LibreOffice's lame behaviour, given the relative number of users of the two programs.

Clearly, this would be solvable by including a "default extension" (e.g., ".csv" or ".txt") that would probably make everyone happy, but it seems that simply going back to the old buggy behaviour would be worse for a lot more people than it would help.

 

Note, if your file is ends with ".txt", then simply doing a "File" | "Open" in Excel will automatically/correctly invoke the Import from Text Wizard, and let you pick the delimiter you want (and it does a good job of auto-detecting).  Easy!

And, if you don't want to use commas as your delimiter (for any number of reasons), then you can change that in pgAdmin by going to "File" | "Preferences" | "CVS/TXT Output" and choose your preferred delimiter (e.g., Tab, "|", etc.), and thereafter that's what pgAdmin will always use when you do an "export to file", but will give it an ".txt" extension so Excel won't screw up opening the file because it's expecting commas as delimiters.

Currently the *only* options pgAdmin4 gives me are; semicolon, comma, vertical bar, or tab.  I can apparently just type whatever I want, but that's hardly obvious.  And comma is a very bad choice for European countries where sixteen hundred point fifty-two would be written as 1600,52 as opposed to 1600.52 on this side of the pond.  You might be suffering from just a touch of Americentric bias.

   

Cheers,
Dave


On Fri, Feb 5, 2021 at 1:25 PM Alain Dupeyron <adupeyron@yahoo.fr> wrote:
>
> Hi,
>
> I'm using frequently the download file function in the query tool. In previous version of PgAdmin4, I select field separator ";"  and file was always generated with extension .csv and can be opened directly by Excel in one click with correct columns !
>
> Now I have always .txt extension (since 4.28 or before, I'm in PgAdmin 4.29 for now) but the .txt file do not open directly in excel
> I Have to launch excel and open it but colums are not detected in my French excel, I have to convert data...(idem with TAB separator)...fastidious !
> Or I have to save the file from the browser, then navigate to file, then I change its extension to .csv, confirm and then the file is opened correctly...fastidious !
>
> When I choose field separator "," in PgAdmin, then file is generated with extension .csv but colums are not detected in French Excel (comma is the decimal number separator in France...)
>
> => How can I "force" or choose in Pgadmin to always generate file with extension .csv independently of selected field separator ?
>
>
> 2nd point: Can we choose file encoding of generated file too ? it's in UTF-8 now (was plateform dependant in PgAdmin 3?)
> accentuated characters are not detected corretly by french excel (expecting by default my Windows-1252 plateform encoding), I have to import file in excel and to select the right utf8 encoding in a huge encoding list...
>
> Other approach : copy from query result then paste in excel and save the file myself...
> accent are ok but column header are missing, I need to select the option on each new query tab "copy with header" => there is no pgAdmin preferences for that point ? (was in PgAdmin 3)
>
>
> Yes we can blame Excel/Windows and csv format but it's the only download format proposed here  for spreadsheet tool and only theses 2-3 configurations are missing to parametrize it as expected by end user platform...
>
> Thanks