Transfer Text with a non delimited text file??

[Robert88]

I have created a button on a form in order to transfer text into a table tblPlanetarySystems from a file PlanetarySystems.txt. the code is as follows

Private Sub CmdReadFile_Click()

DoCmd.TransferText acImportDelim, , "tblPlanetarySystems", "C:\Planet\PlanetarySystems.txt", True

End Sub

a portion of the text file "PlanetarySystems.txt is as below

"Host Star", "Planet", "Distance from Earth", "Magnitude", "Planet Mass"
"16 Cygni", 16 Cygni b", 70, 6.2, 1.5
"47 Ursae Majoris", "47 Majoris b", 43, 5.1, 2.41

I have no probem with this but here is my problem. I have taken this out of a book and it works. The reason for using it is to use it on a key register. The key register has an *.ash file which I can change to *.txt and read the file like my example. However the file I am dealing with is not delimited, one line of it shown below

sample of one line of data from the *.ash file
2005110407000000114890180020000000087C44..............

If it was to be delimited it would have these properties.

Date, Time, Access Code (Each users is different)...........
20051104, 0700, 0000114890180020000000087C44............

So my question , is it possible to read in such a file without delimitation?

Currently I am getting a run time error 2391, Field F1 does not exist in table tblKey. The table I created tblKey has currently two fields fldDate_Time [Date/Time with general date format] and a field fldAccess [200 characters properties].

The data in this file has consistent field widths for each record and a line as shown represents a record. The data shown is only a portion of the data in one line which makes up a record but I figure this is a good enough sample, if not let me know and I can paste more.

Look forward to anyones input.

Best Regards

 

[Bat17]

if you do an import by hand using the Wizard you can then save the spec and use that in your code to import nextime
Files>Get external Data>import...
use the dialog to select your file...
Click the Advanced Button..
Select "Fixed Width" under File Format
Go though your fields setting up the correct widths
Save the Spec with "Save As"

Then from code you can
DoCmd.TransferText acImportDelim, "MySavedSpec", "tblPlanetarySystems", "C:\Planet\PlanetarySystems.txt", True

HTH

Peter

 

[Robert88]

Thanks Bat17, only one minor teething problem with time...

Bat17,

Your a wizard, that did the trick, well 99% of the way.

I only have one problem with time, need more of it, LOL!!
I have managed to find workaround but not sure if it is going to work with a concatenation later as have not tried yet?

I have transferred the 07 of the time in the example above into a field "fldhrs" and the 00 into a field "fldmins", the date I decided into another field "fldDate"

The time in the example above is 0700. Since it did not have a ":" between 07 and 00 it was unable to read into a field "fldtime" with a format of hhnn, rather than the default hh:nn. I noticed that when I tried to place 0700 into the table with this field it did not work but when I input it as 07:00 it went into the field but this is not what my data represents even after trying to change the format without the ":".

If you can suggest a better method with the time field please do so, otherwise I hope I can concatenate the two "fldhrs" & "fldmins" fields.

I thank you for your help to date, it has been great as I have managed to convert all data from the *.ash file into a Microsoft Access table apart from the time field which is currently in two fields a little ugly, I hope I can use the workaround......?

Regards
Rob

 

[Bat17]

I think that you will have to stay ugly
Ulness somebody else pops up with a solution!

Peter

 

[Robert88]

My First Function.....

Thanks for your help BAT17, I thought this might be the case.

I managed to merge the data so I am somewhat happy with this function

Option Compare Database

Public Function MakeTime(fldHours As String, fldMins As String) As Date

' This function concatenates fldHrs and fldMins and converts two
' Strings into one field as a time field.

MakeTime = fldHours & ":" & fldMins

End Function

I suppose since it is require to be appended to a history table I can use it then to store for later us in case history is needed....


I am starting to get the hang of this programming....