*TXT File Import Into Access*

[xrookie]

I've been tasked with the following challenge: I have a text file with data in it that looks like the lines below. These lines represent three records that are extracted from a program and i have a few thousand of these to import into an access table (alternatively into an excel spreadsheet if it would be easier).


[POI]
Type=0x4400
Label=BP FUEL (SWAKOPMUND)
Data0=(-22.67827,14.52845)
[END]

[POI]
Type=0x2700
Label=JCT S=MOON LANDSCAPE
Data0=(-22.67005,14.55944)
[END]

[POI]
Type=0x2700
Label=HANDCRAFTED LEATHER
Data0=(-22.67913,14.52888)
[END]

The result should look something similar to this:

Field1 Type Label Latitude Longitude
POI 0x4400 BP FUEL (SWAKOPMUND) -22.67827 14.52845
POI 0x2700 JCT S=MOON LANDSCAPE -22.67005 14.55944
POI 0x2700 HANDCRAFTED LEATHER -22.67913 14.52888


This ofcourse should be in an Access 2003 table or an Excel spreadsheet.
Is this possible or what would be the easiest way to go about doing this? Any help would be greatly appreciated.
Thank you

[rt_roh]

The code is crudely written. Don't know how it wud be done more elegantly. This code is assuming that each record is spread in 5 rows + there is always 1 blank row in text file.

You can copy following macro and run it in excel. There is one problem I haven't solved i.e. how to find out max rowcount. You have to set that manually. I think it works fine otherwise. I have set it 18 as I had tested on 3 recs only. If it solves your prob, well and good; otherwise if anyone knows how to get rowcount of data retrieved by Querytables, plz let me know.

Test it and let me know if it helps.

=============================================
Sub importtxt()
'
' importtxt Macro
' Macro recorded 9/14/2005 by
'
' Keyboard Shortcut: Ctrl+i
'
Dim intSrcRowNum, intTgtRowNum As Integer
Dim intMaxrow As Integer
Dim rngSource As Range
Dim rngTarget As Range
Dim rngCopySource As Range
Dim strTxtFilePath, strTemp As String

Set rngSource = Range("M1")
Set rngTarget = Range("A1")

intSrcRowNum = 1
intMaxrow = 18
intTgtRowNum = 2
strTxtFilePath = "C:\Text1.txt"

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & strTxtFilePath, Destination:= _
rngSource)
.Name = "TextToExcel"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.Refresh BackgroundQuery:=False
End With

Cells(1, 1).Value = "Field1"
Cells(1, 2).Value = "Type"
Cells(1, 3).Value = "Label"
Cells(1, 4).Value = "Longitude"
Cells(1, 5).Value = "Latitude"

While intSrcRowNum < intMaxrow
Range("M" & Trim(Str(intSrcRowNum)), "M" & Trim(Str(intSrcRowNum + 3))).Select
Selection.Copy
Range("A" & Trim(Str(intTgtRowNum))).Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True

Cells(intTgtRowNum, 2).Value = Right(Cells(intTgtRowNum, 2).Value, Len(Cells(intTgtRowNum, 2).Value) - 5)
Cells(intTgtRowNum, 3).Value = Right(Cells(intTgtRowNum, 3).Value, Len(Cells(intTgtRowNum, 3).Value) - 6)
strTemp = Right(Cells(intTgtRowNum, 4).Value, Len(Cells(intTgtRowNum, 4).Value) - 7)
Cells(intTgtRowNum, 4).Value = Left(strTemp, Len(strTemp) - InStr(1, strTemp, ","))
Cells(intTgtRowNum, 5).Value = Right(strTemp, Len(strTemp) - InStr(1, strTemp, ","))
Cells(intTgtRowNum, 5).Value = Left(Cells(intTgtRowNum, 5).Value, Len(Cells(intTgtRowNum, 5).Value) - 1)
intSrcRowNum = intSrcRowNum + 6
intTgtRowNum = intTgtRowNum + 1
Wend

