Importing CSV - Avoiding conversion to 'dates', but totally different results when controlling manua

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

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:

(e-mail address removed)

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
 
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...
 
I you trying to convert the dates back to text? You can format the date
field to a custom number format like 0-0000. Then if you want to convert
these to text you can select them and choose Data, Text to Columns, Next,
Next, and select the Text option and click Finish.

It this helps, please click the Yes button.
 
Hi again,

I think maybe my last suggestion won't do what you want - instead try this
Assuming the first entry is in A1 enter this formula in a blank cell
=MONTH(a1)&"-"&YEAR(a1). Choose Copy, Paste Special Values.
 
"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com">
wrote in message
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.
Click to expand...

Hi Sheelo,

I recorded the macro and this is what I got:

+-+-+-+-+-+-+-+-+-+-+-+-+

Sub Macro1()

With ActiveSheet.QueryTables.Add(Connection:="TEXT;file.txt",
Destination:=Range("$A$1"))
.Name = "importfile"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


+-+-+-+-+-+-+-+-+-+-+-+-+

The Array(1) bit is different to what you posted (you have two
parameters)?

Does that make any sense?
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...
Click to expand...

The ASCII 13 imports as a small 'square' character (when I described
that it opened perfectly). I guess I was overstating it a bit, but
the 'square' rather than a line break (CR) within a cell is not an
issue for me, so it is relatively perfect ;-)

If I was going to use VBA to edit the file prior to importing, how
would I do that though?

Thanks for your assistance,

--

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:

(e-mail address removed)

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
 
ShaneDevenshire said:
I you trying to convert the dates back to text? You can format the
date
field to a custom number format like 0-0000. Then if you want to
convert
these to text you can select them and choose Data, Text to Columns,
Next,
Next, and select the Text option and click Finish.
Click to expand...

Hi Shane,

The problem is that excel has effectively corrupted the underlying
value of the data, rather then just formatting it wrongly.

Using the example I did in my OP, the original text was "3-8000" (text
string, six chars long).

Excel imports that as a date (1 Mar 8000) which has an underlying
value of 2228041 (numeric value).

There is no simple formatting that will get from that numeric value
back to the text string (as far as I know!)
It this helps, please click the Yes button.
Click to expand...

Not sure what this means. The ms public groups are text only NNTP
groups, so there is no concept of clicking a button?

Thanks,

--

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:

(e-mail address removed)

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
 
ShaneDevenshire said:
Hi again,

I think maybe my last suggestion won't do what you want - instead
try this
Assuming the first entry is in A1 enter this formula in a blank
cell
=MONTH(a1)&"-"&YEAR(a1). Choose Copy, Paste Special Values.
Click to expand...


Hi Shane,

I should have read down before replying to your first post!

Yes, I could use formulae (or code) after the import to correct the
issues, but I would really like to find a way to get excel to import
the data correctly if I can.

However, if that isn't possible, I may have to use a formula such as
that, so thank you for posting it.

The problem then would be that some of the cells are 'corrupted and
some are not, but I can work through the scenarios if I can't resolve
the underlying problem.

Thanks again!

--

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:

(e-mail address removed)

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
 
Hi Shane,

The problem is that excel has effectively corrupted the underlying value
of the data, rather then just formatting it wrongly.

Using the example I did in my OP, the original text was "3-8000" (text
string, six chars long).

Excel imports that as a date (1 Mar 8000) which has an underlying value of
2228041 (numeric value).

There is no simple formatting that will get from that numeric value back
to the text string (as far as I know!)
Click to expand...

=TEXT(A1,"m-yyyy")
 
David Biddulph said:
=TEXT(A1,"m-yyyy")
Click to expand...

Cool!

If I can't find a way to sort out the source of the problem, then
that'll definitely fix it at destination.

Thanks,

--

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:

(e-mail address removed)

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
 
Apparently you did not select 'TEXT' as the field type for the column which
is getting imported as DATE... I assume you have that option in import
through Query. I tested with importing a text file and got the following
recorded macro...
'-------------------
Workbooks.OpenText Filename:= _
"C:\Test.txt", Origin:=437, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2,
2), Array( _
3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True

End Sub
'-------------------
 
Back
Top