Trouble with .CSV file

NotASuperGenius

Board Regular
Joined
Jun 17, 2002
Messages
58
I am working with a .CSV file that is generated upstream from me. It contains 1500 rows of data, each row has 6 columns. The first column is "Company Name", followed by three columns of dates and then some performance numbers.

The problem I am having is that the file is comma delimited and some of the company names include commas. As a result, when I open the .CSV file in Excel about 10% of the rows are misaligned.

I have been manually correcting this but there has to be a better way. I cannot make changes to the processing upstream (change the delimiter or to forbid commas in the company name).

Suggestions appreciated - Cheers, NASG
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

NotASuperGenius

Board Regular
Joined
Jun 17, 2002
Messages
58
Partial solution was the edit the file in Notepad. Doing a find/replace for ", " - that is, comma and then a space - removed all but a few. Not perfect but good enough. Viva la Notepad.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,108
Office Version
365
Platform
Windows
Most programs that generate CSV files have an option to include text delimiters, if they are any good! (see here for details: http://en.wikipedia.org/wiki/Comma-separated_values). Otherwise they are pretty worthless. So you may want to check with the "upstream" process, and ask them to include text delimiters.

Otherwise, how can you differentiate commas that are delimiters from commas that are actually data? If you can't come up with a "hard-and-fast" rule logically, you sure aren't going to be able to program anything (programming isn't magic, it can only do what you tell it to do).

Now, if only this company name can have commas in it (and none of the other fields), you may be able to do something with it by importing each row with no delimiters so that all data gets put in column A. Then you may be able to work backwards and use string functions like FIND, LEFT, RIGHT, and MID to pull out the data you need, i.e.

The data after the last comma is Field6.
The data between the last comma and second to last comma is Field5.
...
and so on until you get to Field2.
Then you know everything left over is Field1.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,108
Office Version
365
Platform
Windows
All right, assuming what I said it true, the only legitimate commas which are data are in the first field (company name), here is a macro that I wrote that will import the data into Excel and put everything in the correct column.

All you do is run the macro, select the file name, and it does the rest!
Code:
Sub MySplitCSV()
 
    Dim myFileName
    Dim myConnection As String
    Dim myNumFields As Long
    Dim myLastRow As Long
    Dim i As Long
    Dim j As Long
    Dim myValue As String
    Dim myLen As Long
    Dim myLastComma As Long
    Dim myCurComma As Long
    
    Application.ScreenUpdating = False
        
'   Enter number of fields
    myNumFields = 6
    
'   Browse to file to import
    myFileName = Application.GetOpenFilename("All CSV Files,*.CSV")
    If myFileName = False Then
        Exit Sub
    End If
    
'   Import file
    myConnection = "TEXT;" & myFileName
    Workbooks.Open Filename:=myFileName
    ActiveWindow.Close
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:=myConnection, _
        Destination:=Range("$A$1"))
        .Name = "records3"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierNone
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
   
'   Find last row
    myLastRow = Range("A1").End(xlDown).Row
    
'   Loop through each row
    For i = 1 To myLastRow
'       Get length of field
        myValue = Cells(i, "A")
        myLen = Len(myValue)
        myLastComma = myLen
'       Loop through fields
        For j = 1 To 6
            myCurComma = InStrRev(myValue, ",")
'           Populate cells
            If j < myNumFields Then
                Cells(i, myNumFields + 2 - j) = Mid(myValue, myCurComma + 1, myLastComma - myCurComma)
                myLastComma = myCurComma
                myValue = Left(myValue, myCurComma - 1)
            Else
                Cells(i, myNumFields + 2 - j) = Left(myValue, myLastComma)
            End If
        Next j
    Next i
    
'   Delete column A
    Columns("A:A").Delete Shift:=xlToLeft
    
    Application.ScreenUpdating = True
    
End Sub
 

NotASuperGenius

Board Regular
Joined
Jun 17, 2002
Messages
58

ADVERTISEMENT

Joe - Thank you. Works like a charm. Hadn't thought about approacing the problem from the right. Quick question, the CSV file has 2 rows of data (4 columns) preceeding the problem data. How would I tweak your macro to account for this?

Cheers - NASG
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,108
Office Version
365
Platform
Windows
Are you saying that the problem column is not the left-most one, but in the middle somewhere?

Then you will probably want to come from the right for however many fields there are to the right of this problem field, come from the left for however columns there are to the left of this field, and dump the leftover in as this field.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,108
Office Version
365
Platform
Windows

ADVERTISEMENT

Another thought is insteading of breaking it up piece-by-piece, use the same logic coming from the left and right and insert your own Text Qualifer in it. Then you can use Text-to-Columns to split the columns easily once you have the appropriate text qualifer.
 

NotASuperGenius

Board Regular
Joined
Jun 17, 2002
Messages
58
Sorry, difficult to convey. The head of the file looks like

Code:
Added,Inactive,Updated,Queries
1602,28,34,83
Company Name,Record #,Xref,Created,Modified,Hits
18 Miles, LLC,5721,N/A,11/10/2008 10:10:46,11/10/2008 10:10:46,0
Elite Ltd,4546,N/A,07/21/2008 09:51:45,07/21/2008 09:51:45,0
MG COMPANY,9996,3525,05/24/2005 02:55:11,05/24/2005 02:55:11,0
ALL Red, Inc.,9590,N/A,03/29/2006 09:11:36,03/29/2006 09:11:36,0
BIG GROUP, INC.,0400,N/A,05/24/2005 10:34:28,05/26/2005 10:18:12,0
AGR INC.,8100,9251,05/25/2005 09:05:11,05/25/2005 09:05:11,0
TT Technologies, Inc.,5686,N/A,12/15/2008 02:09:06,12/15/2008 02:09:06,0
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,108
Office Version
365
Platform
Windows
You could skip the first two lines by updating the loop that cycles through the rows, i.e.
Code:
    For i = 3 To myLastRow
Or if you want to include them, and want them separated by columns, just add two commas to the end of each before the loops, i.e.

Code:
Range("A1")=Range("A1") & ",,"
Range("A2")=Range("A2") & ",,"
...
Then the code should work fine against it.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,198
Messages
5,509,762
Members
408,752
Latest member
KrisF

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top