TXT import

when you import a text file it is possible to press the "advancded" button where get much more control over the import process. I wouldnt have thought that a new line would give any problmes.

You can also save the import specification for later use

Hi terry

As I see it, I cannot use the advance import settings (access 2003), and I cannot save the process.


If I make and import, the line break, also make a line break in the table, so the data is no longere in the right collum.


I need to use a macro or a vba code, because it has to be used serveral times a day by different users.

Regards

Tony

Hi Tony
Can you send me an example of the file?

ekspertenATsanthell.dk
AT = @

Når jeg får sådanne data plejer jeg at hive filen igennem PHP først hvor jeg printer hver linje og indsætter linjeskift.

Så copy/paster jeg resultatet fra min browser over i en ny TXT... quick and dirty, men det spiller fint med flere hundrede tusinde records...

Alternativt skal du i VBA og gøre noget tilsvarende efter importen.

Hej Tingholm



Jeg skal bruge en automatisk import, der er flere bruger, så
det skal foregå uden en masse procedurer.


VBA er fint, men jeg er ikke skarp i VBA.

Er der nogen der måske kan hjælpe mig på vej ?


MVH

Tony

Hej


Jeg har prøvet denne:

Private Sub Kommandoknap1_Click()

DoCmd.TransferText acImportDelim, test, "tbltxtload", "C:\test.txt", True

End Sub


Jeg har levet importspecifikationer ved navn test, der separere tekst ved hjælp af  |.


Men kode bruger ikke importspecifikationerne.


Hvad gør jeg forkert ?


MVH

Tony

put test in a string "test"

Hi Terry


Much better tks.


Er der nogen der ved hvordan jeg kan undgå, at importere linie skift i denne import:

Private Sub Kommandoknap1_Click()

DoCmd.TransferText acImportDelim, "test", "tbltxtload", "C:\test.txt", True

End Sub



MVH

Tony

Hi Tony
The line (extra) break (in the middle of the line) is the source of the problem

Access doesnt know that  the extra line break is not supposed to be there so it assumes that its teh end of the record and thats why you get extra (short) records.

So it will need to be removed in code which can also be a challenge

Hi Terry


Could it be possible to set the separator, for 2 signs.


So access see both | and linebreak as a separator ?

Each line ends with a  þ , this should be set as a linebreak.



Regards

Tony

No I very much doubt you can have two seperators.

The way I see it is you will need to read the file first remove ALL CR/LF characters and then convert þ to CR/LF

I still can not understand that SAP expects a CR/LF in the middle of teh line and at the same time uses it as EOL

Hej

Er der nogen der hjæple mig videre med denne convert fil ?

Sub Convert_data()



Const ForReading = 1
Const ForWriting = 2

Dim Infile
Dim Outfile

Infile = "C\test.txt"
Outfile = "C\converted.txt"


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(Infile, ForReading)

....remove linebreak ALL CR/LF......

....convert þ to CR/LF.....


Next

objFile.Close
   
End Sub

Prøver på ny



MVH

Tony