Range("A2", "A" & Trim(Str(intMaxrow / 6 + 1))).Select
Selection.Replace "[", ""
Selection.Replace "]", ""

End Sub

=============================================

[xrookie]

Amazing stuff! I'll be very honest with you rt_roh, this is way passed my streched ability to do an import. I am not nearly as clued up with the coding part. The macro basically does the trick when i have a couple of those records in a text file, but when i try to read in the whole file (i did change the intMaxrow accordingly) but i think the problem comes in where excel can only read 65500 lines and there is about 157000 lines in that file if i pull it into Access! Can i also run this macro in Access maybe? I wanted to attach the file for you to look at but its too big (1.7mb) so i pulled out the few lines below. The file only has two types of records; [POI] records (that i showed you already) and the [POLYLINE] records (below) and there are a few added challenges. The total line count when i pull it through to Access is 157330. The first 26 lines in the beginning of the file look different but i wouldnt mind skipping them. The POLYLINE records look like this:

[POLYLINE]
Type=0x3
Label=~[0x2c]M35/C13
Data0=(-26.16791,16.60225),(-26.13143,16.60380)
[END]

[POLYLINE]
Type=0x3
Label=~[0x2c]M35/C13
Data0=(-26.13143,16.60380),(-26.11042,16.60468),(-26.10955,16.60483),(-26.10865,16.60511),(-26.10787,16.60547),(-26.10716,16.60607),(-26.10650,16.60682),(-26.10564,16.60817),(-26.10347,16.61268),(-26.10283,16.61363),(-26.10223,16.61431),(-26.10150,16.61491)
[END]

[POLYLINE]
Type=0x5
Label=~[0x2f]D407
Data0=(-25.91207,16.65485),(-25.91158,16.64474),(-25.91145,16.64392),(-25.91110,16.64240),(-25.90550,16.62017)
[END]

The problem comes in with the Data line. In the previous records ([POI]) there were only two values (lat and long) that was seperated by a comma, now there can be more then one lat and long values and to make it worse, these lat and long values put together are also separated with a comma and the ammount of lat and long values for a polyline differs all the time. Maybe its not as big a thing as i make it out to be but like i said, this is way past my coding knowledge. Please give me a few more pointers. I would understand if it is something that will take up too much time that you probably dont have.
Thanx for your help so far.

[rt_roh]

Some details are required
1) what is the result supposed to look like incase of polyline?
What i mean to ask is whether
[POLYLINE]
Type=0x3
Label=~[0x2c]M35/C13
Data0=(-26.16791,16.60225),(-26.13143,16.60380)
[END]

CONVERTS INTO

POLYLINE 0x3 ~[0x2c]M35/C13 -26.16791 16.60225 -26.13143 16.60380

OR

POLYLINE 0x3 ~[0x2c]M35/C13 -26.16791 16.60225
POLYLINE 0x3 ~[0x2c]M35/C13 -26.13143 16.60380

2)Is the number format always going to be fixed length like longitude is
-NN.NNNNN and latitude always NN.NNNNN?

[xrookie]

Yes, the polyline will look like your first suggestion:

POLYLINE 0x3 ~[0x2c]M35/C13 -26.16791 16.60225 -26.13143 16.60380

The whole record is displayed in one line. The naming of the columns will be along the lines of:

Longitude,Latitude,Longitude2,Latitude2,Longitude3 ,Latitude3...etc

And yes, the number format will always be a fixed length (-NN.NNNNN or NN.NNNNN).

Thankx rt_roh, much appreciated.

[rt_roh]

This is assuming
- you won't have "),(" or comma in any other row except Data0
- the data is in batch of 6 rows(first 4 to include next 2 to ignore)

1. TAKE BACKUP of your text file and replace "),(" with "," and then

Import text file 2 times in 2 tables
-1st table named importtext comma delimited and let it add primary key ID.
-2nd table named finaltable again comma separated and let it add primary key Id.

