Text Import routine does not work,can't Use TextTransfer... 1

[NikosXP]

Hi to all
I have a text file which looks like the following

1,6345 1
2,0514 2
2,7129 2
5,8571 1
6,6885 1
7,7595 1
8,3747 2
9,2340 1
10,7079 2
11,3166 3
13,9199 2
14,1235 3
etc
345896606,655 343443
etc
As you can see The data are not very well alligned as the first column can grow and mess up the TransferText Action (I can't use fixed with As you can mention from the samples above.) when I try to Import the data into an Access Table
I've written the following piece of code in VBA but it does not Work (I can see that the comma(,) produces much of the error). the comma Is Used in the Text File as . but it cant be changed...
Dim A As DAO.Recordset
Dim Time As Double
Dim Custs As Long

Open "c:\scratch\results.txt" For Input As #1
Set A = CurrentDb!Table1.OpenRecordset
Do While Not EOF(1)
Input #1, Time, Custs
With A
.AddNew
!Time.Value = Time
!Custs.Value = Custs
.Update
End With
Loop

I'm missing something here but I'm not in the VB coding for long and I don't know what to do....
Help Please....

Nikos

 

[PhilStaines]

What you could try is importing the data into a single field in a table and then use the Instr, left, right and len functions to parse the data by looking for a number of spaces.

This finds the left side:

Left([field1],InStr([field1]," &quot)

This finds the right side:

Trim(Right([field1],Len([field1])-InStr([field1]," &quot))

So import the data (fixed width) so all the data is in one field. (field1 in the examples) Create 2 new text fields in the table (the table is called xx in the examples), time and cust. Run the following sql:

UPDATE Xx SET [time] = Left([field1],InStr([field1]," &quot), cust = Trim(Right([field1],Len([field1])-InStr([field1]," &quot));

To change the commas to decimal points run this sql:

UPDATE Xx SET [time] = IIf(InStr([time],",&quot=0,[time],Left([time],InStr([time],",&quot-1) & "." & Right([time],Len([time])-InStr([time],",&quot));

Change the new text fields to number fields and delete the original field. All these steps can be automated in VBA.

Good luck.

 

[NikosXP]

I thought that there would be an easier solution like doing the parsing while reading from the file and before porting the data into the access table.....
I will try it out and see if I can do the left and Trim while readingthe file , line by line

Thanks

Nikos

 

[lameid]

The following should work... Are you certain that it isn't a tab delimeted file and all this work could be saved by just importing that way?

Dim A As DAO.Recordset
Dim Time As Double
Dim Custs As Long
Dim lngFileHandle as long
Dim StrInput as string
dim lngPos as long
lngFileHandle = FreeFile()

Open "c:\scratch\results.txt" For Input Access Read As lngFileHandle
Set A = CurrentDb!Table1.OpenRecordset
Do While Not EOF(1)
Line Input #1, StrInput
lngPos = instr(1,Strinput," &quot
Time = Left(Strinput,lngPos)
Custs = Trim(Right(Strinput,len(strinput) - lngPos + 1))
With A
.AddNew
!Time.Value = Time
!Custs.Value = Custs
.Update
End With
Loop

 

[llocklin]

I import megabytes of data daily from a mainframe and have found the Access text import routines to be intolerant of deviations in the specified line length resulting in inconsistent imports (you never know how it's going to fail). My resolution was to write my own import routine based on a definitions table and reading the text files directly. The basic text file routines are sensitive to high order characters in the data though so those must be removed prior to processing (I use a freeware program called change (I can't find the credits right now but it's by someone working for the government and it's so fast you won't even know it ran).

I hesitate to post the code here as it is fairly large. But the code isn't complex and you've already got an example from lameid above. Let me know if you need more.

Luther