help importing data

[john smithmyer]

I have a data source with 20 fields. I created an export “template” that exports 8 of the 20 fields to a tab delimited text file.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I also have an Excel spreadsheet (with visual basic code) that imports this data ( 8 fields) and processes it into a report.<o></o>
<o></o>
The eight fields are:<o></o>
Date 05/26/2012<o></o>
View Good<o></o>
Temp 78<o></o>
Size 8<o></o>
Cap Yes<o></o>
Bad No<o></o>
Run Yes<o></o>
Color Red, Blue, Green<o></o>
<o></o>
When exported from the data source, if Color has more than one entry it creates duplicate fields for each one like this:<o></o>
Date View Temp Size Cap Bad Run Color Color Color<o></o>
05/26/12 Good 78 8 Yes No Yes Red Blue Green<o></o>
<o></o>
Any ideas on how I can import this data into my Excel application which would combine the “like” fields into one before processing?<o></o>
<o></o>
Thanks as always for your kind assistance!<o></o>
<o></o>
John<o></o>

 

[Joe4]

It is not quite clear what you mean by this statement:

Any ideas on how I can import this data into my Excel application which would combine the “like” fields into one before processing?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[john smithmyer]

Sorry for not explaining my question clearly.

The data service I am working with allows me to export data in a tab delimited fashion.

I am exporting 8 fields. Field 8 may contain more that one word. If it does instead of exporting 8 fields it exports 7 fields plus a seperate field for earch word in field 8. For example if field 8 has 3 words I get a total of 10 fileds and 3 of the fields have the same or duplicate headers.

I am looking for a way to import these into Exce as only 8 fields combining any fields with duplicate headers into one field.

All of my existing program expects only 8 fields.

 

[Joe4]

It seems to me that it might be better to address this at the point of the data being exported rather than the file being imported (correct the source of the problem instead of trying to create a workaround).

If it does instead of exporting 8 fields it exports 7 fields plus a seperate field for earch word in field 8. For example if field 8 has 3 words I get a total of 10 fileds and 3 of the fields have the same or duplicate headers.

The question is, why is this happening? If you are create a tab-delimited file, is the issue that the three values are already separated by tabs in the Excel file? If we can correct it here, it will only export as 8 fields, and then there is no problem with the import.

 

[john smithmyer]

I wish it were that simple. The data service I use (and pay for) admits that there is an error in thier export process but refuses to fix it. So I am stuck with the problem.

 

[Joe4]

OK, so I assuming that when you import the data into Excel, it should be putting that data on columns A-H, but sometimes it is going our further because of this issue.

We can create a macro to loop through all the records, and fix anything that extends out beyond column H, like this:

Sub FixData()
 
    Dim myLastRow As Long
    Dim myLastCol As Long
    Dim myRow As Long
    Dim myCol As Long
    Dim myString As String
    
    Application.ScreenUpdating = False
    
'   Find last row of data
    myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through each row
    For myRow = 1 To myLastRow
'   Find last column of data
        myLastCol = Cells(myRow, Columns.Count).End(xlToLeft).Column
'   If goes past column H, combine last columns
        If myLastCol > 8 Then
            myString = ""
            For myCol = 8 To myLastCol
                myString = myString & Cells(myRow, myCol) & ","
            Next myCol
            Cells(myRow, 8) = Left(myString, Len(myString) - 1)
            Range(Cells(myRow, 9), Cells(myRow, myLastCol)).ClearContents
        End If
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

 

[john smithmyer]

Thanks Joe!!!!!!!

Your solution works great as a standalone but I am not sure ho to include it in my code. I have tried a few ways but get errors:

Here is my code

Sub Autpen()
'
' Autpen Macro
'
Sheets(1).Select

Application.Goto Reference:="R1C1"

MsgBox "Be sure you have saved your Multiple Listing Data to the C:\ drive under the filename 1004MCdatares.txt", vbExclamation, "1004MC Calculator"

Range("j2").Select
effective = InputBox("Please enter the effective date of the report MM/DD/YYYY", effective)


ActiveCell.Value = effective



Range("A1:M1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

Dim blnTab As Boolean, blnComma As Boolean
blnTab = False: blnComma = False
Dim msgdelimit As Integer

msgdelimit = MsgBox("This data came from: FMLS - FUSION or a tab delimited MLS system correct?", 36, "Tab or Comma")

Select Case msgdelimit

'Yes was clicked
Case 6
blnTab = True
MsgBox "Please confirm the data came from: FMLS - FUSION or a tab delimited MLS" & vbCrLf & _
"Click OK to continue.", , "Tab delimited"

'No was clicked
Case 7
blnComma = True
MsgBox "If the data came from GAMLS - REInsight or a comma delimited MLS" & vbCrLf & _
"Click OK to do continue.", , "Comma delimited"
End Select
'



With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\1004mcdataRes.txt", Destination:= _
Range("$A$1"))
.Name = "1004mcdataRes"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = blnTab
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = blnComma
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Range("A1").Select




End Sub

 

[Joe4]

Just keep it as a standalone, and call it from your other macro. You want it after the import, so it should be near the end, i.e.

   ...
   Call FixData
 
End Sub

 

[john smithmyer]

Joe,

I can't thanks you enough for your kind help.

I am getting a Run-time error 13 Type mismatch and it is refering to:

myString = myString & Cells(myRow, myCol) & ","

It runs fine alone but when it is called I get the mismatch error

 

[Joe4]

That's odd. What happens if you just copy and paste the code I gave you to the bottom of your code, so the code is embedded and you are not calling it.