parsing data - formatting issue

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.
--
Terri

Try:

Format > Cells... > Number > Custom and use mmm-yy
--
Gary's Student


"terri" wrote:

> 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.
> --
> Terri

"terri" <terri@discussions.microsoft.com> wrote in message
news:4AF76191-29EA-49EE-A29D-5095931FB890@microsoft.com...
> 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.
> --
> Terri

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

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

Vaya con Dios,
Chuck, CABGx3



"terri" wrote:

> 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.
> --
> Terri

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.
--
Terri


"Gary''s Student" wrote:

> Try:
>
> Format > Cells... > Number > Custom and use mmm-yy
> --
> Gary's Student
>
>
> "terri" wrote:
>
> > 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.
> > --
> > Terri

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.
--
Terri


"CLR" wrote:

> When you do the TextToColumns, format that column as TEXT instead of general.
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "terri" wrote:
>
> > 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.
> > --
> > Terri

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.

"terri" <terri@discussions.microsoft.com> wrote in message
news:13A68C84-6051-439B-8BA4-B815B61E276B@microsoft.com...
> 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.
> --
> Terri
>
>
> "CLR" wrote:
>
>> When you do the TextToColumns, format that column as TEXT instead of
>> general.
>>
>> Vaya con Dios,
>> Chuck, CABGx3
>>
>>
>>
>> "terri" wrote:
>>
>> > 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.
>> > --
>> > Terri

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?

"terri" <terri@discussions.microsoft.com> wrote in message
news:E608E470-0A97-4EB0-850C-9262167EB7CE@microsoft.com...
>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.
> --
> Terri
>
>
> "Gary''s Student" wrote:
>
>> Try:
>>
>> Format > Cells... > Number > Custom and use mmm-yy
>> --
>> Gary's Student
>>
>>
>> "terri" wrote:
>>
>> > 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.
>> > --
>> > Terri

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.
--
Terri


"Doug Kanter" wrote:

>
> "terri" <terri@discussions.microsoft.com> wrote in message
> news:4AF76191-29EA-49EE-A29D-5095931FB890@microsoft.com...
> > 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.
> > --
> > Terri
>
> Format the cells in the Excel sheet *before* they receive the text data.
> Then, copy & paste the data into those cells.
>
>
>

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?

"terri" <terri@discussions.microsoft.com> wrote in message
news:3AB32BCF-9880-48DA-ADD0-A480F1BF3640@microsoft.com...
> 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.
> --
> Terri
>
>
> "Doug Kanter" wrote:
>
>>
>> "terri" <terri@discussions.microsoft.com> wrote in message
>> news:4AF76191-29EA-49EE-A29D-5095931FB890@microsoft.com...
>> > 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.
>> > --
>> > Terri
>>
>> Format the cells in the Excel sheet *before* they receive the text data.
>> Then, copy & paste the data into those cells.
>>
>>
>>

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.

--
Terri


"Doug Kanter" wrote:

> 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?
>
> "terri" <terri@discussions.microsoft.com> wrote in message
> news:3AB32BCF-9880-48DA-ADD0-A480F1BF3640@microsoft.com...
> > 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.
> > --
> > Terri
> >
> >
> > "Doug Kanter" wrote:
> >
> >>
> >> "terri" <terri@discussions.microsoft.com> wrote in message
> >> news:4AF76191-29EA-49EE-A29D-5095931FB890@microsoft.com...
> >> > 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.
> >> > --
> >> > Terri
> >>
> >> Format the cells in the Excel sheet *before* they receive the text data.
> >> Then, copy & paste the data into those cells.
> >>
> >>
> >>
>
>
>

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-Ligh...ml?tag=lst-0-1

-Doug

"terri" <terri@discussions.microsoft.com> wrote in message
news:195E014D-FAB7-4918-B326-53EE87E6C1B6@microsoft.com...
>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.
>
> --
> Terri
>
>
> "Doug Kanter" wrote:
>
>> 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?
>>
>> "terri" <terri@discussions.microsoft.com> wrote in message
>> news:3AB32BCF-9880-48DA-ADD0-A480F1BF3640@microsoft.com...
>> > 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.
>> > --
>> > Terri
>> >
>> >
>> > "Doug Kanter" wrote:
>> >
>> >>
>> >> "terri" <terri@discussions.microsoft.com> wrote in message
>> >> news:4AF76191-29EA-49EE-A29D-5095931FB890@microsoft.com...
>> >> > 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.
>> >> > --
>> >> > Terri
>> >>
>> >> Format the cells in the Excel sheet *before* they receive the text
>> >> data.
>> >> Then, copy & paste the data into those cells.
>> >>
>> >>
>> >>
>>
>>
>>