Import Text File... 1

[dodgyone]

I have been sent a text file to import and use in Access 97 but it isn't in a standard format so the auto import procedure will not work.

How the file is set up:

Each field is set on a single line, so...

ID
Title
Name
Surname
Address1
Address2
Address3

But, if there is no data for a field then the line set aside for it is left blank, so...

ID
Title

Surname
Address1

Address3

From the above example you can see that "Name" and "Address2" have no data so they are left as empty lines.

Each record goes on for 19 lines displaying field data and leaving blank lines for empty fields. I've managed to open the text file using VB but working out how get the data into the table in a reasonable format is proving difficult.

Thanks in advance for any help...

 

[GrantDB]

If I was doing this I would use to go through the file and read each line

Do While Not EOF(1) ' Loop until end of file.
Line Input #1, MyString
Loop

One difficulty would be deciding when a record starts and finished. But you said that each record has 19 lines (whether having data or not) so that is easy (just have a counter for each line and reset when it reaches 20). You can assign line 1 to ID variable, line 2 to title variable etc and then usean sql statement to insert it into a table.

If none of that is clear then let me know

Grant

 

[dodgyone]

Thanks Grant... that's how I have set it up to start with but there seems to be troubles with the number and date variables taken from the input procedure.

If a number or date is a blank line then it causes errors. If I make all variables strings then it works fine. So I have to convert all of the string fields in the table to the correct format after the transfer. It works this way but it's not the ideal solution.

Cheers for the advice though...

 

[GrantDB]

How are you inserting the values into the table? I would use SQL. Can't you also use a convert function to convert the strings to date format before you insert them? - maybe use 'cdate' If you are unsure about SQL and access let me know

Grant

 

[dodgyone]

I started testing with a smaller example text file to start with and all worked fine. But I have now discovered that if an address begins with a house number (i.e. "1 House Name" rather than "House Name&quot then the field is separated and the second part is placed in the second address field (i.e. field 1 = "1" & field 2 = "House Name) . This moves every other field in the record along by one causing errors as there is no field to take the last field data.

I need to find a way of telling the code to igore these kinds of occurances...

My code is as follows:

Set db = CurrentDb()
Set rst = db.OpenRecordset("Import&quot

Open "C:\test.txt" For Input As #1

Do Until EOF(1)

Input #1, no, ad1, ad2, ad3, ad4, AdDat, WPen, Sur, PNew, notes, POld, POldDate, PNewDate, PGranted, PNo, ADPerc, rules, title, TypPen

rst.AddNew
rst.Fields("ID&quot = no
rst.Fields("Address1&quot = ad1
rst.Fields("Address2&quot = ad2
rst.Fields("Address3&quot = ad3
rst.Fields("Address4&quot = ad4
rst.Fields("AdjustDate&quot = Format(AdDat, "dd/mm/yy&quot
rst.Fields("WidowsPen&quot = WPen
rst.Fields("Surname&quot = Sur
rst.Fields("PensionNewAmount&quot = PNew
rst.Fields("Notes&quot = notes
rst.Fields("PensionOldAmount&quot = POld
rst.Fields("PensionOldDate&quot = POldDate
rst.Fields("PensionNewDate&quot = PNewDate
rst.Fields("PensionGranted&quot = PGranted
rst.Fields("PensionNo&quot = PNo
rst.Fields("AdjustmentPercentage&quot = ADPerc
rst.Fields("Rules&quot = rules
rst.Fields("TitleInitials&quot = title
rst.Fields("TypeOfPension&quot = TypPen

rst.Update

Loop

 

[GrantDB]

I started to write this to check for numerical characters, but then I wondered whether this really is a solution.

Are you saying that when you read the variables in the file:
eg: one,1 two,three
then if a field is like the second one you get a '1' and 'two'? This doesn't quite make sense.

Do you have an example of the text file? I though initially that the data was on separate lines

Grant


Dim counter
Dim stringValue
Dim testLetter
stringValue = "w1w1m2"
For counter = 1 To Len(stringValue)
testLetter = Mid(stringValue, counter, 1)
If IsNumeric(testLetter) = True Then
MsgBox "Number"
End If
Next counter

 

[dodgyone]

Here's an example of a record. The field name/type is shown here in front of each field for viewing purposes. It then repeats on the next line with record 7 and so on...

[IDNumber]: 6
[Address 1]: 2 Wetherington House
[Address 2]: Wetherington Road
[Address 3]: Aldershot
[Address 4]: TY7 IG1
[Date]: 30/06/2001
[Date]: 30/06/2002
[Surname]: Smith
[PensionNew]: 630.41
[Notes]: this, that etc etc
[PensionOld]: 627.77
[PensionOldDate]: 01/01/2001
[PensionNewDate]: 01/01/2002
[PensionGranted]: 26/10/2001
[PensionNumber]: 10001
[AdjustmentPercentage]: 0.42
[Rules]: 72E
[TitleInitials]: Mrs J S
[PensionType]: M

 

[GrantDB]

The code you used
Input #1, no, ad1, ad2, ad3, ad4, AdDat, WPen, Sur, PNew, notes, POld, POldDate, PNewDate, PGranted, PNo, ADPerc, rules, title, TypPen

is for files that have the format
"[IDNumber]: 6","[Address 1]: 2 Wetherington House","[Address 2]: Wetherington Road"...

Try using
dim string as string
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, MyString
debug.print MyString
Loop

This will pick up each line - eg myString = [Address 1]: 2 Wetherington House

You can then use something like:
dim splitArray as string
splitArray=split(MyString,":&quot 'splits up the string at the ":"
CleanedMyString=trim(splitArray(1)) '- this should return 2 Wetherington House

You also use Instr to find the startingposition of ':'

Let me know if that helps

Grant

 

[dodgyone]

Sorry, the starting bit in brackets with the ":" is just there for illustrative purposes.

Sorry for confusing you...

 

[GrantDB]

Oh, no problem. I am easily confused.

I would still still use Line Input though, for the same reasons

Grant

 

[dodgyone]

Hi Grant... thanks for your suggestions. I've been trying out the examples but I'm pretty stuck. There are thousands of records in the text file and does this all go into a single array? I'm pretty confused about how to get the single record data out of 'MyString' and into the correct fields in the database and then starting again afresh with the next record... ad so on.

Thanks for taking the time to help...

 

[GrantDB]

Let's try and put this problem to bed. Here is some code.
The code goes through each line of a text file. The first line of the file must contain code, and there must not be any empty lines between the groups. Picks up a line of data and adds it to the array - line 1 to array 0, line 2 to array 1 etc. The array is two dimenional that contains a fieldname for later use, and the data. When the fieldCounter exceeds the number in the array, we know that the next bit of data must be a new data group so it is time to write the data to the database (I haven't included this bit of code).

Try the code out, and see if it works. Have a look in the debug window for the output. Remember that the code expects the groups of data to be back to back (ie 19 line, then 19 line etc) If this isn't how you text file is, we can modify the code.

Let me know how you go with it.

Grant

Private Sub Command3_Click()
Dim textFile As String

Dim TextFieldsArray(19, 1)

Dim fieldCounter As Integer
Dim CurrentLine As String

textFile = "C:\temp\MyFile.txt"
'The fields
TextFieldsArray(0, 0) = "IDNumber"
TextFieldsArray(1, 0) = "Address 1"
TextFieldsArray(2, 0) = "Address 2"
TextFieldsArray(3, 0) = "Address 3"
TextFieldsArray(4, 0) = "Address 4"
TextFieldsArray(5, 0) = "Date"
TextFieldsArray(6, 0) = "Date"
TextFieldsArray(7, 0) = "Surname"
TextFieldsArray(8, 0) = "PensionNew"
TextFieldsArray(9, 0) = "Notes"
TextFieldsArray(10, 0) = "PensionOld"
TextFieldsArray(11, 0) = "PensionOldDate"
TextFieldsArray(12, 0) = "PensionNewDate"
TextFieldsArray(13, 0) = "PensionGranted"
TextFieldsArray(14, 0) = "PensionNumber"
TextFieldsArray(15, 0) = "AdjustmentPercentage"
TextFieldsArray(16, 0) = "Rules"
TextFieldsArray(17, 0) = "TitleInitials"
TextFieldsArray(18, 0) = "PensionType"
fieldCounter = 0
Open textFile For Input As #1 ' Open file for input.
Do While Not EOF(1)
Line Input #1, CurrentLine
TextFieldsArray(fieldCounter, 1) = CurrentLine
Debug.Print TextFieldsArray(fieldCounter, 0) & ":" & TextFieldsArray(fieldCounter, 1)
fieldCounter = fieldCounter + 1
'this will reset the counter when array is exceeded
If fieldCounter = UBound(TextFieldsArray) Then
'Write data here
Debug.Print "WRITEDATA"
fieldCounter = 0
end if

Loop
Close #1
End Sub

 

[dodgyone]

Thank you so much for your help. Your "Let's try and put this problem to bed" solution solved the problem. I added the insert statements and bingo, it transferred with no errors.

I'm going to have to sit down and really read up more on arrays. I just know the basics about arrays at present.

 

[GrantDB]

What is useful about this solution is that you can use the fieldname in the insert procedure - this saves a lot of coding. As shown, arrays are pretty useful. As a helpful hint I like to use 2 dimensional arrays even if a simple array would do. As above, you can use one of the levels to give a description of the other level.

Grant