import tx file into excel

[Pammy]

I have a txt file of stolen guns, the problem, some have a mak and type field
and some do not so data is not lining up correctly on the ones missing the
fields. What do you do about missing fields in the data? Using 2003 Excel

 

[Chip Pearson]

If the missing fields are at the start or end of the data string, you
can do something like:


Dim S As String
Dim N As Long
Dim E As Long
Dim Diff As Long
E = 4 ' expected number of delimiters
' give S a test value
S = "a,b,c"
N = Len(S) - Len(Replace(S, ",", ""))
Diff = E - N
If Diff <> 0 Then
S = Application.Rept("missing,", Diff) & S ' ADD AT START
'S = S & Application.Rept(",missing", Diff) ' ADD AT END
End If
Debug.Print S


Here, S is the string that you are reading in from the text file. E is
the number of delimiters that are expected in "good" data. The code
the gets the number of delimiters (in this case, commas) in the test
string, and if the number of delimiters does not equal the expected
number of delimiters, it adds "missing" at either the end of the test
string or the beginning of the test string. You can change "missing"
to whatever you want to use for missing data.

If your data is missing fields within the record, at neither the start
or end, then the only way that might be possible to test expected data
types and expected values to attempt to find where the missing fields.
That would be rather complicated and not necessarily very reliable.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Sheeloo]

You will either have to fix the text file or the Excel worksheet after
importing the data...
Fixing Excel would be easier
Select the rows with missing information (Data | Filter)
Press F5 | Special | Visible Cells only
Right Click and insert cells (Shift cells right)... as per requirement