Spaces disappearing in .txt import

elbastador

New Member
Joined
Oct 7, 2005
Messages
36
I'm using a macro to import a series of fixed-width text files. The problem I'm having is that for some lines of data the name field will drop all the spaces in the name and screw up those lines of data. For example something like this:

John Moe Doe 1003 10000

Will get imported as this:

JohnMoeDoe 1003 10000

This only happens to a few lines and I can't figure out why. When this does happen, it messes up the spacing with the following fields and that line does not get imported correctly. So instead of the cell saying "John Moe Doe" it will say "JohnMoeDoe10" and the following cell would be "03 10000". I opened the file in a text editor and this does not happen. Does anybody have any idea what is causing this and how to prevent it?

Thank you!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Need to check first that the blank characters are actually spaces. Could be other non-printing characters like a Tab character(9)

Copy/Paste a section from a text editor like Notepad into a cell and use a formula to check the code of the blank character.

eg. If you have a name like "John Doe" in cell A1 the blank character is number 5, so use the formula :-
=CODE(MID(A1,5,1))

A Space is character(32)

If the blanks are not spaces it is possible to write code to open the text file and replace them with spaces. Here is an example :-

http://www.mrexcel.com/forum/showthread.php?t=306592
 

elbastador

New Member
Joined
Oct 7, 2005
Messages
36
BrianB:

Thanks for the information. I did what you said and copied the name into another file and I did get that the blank characters were "32". If I open the file in a text editor and delete the spaces in question and replace them with a space, it seems to work. Given that these are spaces, what should I do in my macro to make them import properly?

I actually don't even need the data in the Name column that I'm having issues. Once my importing is complete that column is deleted. I just need the other columns to line up right so they get imported correctly. So it doesn't matter if the data in the Name column gets altered or destroyed as long as the other columns line up right.

Thank you!
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
It seems related to the fact that that, with fixed width text files, spaces can be interpreted as column breaks, and get removed. It might be worth recording a macro of the import using the wizard to check that this is set up correctly. I know that this is not a likely cause in view of the fact that removing and replacing spaces in the raw data corrects the matter.

Failing that, if you send some of the raw data to me we could try a macro that by-passes the normal importation method. I could also double-check the file data in an editor that shows the hex code of each character. If you would like to do this them PM me and I will let you know my email address.
 

elbastador

New Member
Joined
Oct 7, 2005
Messages
36
BrianB:

I am using a recorded macro to import these. Oddly enough if I import these into Access I don't have this problem. I'd like to use Access but there are some transformations to the data I'm doing in Excel that I don't know how to do yet in Access. I guess the solution is to try to figure out how to use Access to do it.

Unfortunately I can't send you the data, given the nature of it. Thanks anyway for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,870
Messages
5,483,434
Members
407,395
Latest member
Sakshine

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top