transfer text problem

[niki]

Hello, I use mS ACCESS 2002 on windows XP,

I want to automate the importation of multiple text files but an error occurs when I run my Import text macro based on the text transfer action. Indeed, Access asks me to choose the format (I use french version sorry, it's the second row from top in the arguments of the text transfer action, maybe it's named "Specification Name" I dunno...) from a list but I get an empty list, therefore I don't know what to put...

I created a "schema.ini" file in the same folder as my txt file but Access doesn't seem to use it... How can I tell it to do so or more simply, how can I make it work?????

My fields are delimited with "µ" as I was sure this sign would'nt appear anywhere in my text file. There are commas and spaces therefore Access was gonna create thousands of fields...

I am in a dead end!!!
Here's my schema.ini code maybe it can help!!!

[Prop_Aero.txt]
CharacterSet = ANSI
Format = Delimited(µ)
Col1=Numéro Integer
Col2=Contact Person Memo
Col3=Collaboration Memo
Col4=Target Partner Memo
Col5=Organisation Memo
Col6=URL Memo
Col7=Organisation Memo

thanks to anyone who can help

cheers
nico

 

[WayneRyan]

nico,

You have two organisation fields, and I don't think that they
are all memo fields.

I also don't know the ASCII code for your delimiter.


This code will work if you attach it to a command button:

Dim dbs As DataBase
Dim rst As Recordset
Dim sql As String
Dim buf As String
Dim ptr As long
Dim Delimiter As String

Delimiter = Chr(ASC(?))  ' Don't know ...

Set dbs = CurrentDb
sql = "Select * from YourTable"
Set rst = dbs.OpenRecordset(sql)

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

Line Input #1, buf
While Not EOF(1)
   ptr = 1
   '
   Col1 = ""
   While(Mid(buf, ptr, 1) <> Delimiter
      Col1 = Col1 & Mid(buf, ptr, 1)
      ptr = ptr + 1
      Wend
   ptr = ptr + 1
   '
   Col2 = ""
   While(Mid(buf, ptr, 1) <> Delimiter
      Col2 = Col2 & Mid(buf, ptr, 1)
      ptr = ptr + 1
      Wend
   ptr = ptr + 1
   '
   ' Repeat for Col3 - Col7
   '
   rst.Addnew
   rst!Numero = Col1
   rst!ContactPerson = Col2
   rst!Collaboration = Col3
   rst!TargetPartner = Col4
   rst!Organisation = Col5
   rst!URL = Col6
   rst!Organisation2 = Col7
   rst.Update
   Line Input #1, buf
   Wend

Wayne

 

[niki]

yep!

thanks wayne!
I'll try this at once and get back to you if I have problems. I don't think finding the ASCII code for µ is not hard.

thanks again
cheers to the both of you!
nico