Excell 2010 - attempting to convert to a comma delimited txt file AND keep my Leading Zeroes. 1

[Snookless7]

Hello,

Been attempting to export an excel 2010 file to a comma delimited txt file AND keep my Leading Zeroes. But it keeps removing the leading zeroes. The leading zeroes can be dates or a set of numbers.

I currently save as CSV file first, then open notepad > file Open > select my CSV file. Zeroes gone.

Is there a link with instructions on this?

 

[SkipVought]

hi,

Instead of OPENING the .csv, rather IMPORT this text file into a new Excel workbook sheet via

Data > Get External Data > Text Files....

In this method, there is an opportunity to 1) Define the import as DELIMITED by COMMA and 2) define each column appropraitely, which means for the column containing numbers with leading zeros- define as TEXT.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[randy700]

Step 3 of 3 in the Text Import Wizard allows you to set the data format for each column. You should set the fields with leading zeroes to text.



Randy

 

[SkipVought]

BTW, I almost NEVER open a .csv with Excel for a number of reasons:

1) Excel can and will CHANGE YOUR DATA under certain conditions, by guessing that certain strings a) could be a date (like [highlight]1/2[/highlight] which will convert to a date 1/2/current year) or b) could be a time (like [highlight]1:[/highlight] which will be converted to 1:00 AM) or c) could be a scientific notation number (like 171E2 which will be converted to 17100)

2) I want to be the one to decide what each import column's data type should be, not some unknown programmer's guess logic in Excel.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Snookless7]

Thank you skip, however In the end I need and actual txt in notepad file to import into my database.

I am a little ignorant with some of this stuff but I do try

Todd

 

[Andrzejek]

Isn't that another way around: Snookless7 already has "an excel 2010 file" and tries to save it as CSV file "to a comma delimited txt file AND keep my Leading Zeroes" in that newly created text file ?

Have fun.

---- Andy

 

[randy700]

I need and actual txt in notepad file to import into my database

You can't import the Excel spreadsheet?


Randy

 

[Snookless7]

Does not look like it - txt file - comma delimited - leading zeroes.

 

[SkipVought]

If you saved your formatted Excel workbook as a .csv, then if you open your .csv file in Notepad you will see:

1) that COMMAS separate each row into columns
2) your leading ZEROS are there.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Did you SaveAs your file [highlight]as a .csv[/highlight]???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Snookless7]

yes......CSV (MS-DOS) ..tried all the CSV files though.

 

[SkipVought]

I tried SaveAs to BOTH CSV (comma delimited)(*.csv) and CSV (MS-DOS)(*.csv)

BOTH saved my numbers with leading zeros!

Please explain how you formatted your numbers. How many leading zeros are displayed? Please post an example.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Snookless7]

I got it......and it was you...your right. I saved as a CSV file but kept opening like an idiot. Instead I selected "open with", note pad...then saved as txt.


Worked like a champ!


Thank you!