DELETE all records from Final Table

2. Add 3 fields in the end of FinalTable i.e. if last field is field24 then add field25, field26, field27 to FinalTable.
Also change the names of field2 and field3 to type and label(you can let it go but following code was written with the names changed)


3. Create a macro with following steps

(A)Setwarnings No

(B)RunSQL
INSERT INTO FinalTable ( id, Field1 )
SELECT importtext.id/6+1, replace(replace(importtext.Field1,'[',''),']','')
FROM importtext
WHERE (((importtext.Field1) Like '?PO*'));

(C)RunSQL
UPDATE importtext INNER JOIN FinalTable ON [importtext].[id]=([finaltable].[id]-1)*"6"+"2" SET FinalTable.Type = replace([importtext].[field1],'Type=','');

(D)RunSQL
UPDATE importtext INNER JOIN FinalTable ON [importtext].[id]=([finaltable].[id]-1)*"6"+"3" SET FinalTable.Label = replace([importtext].[field1],'Label=','');

(E)OpenQuery
Queryname UpdLongLat
You will have to create this query first as shown in 4 step

(F)SetWarnings Yes

4. Create updLongLat as

UPDATE importtext INNER JOIN FinalTable ON [importtext].[id]=([finaltable].[id]-1)*"6"+4
SET FinalTable.field4 = replace([importtext].[field1],'Data0=(',''),
FinalTable.Field5 = replace([importtext].[field2],')',''),
FinalTable.field6 = [importtext].[field3],
FinalTable.field7 = IIf(IsNull([importtext].[field4]),Null,replace([importtext].[field4],')','')),
FinalTable.field8 = [importtext].[field5],
FinalTable.field9 = IIf(IsNull([importtext].[field6]),Null,replace([importtext].[field6],')','')),
FinalTable.field10 = [importtext].[field7],
FinalTable.field11 = IIf(IsNull([importtext].[field8]),Null,replace([importtext].[field8],')','')),
FinalTable.field12 = [importtext].[field9],
FinalTable.field13 = IIf(IsNull([importtext].[field10]),Null,replace([importtext].[field10],')','')),
FinalTable.field14 = [importtext].[field11],
FinalTable.field15 = IIf(IsNull([importtext].[field12]),Null,replace([importtext].[field12],')','')),
FinalTable.field16 = [importtext].[field13],
FinalTable.field17 = IIf(IsNull([importtext].[field14]),Null,replace([importtext].[field14],')','')),
FinalTable.field18 = [importtext].[field15],
FinalTable.field19 = IIf(IsNull([importtext].[field16]),Null,replace([importtext].[field16],')','')),
FinalTable.field20 = [importtext].[field17],
FinalTable.field21 = IIf(IsNull([importtext].[field18]),Null,replace([importtext].[field18],')','')),
FinalTable.field22 = [importtext].[field19],
FinalTable.field23 = IIf(IsNull([importtext].[field20]),Null,replace([importtext].[field20],')','')),
FinalTable.field24 = [importtext].[field21],
FinalTable.field25 = IIf(IsNull([importtext].[field22]),Null,replace([importtext].[field22],')','')),
FinalTable.field26 = [importtext].[field23],
FinalTable.field27 = IIf(IsNull([importtext].[field24]),Null,replace([importtext].[field24],')',''));


You might have to add more records based on how many max columns you got. Acc to the the data I had I had max 24 cols, So I had to update for 27 fields in Finaltable. Incase you get more, you add more. The iif-isnull-replace part is for every odd number field of final table.

**Note** Sometimes it pops up window for final update- just run macro again.

Try it and let me know.

[xrookie]

Hi rt_roh,

I sent you a message a little while ago asking if i could sent you the file to a an email address so that you could have a look at it but i havent had a response from you. Would it be possible for me to put the file at some location so you can download it?
Thanks