Need help with importing non standard CVS/TXT

[jmckeone]

A co-worker would like to turn some data into a format from which we can generate a usage/functional report.

All data doesn't reside in one file but in 208 files.

Is there any moderately painless approach to merge all the data into one workbook?

 

[jmckeone]

OK ... I decided to record a macro to see what a single import looked like in hopes one of you excel gurus might be able to help me refine the code to import and append all CSV files in the given directory to the current Sheet1 of the active workbook.

Sub TxtImport()
'
' TxtImport Macro
' record importing CSV and formatting fields and indicating separators
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Documents and Settings\E012884\Desktop\Diane\CLMBOH-WIS-SMC-8014WG.csv" _
        , Destination:=Range("$A$1"))
        .Name = "CLMBOH-WIS-SMC-8014WG"
        .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 = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "|"
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[jmckeone]

Problem solved.

Sometimes us computer geeks look for a complex and elegant solution when something simple will do.

Turned out to be painfully simple and utilized an old DOS command

copy *.csv final.csv

Imported resulting file
Removed duplicate headers
Used Data\Text to columns to break it into columns

Hopefully someone else will run across this post and save themselves the extra trouble as a result.