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!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

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,129,478
Messages
5,636,566
Members
416,924
Latest member
cmlacerna

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top