Exporting a Csv File with zeros as the first integer

[tillabong]

Hello. i have a column in my database which stores values starting with 0. i've used varchar to store it so the zero shows up. however everytime i backup my database table using CSV for microsoft excel, the file opens without the zero infront. Is there any way i can prevent this so that when i reupload my csv file the zero still appears infront?

Thanks.

 

[slacker_]

Use int instead of varchar if you're just storing numbers.

edit:
probably it's just an formatting issue in excel..

 

[tillabong]

if you use int the zeros infront will not appear

 

[dlukin]

Are you using them as numbers or as strings (ie like zip codes)?

 

[tillabong]

im storing them as strings. cause if i store as integers they wont register the 0 infront.

 

[dlukin]

Why do you want the 0 in front? That is the question.

 

[tillabong]

for postal code. but does it really matter what i store them for?

 

[dlukin]

When you import the data into Excel, don't you have an option as to the format? Import them as text, not numbers. That should preserve the leading 0's.

 

[tillabong]

there isnt any option for me to import as text using a csv file. and i think the problem already lies within the csv excel file because everytime i edit and save the csv excel file and then open the file again, the 0 disappears.

 

[ardnek]

I have found that entering a single quote (') into the Excel cell prior to your leading zeroes makes Excel treat the cell as text. This is how I format dates to prevent Excel from reformatting them (Example: '2010-05-01 is entered instead of 2010-05-01). Use "find/Replace" to put a leading quote in front of each number in Excel.

 

[tillabong]

i've tried it. but once you save the csv file and open it again, the zero disappears.

 

[gomarc]

Hi tillabong,

This may not be a one click solution, but it works for me.

Instead of opening the CSV file with Excel, try to do it in MS Access.

On a blank database, go to the import data from external data and follow the steps to import data to a new table in the current database ( with your CSV as the source)

The import data interface of Access is pretty good and will probably work ok with all the default options you get. The only thing you will want to change is “Let Access add primary key” to “No primary key”.

Open Excel with a new blank worksheet and paste the Access table (a good old copy/paste)

Before you make any changes to the excel file, select the rows that are giving you trouble and format them as Text.

Now you can make changes and save the file with no problems.

 

[tillabong]

ok thank you very much for your suggestion. i will try them out. thanks. =)

 

[fortissimo]

CSV is a format that is easy to generate, and not proprietary to one application. Therefore I often provide a function "generate/download spreadsheet" that provides a CSV, which the user can then open in Excel (or Ooo, or whatever). I have the same problem with TEXT fields starting with a + or 0, and containing only digits: Excel converts them automatically to numbers - not what is wanted. Using the import function and defining 1 (or more columns) as text is time-consuming and frustrating, users are happy to just (double)click once.

Here is a suggested solution, which admittedly is an ugly hack, but might work, depending on your circumstances. The idea is to append some non-digit character after the value, thus turning it into a value that Excel recognises as text. To make the value still look the same, this char should be a space (no "ink"), BUT the normal space character (dec 32, spacebar) does NOT work for this, as Excel trims it off too and so still recognises a number. 2 other characters that I have tested are your normal tab char (\t, dec 9, tab key) or the non-breaking space (dec 160, HTML &nbsp. Other space chars, as defined by Unicode, MAY or MAY NOT work, please test first (see http://en.wikipedia.org/wiki/Space_(punctuation)#Table_of_spaces).

 

[niagaradad]

the issue is opening the csv file with Excel. Excel is what is stripping the 0 off. The problem isn't the export from the database.