Prevent Comma delimiter on txt file open?

Excel 2010.

I have several hundred txt files, amounting to just over 1gb in cumulative file size that was handed to me for processing. There are integrity problems preventing me from importing them directly into Access, so I am going through them one by one in Excel first, hitting them with a VBA macro to clean up the file. I ran into a snag however, in which I can't figure out how to stop Excel from opening the .txt file under an assumption that it is a .csv.

The data is pipe delimited, because of the valid presence of commas, colons, semicolons, and tabs within the text data. What is worse, there are even valid single AND double quotes in the text.

Opening the file as a pure txt file in Excel is fine IF that particular file does not have any commas in the text fields. On any row that contains a comma however, it automatically splits the row string on that comma into subsequent columns. I can't just open the files in notepad first and strip commas, because they are still important.

I can't seem to locate any setting within Excel that handles how it auto-processes text files. I CAN run through the wizard by opening the file directly through the File menu, but would prefer not to. Even using this wizard, or any other source of text to column wizard, removing the comma delimited option doesn't appear to change the default of a comma for the delimiter

I know this has to be an internal setting somewhere, and I'm hoping someone might be able to point me to how to change it. Even if it's a VBA script to force a change because there isn't a UI element to do so.


Ideas?

Hi

I'd like to see a file example, but failing that (maybe sensitive data), how about doing a line input approach to processing each line individually, then writing it back out (probably to a new file). This would be extremely tedious, but it may get there as it won't actually import the line into excel directly.

rylo

Please Login or Register  to view this content.

Replaced sensitive information with meaningless bits.

This would be 1 row of my most troublesome file. A 120mb text file. Pipe delimited as you can see, but the single comma disrupts the line. But it's not in every line.

I'm thinking having the script check for a pre-split in the line before processing that line to join it back together, add the comma, before processing the text to columns. I have to do the text to columns anyway at this point I think, since anything over ~4mb file appears to randomly drop data when doing the text to columns (on a per line basis). Really weird.


Still kinda interested if anyone knows of a method of changing the on-load default delimiter, but it might not matter for this.

Hi

But what do you do with that line of data? From your first post, you bring it into excel, then run a macro over it. If you just picked up this line and put it in a variable, then presumably you would do the processing on that variable, then post it back out. So what do you do with the line? If that processing is too business sensitive, then make up something that you could work back, so you can see what the full process steps would be.


rylo

This particular row's issue is only with the comma. Some of the other issues I have to deal with are special characters (such as line feeds, etc...), and data that has the incorrect number of delimiters for whatever reason. Once I have the data cleaned and ready for import into access, Access does additional table processing, including pulling in additional data based on field values. Then it sends the data in batches to a MySQL table, empties the Access data (except for a primary key record), and moves to the next file.

The excel side is simply because I CAN'T import the data into Access directly without literally thousands of errors and dropped data. I have to get it cleaned up first. Since I'm processing some cleanup anyway in Excel, I run other stuff against it in the macro since I'm already "touching" it to eliminate having to do it later in Access (like case changes, etc...)

Not quite what I was going for, but it does bring up a new idea that could save me quite a bit of time.

I could read the text files line by line in Access via the file reader, cleaning them, adding to the appropriate table as i do so. Skip Excel altogether.

Yup, me to. The original question is still somewhat valid from a curiosity standpoint, but it no longer impacts me. This put me on a far better track than you know. I'm not even writing back out to the file, and skipping the Access side of it completely. Since the data gets up to MySQL in the end anyway, the script I'm writing to read each file line by line just just sending that line up once it's done with it.

Plenty of time saved.

Adding in other "features" at the moment, like to dump any errors it runs into into a table to address later without having to rerun the file, etc...

Hmm, should I mark it as 'solved'?

We have made the question irrelevant for my particular issue, but the question itself hasn't really been solved

Yes, I will leave it open, in case anyone finds a way to disable or adjust the default delimiter. It may come in handy some day.


Thank you very much for pointing me to a better solution though. Script is off and running smoothly, and quickly.