Prevent Comma delimiter on txt file open?

**mike216** · 03-26-2012, 05:55 PM

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?

**rylo** · 03-26-2012, 07:38 PM

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

**mike216** · 03-26-2012, 09:14 PM

-2107390455M130007925125|1107740|DATA - WITHHELD||Sample, Text|(B)|Includes: Data.|Redacted|0|.8|.9|V|FILE-049-06|M12-2107390455MO2156|3/13/2012 12:10:13 AM|470

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.

**rylo** · 03-26-2012, 09:50 PM

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

**mike216** · 03-26-2012, 10:09 PM

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...)

**abousetta** · 03-26-2012, 10:58 PM

Here's the full version I use with the choice to pick one file or every file in a folder. If you use the latter approach then you could automate the process by cleaning your data and saving it back to the original text file (or a new file).

Option Explicit

Dim myFile, myFolder


Sub test()

Dim fso As Object, fPath As String
Dim Import As Integer

Import = 1
  ' Import File(s)
    Select Case Import
        Case 1
            With Application
                myFile = .GetOpenFilename("Text Files,*.txt")
                .ScreenUpdating = True                 ' Allow pop-up to disappear
                .ScreenUpdating = False
            End With
            If myFile = "False" Then Exit Sub Else Application.Run "CleanMyData"
        Case 2
            Set fso = CreateObject("Scripting.FileSystemObject")
            With Application
                With .FileDialog(msoFileDialogFolderPicker)
                        .AllowMultiSelect = False
                        .Show
                    If .SelectedItems.Count > 0 Then fPath = .SelectedItems(1) & "\" Else Exit Sub
                End With
                .ScreenUpdating = True                 ' Allow pop-up to disappear
                .ScreenUpdating = False
            End With
            
            Set myFolder = fso.GetFolder(fPath).Files
            
            For Each myFile In myFolder
                If LCase(myFile) Like "*.txt" Then Application.Run "CleanMyData"
            Next myFile
    End Select

End Sub


Private Sub CleanMyData()

Dim sq
    Open myFile For Input As #1
        sq = Split(Input(LOF(1), #1), vbCrLf)
    Close #1
    ActiveSheet.Cells(1).End(xlUp).Resize(UBound(sq) + 1) = Application.Transpose(sq)
    ActiveSheet.Columns(1).TextToColumns , , , , False, False, False, False, True, "|"
End Sub

**mike216** · 03-27-2012, 08:41 AM

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.

**abousetta** · 03-26-2012, 10:56 PM

This a modified version of snb's feature tricks.

Sub CleanMyData()

Dim sq
    Open myFile For Input As #1
        sq = Split(Input(LOF(1), #1), vbCrLf)
    Close #1
    ActiveSheet.Cells(1).End(xlUp).Resize(UBound(sq) + 1) = Application.Transpose(sq)
    ActiveSheet.Columns(1).TextToColumns , , , , False, False, False, False, True, "|"
End Sub

**abousetta** · 03-27-2012, 08:46 AM

Hi,

It's not clear what you clean up, but yes you should be able to implement something similar in Access since both use vba.

**abousetta** · 03-27-2012, 08:49 AM

I personally like the idea of opening the text file into an array from the beginning, looping through the array making all the changes that are needed and only writing to the file (excel or access or whatever type) the final product. When you have a ton of files with repeated actions that don't need user input this can speed things up quite a bit becuase it does everything in memory rather than writing to a file, editing the file, etc.

Hope this helps.

abousetta

**mike216** · 03-27-2012, 10:04 AM

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...

**abousetta** · 03-27-2012, 10:06 AM

Well it looks you got a handle on things...

Good luck and don't forget to mark the thread as solved if our assistance is no longer needed on the originally posted issue.

abousetta

**mike216** · 03-27-2012, 10:17 AM

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

**abousetta** · 03-27-2012, 10:35 AM

Well if you want to continue with the same track as the original post then you are of course free to do so, but it is obvious that you are not and that's why you don't want any more input. Part of the reason that we ask people to mark threads as solved is to disencourage others from attempting to provide a solution when the OP is content.

**mike216** · 03-27-2012, 11:08 AM

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.

**abousetta** · 03-27-2012, 11:41 AM

Have you tried the code in post #6?

Prevent Comma delimiter on txt file open?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Prevent Comma delimiter on txt file open?

Re: Prevent Comma delimiter on txt file open?

Re: Prevent Comma delimiter on txt file open?

Re: Prevent Comma delimiter on txt file open?

Re: Prevent Comma delimiter on txt file open?

Re: Prevent Comma delimiter on txt file open?

Re: Prevent Comma delimiter on txt file open?

Re: Prevent Comma delimiter on txt file open?

Re: Prevent Comma delimiter on txt file open?

Re: Prevent Comma delimiter on txt file open?

Re: Prevent Comma delimiter on txt file open?

Re: Prevent Comma delimiter on txt file open?

Re: Prevent Comma delimiter on txt file open?

Re: Prevent Comma delimiter on txt file open?

Re: Prevent Comma delimiter on txt file open?

Re: Prevent Comma delimiter on txt file open?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions