column misalignment

Where are these files being downloaded from?

Are they actually Excel files, delimited text files (like CSV or tab-delimited), or some other sort of files (like DBFs or LOTUS 123)?

Hi Joe4,

Sorry about the duplicate. I'm working w/a deadline re: this problem and got impatient. It won't happen again.

I'm using Windows XP. This file is a list bought and downloaded from an online source and yes, it does download as an excel file. I'm trying to do a sort of certain values and this alignment problem is stalling the project.

Sounds like the problem is actually with how the download is done. If the site that you are downloading it from claims to allow exports/downloads to Excel (and you are following their instructions on how to do this), you may want to tell them that it appears to be faulty.

Otherwise, I think the only thing you may be able to do is an "after-the-fact" fix. If you can determine a "rule" for when this is happening, and how it should be fixed, you can probably create a macro to "clean it up" after the download.

If you need help in doing, please specify these rules for identifying and fixing it. It needs to be something tangible can Excel can understand and we can program.

Hi joe4,

Per your suggestion to develop a rule for a macro fix of my problem, I took a closer look at the file and determined that the file had downloaded into a totally unworkable format. Also, as you suggested, I contacted the source of the file and asked them to resend the files. They placed the information into new files and sent the the info in a multiple of files instead of the original two and that worked out perfectly. Now, I'd like to ask you why this would be a persistent problem with two different excel files (they weren't especially large files-3000 rows and 9000 rows-19 columns respectively) and if I should expect the same problem in the future. I certainly thank you for your response to my problem. I've been trying to resolve this for several weeks and was about at my wit's end. Again, thank you. I really appreciate your help with this.

BTW-I noticed that the file was in CSV format if you think that was significant.

Without seeing the site, data, and knowing how their download works, it really is impossible to say. You mention that the files were CSV files. It could be there may be commas in the data, and they are using text delimiters. This causes extra columns.

For example, let's say you have two columns of data, name (in Last Name, First Name format) and age. Properly put into a CSV file, it would look like:

"Smith, John",34

This would put Smith, John in the first column and 34 in the second. Note that the double-quotes are text delimters which tell Excel that everything between them is literal text, so the comma between First and Last Name is treated as text and not a delimiter.

However, I have seen some entities send invalid CSV files where the data looks like this:

Smith,John,34

This puts Smith in the first column, John in the second, and 34 in the third. Since there are no text delimiters used, the comma between Smith and John is treated as a delimiter.

I have had this problem myself with a few vendors who don't understand that CSV files requires Text Delimiters if there are any commas in the data.

So something like this might be what is going on, but without knowing all the background, it is hard to say. Even if I saw the site and the file it produces, without knowing how their download process works behind the scenes, it might be impossible to say.

If the problem continues I suppose I'll just have the source send the files in a multiple file download, which seems to alleviate the problem. Thanks again for your help!