Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
import tx file into excel
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
import tx file into excel
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) On Tue, 7 Apr 2009 12:06:04 -0700, Pammy wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
import tx file into excel
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 ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Pammy" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import Txt file over several excel sheets | Excel Discussion (Misc queries) | |||
USING DATA FROM AN EXCEL FILE TO IMPORT INTO ANOTHER | Excel Worksheet Functions | |||
How come I can't import an .svc file(Open Office) to Excel file? | Excel Discussion (Misc queries) | |||
How do I import a CSV file to excel | Excel Discussion (Misc queries) | |||
Is there a way to import a pdf file in to Excel | Excel Worksheet Functions |