parsing data - formatting issue

[Guest]

In the original data file (text file - csv format) one of the fields has date
data and it looks like this: Jan-06. When I select "text to columns" and
parse the data, the format of this field changes and looks like this: 6-Jan.
I need to import this data into access and this date field is causing
problems. I tried manually changing the format back to its original form but
when I save the file and then go back into it, it shows up as 6-Jan again.
How do I keep the formatting from changing when I parse the data? Thanks.
Any help is appreciated.

 

[Guest]

Try:

Format > Cells... > Number > Custom and use mmm-yy

 

[Guest]

When you do the TextToColumns, format that column as TEXT instead of general.

Vaya con Dios,
Chuck, CABGx3

 

[Doug Kanter]

In the original data file (text file - csv format) one of the fields has
date
data and it looks like this: Jan-06. When I select "text to columns"
and
parse the data, the format of this field changes and looks like this:
6-Jan.
I need to import this data into access and this date field is causing
problems. I tried manually changing the format back to its original form
but
when I save the file and then go back into it, it shows up as 6-Jan again.
How do I keep the formatting from changing when I parse the data? Thanks.
Any help is appreciated.

Format the cells in the Excel sheet *before* they receive the text data.
Then, copy & paste the data into those cells.

 

[Guest]

I did try that before and it works if I save the file as an excel file. I
was trying to keep the document as a text file (csv format) before I load
into Access to keep from having to do that extra step. I should have
mentioned that in the question. Thanks anyway. It seems like we will just
have to take the extra step. Thanks.

 

[Guest]

Thanks. I did what you advised but now I am having problems opening up the
saved csv file. I guess my computer has decided not to work any more today.
I will try opening it up on Monday and see if the formatting remained the
same.

 

[Doug Kanter]

If you're doing a lot of work with text files, go to www.download.com and
get yourself a copy of the free version of Notetab. Not "notepad", but
Notetab. Handles huge text files nicely. Great for when you want to inspect
them before Excel has fiddled with them.

 

[Doug Kanter]

By way of information, if you make certain kinds of formatting changes to a
csv file, and then close it, Excel will usually tell you that certain styles
can't be saved in a csv file. That's your hint, and the reason why you have
to have the Excel file as an intermediate step. Out of curiosity, though,
why are you sending the file through Excel before putting the data in
Access?

 

[Guest]

My concern is that our data may be too big for excel so I need to keep it in
a text file to load into Access. Any other words of wisdom? Thanks.

 

[Doug Kanter]

Excel has a limit of 65536 rows. Opening your text file in an text editor
and hitting CTRL-END will tell you how many lines you have. Again, my
question: Why are you opening the text file in Excel if its final
destination is Access?

 

[Guest]

I just started here last week so I am trying to figure out what is where. It
sounds like I need to talk to the folks that actually download the csv file.
They are the ones saving it as a csv file in excel. It sounds like you are
saying it will make things easier if the csv file is downloaded from its
source into Access, correct? I am not even sure where the file comes from
but I will find out. You pointed out a good question. I may be coming back
with some more questions once I find out where the data begins. Thanks.

 

[Doug Kanter]

I'll leave this discussion "watched", so I don't miss anything you add
later. Or, feel free to email me with questions.

Meanwhile, two things to remember:

1) Almost any program that imports text may mess things up a bit. They're
trying to help by guessing how different types of data should look. So, it
makes no sense to pass the data through TWO programs, both of which may
force their "opinion" on the results. This is why I suggest going directly
to Access, skipping Excel.

2) Any time you're dealing with a text file from a new source, you should
inspect it with a text editor, which will NOT make any changes to the data.
This allows you to see the text in its pure form, so you can compare it to
the results in Access, and it alerts you to potential problems which you
need to deal with.

There's a nice, free text editor called Notetab (not to be confused with
Notepad, which comes with Windows, but won't open very large files).
Definitely worth getting:
http://www.download.com/NoteTab-Light/3000-2352_4-10294450.html?tag=lst-0-1

-Doug