CSV import format problem

[egoldberg]

All,

I have been looking around for a solution to this problem I am having and am only finding others with the problem and not much of a solution. When I import a .CSV file I have that is created from a database we use, Excel chooses to format some of the values as it sees fit. For instance, every row we have has an application number in the form "09-XXXX". Now excel imports some of these ok but some it converts into the form of Sept-2322 (i.e. 9/1/2322). Attempting to format the row as text then converts that value into its equivalent Excel numerical value (i.e. 14322). An example CSV snippet... in the import 09-1456 comes out ok, but 09-1939 imports as 09/01/1939.

1/20/2009 7:00:00,1/20/2009 15:30:00,"09-1456","X","X","X",,"XX","X","X",12/10/2008 17:09:40,,"2"," "
1/20/2009 7:00:00,1/20/2009 15:00:00,"09-1939","X","X","X",,"X","X","X",1/19/2009 15:07:54,,"0"," "

Now I've determined one solution is to open the CSV file in notepad and revise each application number to be "' 09-XXXX" but there are two problems with this. One, its a pain. Two, when I do that, excel imports each point with the ' and doesn't become just the number until you open the value and push enter again. Is there either a way to import without the formatting, or if I need to use the ' is there a way to "push enter" on all the text values without going through each one by one? Thanks for your help.

 

[Richard Schollar]

Hi

One potential way of doing this would be to change the extension of the file in Explorer before you open the file in Excel to .txt. Then when you open the file in Excel the Text Import Wizard should fire and you should be able to specify Text import format for the particular column causing you an issue on the third screen of the wizard.

 

[egoldberg]

Thanks Rich, the problem that arises when I do it this way is for some reason Excel is delimiting by Carriage returns in text fields so for some reason. Text that has an enter in it, also becomes the next row when I import using the .TXT file method but for some reason works just fine when I open it using .csv other than the date formatting. Any ideas?