Importing large tab-delimited text files?

  • Thread starter Thread starter Mark R-S
  • Start date Start date
M

Mark R-S

I deal with very large data sets that are in tab-delimited .txt format.
Many times, there are more than 256 columns of data.

The Microsoft Knowledge Base had an article titled "Sample Macro to Import
Data with More Than 256 Fields or Columns". This could do the trick for me
if I could modify the macro to accomodate tab-delimited formats.

Q1: Could anyone tell me how to modify the macro described above for
tab-delimited .txt files?

Q2: Does anyone know of a bulk file conversion utility that would change a
tab-delimited text file to a CSV format?

Thanks a bunch,

Mark R-S
 
Mark, perhaps you could try to import first into an Access table and
then just parse the Access table into Excel, or just leave it in
Access depending on what you're doing.

ryan
 
change this line


WorkResult = Right(WorkResult, Len(WorkResult) - InStr(1, WorkResult, ","))
CommaCount = CommaCount + 1

to


WorkResult = Right(WorkResult, Len(WorkResult) - InStr(1,
WorkResult, vbTab))
CommaCount = CommaCount + 1


in both lines that look like this:


Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1))

Change Tab:=False to Tab:=True

change comma:=True to comma:=False
 
That's correct. Please ignore my advice.


Tom Ogilvy said:
change this line


WorkResult = Right(WorkResult, Len(WorkResult) - InStr(1, WorkResult, ","))
CommaCount = CommaCount + 1

to


WorkResult = Right(WorkResult, Len(WorkResult) - InStr(1,
WorkResult, vbTab))
CommaCount = CommaCount + 1


in both lines that look like this:


Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1))

Change Tab:=False to Tab:=True

change comma:=True to comma:=False
Click to expand...
 
Back
Top