Importing large tab-delimited text files?

[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

 

[Ritujoy]

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

 

[Tom Ogilvy]

Access has a limit of 256 columns as well, doesn't it?

 

[Tom Ogilvy]

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

 

[Ritujoy]

That's correct. Please ignore my advice.

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