How to export fields as text

The export can’t easily be modified.

Did you try changing the formatting of the fields in Excel after importing? For example, change the number field to a text field? If opening the CSV in Excel doesn’t change the underlying data, that should work. I’m not an Excel user, so I can’t be more specific.

As a person who uses Excel extensively; after opening the CSV file, select the column with zip codes, change the format <crt>1 (with a PC) -> then “number” -> “special” -> zip code … that will add a leading zero. Ditto with; <crt>1 (with a PC) -> then “number” -> “date” and select the format you prefer. (ditto with phone numbers, social security numbers, etc. etc.) … then “save as” an Excel file.

Thank you both. I did find that the actual data in the CSV is unaltered. But when I open the file in Excel (on a Mac) Excel automatically converts the data and alters it.

I did find that if I change the extension on the file from CSV to TXT and then open in Excel, I have an opportunity to change the formatting of the columns. It’s a little clunky and ideally I’d like to be able to just open the file — but I’d need to change Excel’s default behavior. Is that possible?

Hi –
There are many default options in Excel, however there is one that will format a particular column in a particular way. Like a number string to a date format.

One could build a Visual Basic Applications to automate the process in Excel, but for a simple re-formatting of a column, it is not worth the effort.

One must expect to need to do some massaging of the output of any database to any type of flat file (csv, text, excel, etc.) … Further, I’m not sure what you mean when you say that when you open a CSV is the content is altered, but you change the extension to TXT and then opening, it isn’t. CSV is just a comma-separated text file. There should be no difference in the way it behaves and either file format, once opened in Excel can be reformatted. The data in each cell will be the same, even if the format of that data isn’t.

BTW — one thing that might save you some time … if the exported data is always the same set of pdb fields in the same sequence you could export and format it once. Then save it as a file called “template” or something. The next time you export data, you open it in Excel, then cut/paste the entire exported set into the ‘template’ file. You may still need to do format things like zip & date columns, but you would not have to reformat for column widths, etc AND … when you cut/paste the new data, if you leave one row at the top of the template file with the formatting you like, you can paint-brush that formatting to all the rows and then delete that first row that was retained just for the formatting.

Note that any field with more than 256 characters may need to be additionally cut/pasted as the data may otherwise be truncated.

Thanks David, that’s helpful.

Just to clarify:

If I select File > Open and choose a .csv file, Excel opens the file, but reformats the data (drops leading zeros, changes dates to 2-figure years).

If I change the file extension to .txt and do the same, Excel walks me through the text import wizard and I have an opportunity to preserve the data format as text.

As you note, it’s the same text file, but Excel treats them differently. Maybe a Mac thing?

Anyway, I can work around it and the template idea will help.

Thanks again.

I think there is another way to address this…

1. Open a new empty sheet in Excel
2. Under Data tab, select “Text file ”
3. then select your csv file
4. it will offer all the formatting options…

Yes .. there are many ways to bring an exported text file into Excel. Excel is operating system neutral, except for keyboard things like <ctl> vs <cmd>.
//