MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Import CSV file

  Asked By: Pedro    Date: Nov 09    Category: MS Office    Views: 1941

I think the threads are mixed so I have restarted this one. As I understand
it - You want to import a csv file which has numbers and text in a column.
Some of the text starts with a - (minus sign). If these are imported in
General format the text generates errors. If they are imported in text format
you cannot manipulate the numbers.

The answer is surprisingly easy. Import the file as Text, then select the
columns and change the format to General.



4 Answers Found

Answer #1    Answered By: Joyce Edwards     Answered On: Nov 09

sorry, I must have been half asleep when I sent the last email
and got it mixed up with a different question!

I tried what you suggested, but after importing the file  as text, then
changing the format  to General, the numbers  were still formatted as text
(with the little green triangle in the corner to indicate Excel realises the
cell contains a number formatted as text).

I tried submitting a similar question on an internet forum, and someone
suggested I use the following code to convert the cells to numbers:

For Each a In Range(Cells(1, "B"), Cells(Cells.Rows.Count, "B").End(xlUp))
With a
If Len(.Value) > 0 Then .Value = .Value * 1
End With

It did end up helping to fix the problem, although I thought there may have
been a simpler way (without needing to loop through all the cells). If I
select the whole column  and click one of the smart tags to convert the text
to numbers, they all get converted - I was hoping the same thing could be
done using VBA, but I guess I should be thankful that the above For Next
loop gets the results I want.

Answer #2    Answered By: Adel Fischer     Answered On: Nov 09

OK. I am pleased you have come up with a solution.

I cannot see an easy  way to simplify what you are doing.

As with any code, you may be able to speed it up by stopping screenUpdating
and calculating while it runs.

Answer #3    Answered By: Teresa Rogers     Answered On: Nov 09

Sorry guys for jumping in an messing up the thread...I must be the one
half asleep!.

Answer #4    Answered By: Tammy Sanders     Answered On: Nov 09

Tim responded to my question on csv  format. As explained earlier,
I am running an unattended scheduled task to download daily csv files.
In order to successfully import  the files to my accounting application,
I need to reformat it. I am already reformatting the file  thru the
"import external" file task and this is working fine. What I would like
to do is to delete the rows that have a zero value (wire transfers will
not produce a check number). These rows need to be deleted prior to
importing to eliminate the possibility of getting an error report.

Didn't find what you were looking for? Find more on Import CSV file Or get search suggestion and latest updates.