Two suggestions;

1. Record a macro while importing the file and later edit that macro
You will see an entry like following -
FieldInfo:= Array(
Array(0,1),
Array(14, 1),
Array(39, 9),
Array(48, 1),
Array(54, 1),
Array(60, 1),
Array(72, 1),
Array(85, 1),
Array(93, 1),
Array(100, 1)
)
Try to identify the column which is being treated as date and change 1 to 2
in that array entry... this should solve text getting imported as date
problem.

2. Do you really get the file opened perfectly? I would guess that ASCII 13
would take the data to next row... Yes, you can replace it with a space
character... ASCII 10 is treated as End of Record while importing a text
file...
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Alan" wrote:

Hi All,

I have to import CSV files into excel.

A regular issue with this is that often the CSV files will contain
data (in this case account codes) that excel interprets as dates (such
as '3-8000' being converted to 1 Mar 8000).

Nothing unusual so far, in that we handle that issue by importing
manually (External Data - Text import), and specifying that the field
in question is imported as TEXT not GENERAL.

However, I have a file that, when I double click on it, it opens up
perfectly (except with dates where it should be text as outlined
above), but if I manually import that file, it then chokes on some
control characters that are in the CSV file (specifically an ASCII
Code 13 which is a carriage return).

I haven't got much of a clue on what the best way to go from here
would be, so looking for any suggestions.

One possible idea that is probably a VBA solution (happy to go down
that track if easiest) is to somehow do a search and replace on the
source CSV file and convert the CRs (ASCII 13) to, say, spaces (ASCII
32). I think I'd need to do that to the external CSV file though
before it got imported, hence the VBA requirement?

Thanks in advance for any ideas.

--

Alan.

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb